블로그 서비스의 DB 구축

1. 설계

User 테이블 명세서

Field	Type	Constraints	Desc
id	INT	pk, auto..	사용자 고유 ID
user_name	VARCHAR(100)	not null, Unique	사용자 이름
password	VARCHAR(255)	not null	비밀번호
email	VARCHAR(100)	not null	이메일
userRole	VARCHAR(20)	-	유저 타입
createDate	TIMESTAMP	Deault CURRENT_TIMESTAMP	계정 생성 시간

 

Board 테이블 명세서

Field	Type	Constraints	Desc
id	INT	pk, auto..	게시글 고유 ID
userId	INT	not null, fk	사용자 ID
title	VARCHAR(100)	not null	제목
content	TEXT	-	내용
readCount	INT	-	조회수

 

Reply 테이블 명세서

Field	Type	Constraints	Desc
id	INT	pk, auto..	댓글 고유 ID
userId	INT	fk	사용자 ID
boardId	INT	fk	게시글 ID
content	VARCHAR(300)	not null	내용
createDate	TIMESTAMP	Deault CURRENT_TIMESTAMP	댓글 시간

create database m_board;
use m_board;

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL,
    userRole VARCHAR(20),
    createDate TIMESTAMP
);

-- board table, reply table

CREATE TABLE board (
    id INT PRIMARY KEY AUTO_INCREMENT,
    userId INT,
    title VARCHAR(100) NOT NULL,
    contend TEXT,
    FOREIGN KEY (userId)
        REFERENCES user (id)
);

CREATE TABLE reply (
    id INT PRIMARY KEY AUTO_INCREMENT,
    userId INT,
    boardId INT,
    content VARCHAR(300) NOT NULL,
    createDate TIMESTAMP,
    FOREIGN KEY (userId)
        REFERENCES user (id)
        ON DELETE SET NULL, -- user id가 삭제되면 null로 바꿈
    FOREIGN KEY (boardId)
        REFERENCES board (id)
);

2. 정규화 검토

• 제1정규형 (1NF)
  • 모든 필드는 원자값을 가지고 있으며, 각 컬럼은 유일한 데이터 유형을 가진다. 테이블의 모든 키는 유일하게 식별된다.
• 제2정규형 (2NF)
  • 기본 키의 일부에만 종속되는 비키 종속성이 없다. 각 테이블에서 기본 키가 완전히 기능적 종속성을 이루고 있다.
• 제3정규형 (3NF)
  • 모든 필드가 기본 키에만 종속되고, 기본 키가 아닌 다른 필드에 종속되는 이행적 종속성이 없다. 예를 들어, User 테이블에서 사용자의 주소나 역할은 사용자 ID에만 종속된다.

-- 스키마 구조를 변경하는 쿼리 --- DDL, DML, DCL
-- user 테이블에 address 컬럼을 추가해라
alter TABLE user add address varchar(100) not null;

INSERT INTO user (username, password, email, address, userRole, createDate)
VALUES
('홍길동', '1234', 'hong@example.com', '서울시 강남구', 'admin', NOW()),
('이순신', '1234', 'lee@example.com', '부산시 해운대구', 'user', NOW()),
('김유신', '1234', 'kim@example.com', '대구시 수성구', 'user', NOW());

INSERT INTO board (userId, title, content, readCount)
VALUES
(1, '첫 번째 글입니다', '안녕하세요, 홍길동입니다. 이것은 테스트 게시글입니다.', 150),
(2, '이순신의 포스팅', '부산에서 이순신입니다. 바다가 아름다운 날입니다.', 45),
(3, '대구의 뜨거운 여름', '여름이 기승을 부리는 대구에서 김유신입니다.', 30);

INSERT INTO reply (userId, boardId, content, createDate)
VALUES
(2, 1, '홍길동님의 글 잘 읽었습니다!', NOW()),
(3, 1, '저도 의견이 같네요.', NOW()),
(1, 2, '부산도 좋지만 서울도 좋아요!', NOW()),
(1, 3, '대구가 그렇게 덥군요, 조심하세요!', NOW());

-- 특정 사용자의 게시글 조회 (사용자 ID가 1인 홍길동의 모든 게시글을 보고 싶다면
SELECT title, content, readCount
FROM board
WHERE userId = 1;

-- 1번 게시글에 대한 모든 댓글 조회
SELECT username, content, r.createDate
FROM reply r
LEFT JOIN user u ON r.userId = u.id
WHERE r.boardId = 1;

-- 게시글에 댓글 달기
-- 예를 들어, 사용자 ID 2가 게시글 ID 1에
-- "새로운 댓글입니다"라는 내용의 댓글을 추가하려면 다음 쿼리를 사용합니다.
INSERT INTO reply(userId, boardId, content, createDate)
VALUES (2, 1, "새로운 댓글입니다", now());

-- 특정 사용자의 게시글에 해당하는 게시글의 댓글 수 조회
-- 제목, 내용, 작성자 이름, 댓글 수
SELECT b.title, b.content, u.username, count(*) as 댓글수
FROM board b
LEFT JOIN reply r ON b.id = r.boardId
JOIN user u ON r.userId = u.id
GROUP BY b.id;

-- 조회수가 가장 높은 게시글 상위 2개만 조회
SELECT *
FROM board
ORDER BY readCount DESC
LIMIT 2;