쇼핑몰 , 블로그 서비스의 DB 구축

MySQL

쇼핑몰 , 블로그 서비스의 DB 구축

햄발자 2024. 6. 11. 17:38

User Table

Field	Type	Constraints	description
id	INT	pk, auto..	사용자 고유 ID
username	VARCHAR(50)	not null	사용자 이름
email	VARCHAR(100)	not null, Unique	이메일 주소
password	VARCHAR(255)	not null	비밀번호
address	VARCHAR(255)	-	주소
created_at	TIMESTAMP	Default
CURRENT_TIMESTAMP	계성 생성 시간

Product Table

Field	Type	Constraints	description
id	int	pk, auto..	식별자
name	varchar(100)	not null	상품 이름
description	TEXT	-	상품 설명
price	DECIMARL(10, 2)	not null	가격
stock	int	default 0	재고 수량
created_at	TIMESTAMP	Default
CURRENT_TIMESTAMP	상품 등록 시간

Order Table

Field	Type	Constraints	description
id	int	pk, au-in..	주문 고유 ID
user_id	int	not null, fk	주문한 사용자에 ID
total_price	DECIMAL(10,2)	not null	총 주문 금액
created_at	TIMESTAMP	defualt C_T	주문 시간

Order Detail Table

Field	Type	Constraints	description
id	int	pk, au_in	주문 상세 고유 ID
order_id	int	not null, fk	주문 ID
proudct_id	int	not null, fk	주문한 상품 ID
count	int	not null	주문 수량
price	DECIMAL(10,2)	not null	상품의 주문 가격

주문(orders) 테이블과 주문 상세(order_details) 테이블을 분리하여 설계한 이유는 ?

▷ 데이터베이스 정규화 원칙에 기반한 것

정규화는 데이터베이스 설계에서 중요한 과정으로, 데이터 중복을 최소화하고, 데이터 무결성을 향상시키며, 수정, 삽입, 삭제 등의 데이터베이스 작업 시 발생할 수 있는 문제점들을 방지하기 위해 사용됩니다.

주문과 주문 상세 테이블 분리의 목적

중복 제거

주문 정보와 주문 상세 정보를 하나의 테이블로 관리할 경우, 주문 정보(예: 주문자 ID, 주문 날짜)가 각 주문 상품마다 반복적으로 저장됩니다. 이는 데이터 중복을 초래하고, 디스크 공간을 불필요하게 사용하게 합니다.

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, 
  content 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,
  foreign key(boardId) references board(id) 
);

정규화 검토

제 1 정규형 (1NF)
- 모든 필드는 원자값을 가지고 있으며, 각 컬럼은 유일한 데이터 유형을 가집니다. 테이블의 모든 키는 유일하게 식별됩니다.

제 2 정규형 (2NF)
- 기본 키의 일부에만 종속되는 비키 종속성이 없습니다. 각 테이블에서 기본 키가 완전히 기능적 종속성을 이루고 있습니다.

제 3 정규형 (3NF)
- 모든 필드가 기본 키에만 종속되고, 기본 키가 아닌 다른 필드에 종속되는 이행적 종속성이 없습니다. 예를 들어, User 테이블에서 사용자의 주소나 역할은 사용자 ID에만 종속됩니다.

샘플데이터

desc user;

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());

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

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


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

SELECT * FROM user;
SELECT * FROM board;
SELECT * FROM reply;

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

-- 1번 게시글 대한 모든 댓글 조회
SELECT u.username, r.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 ...
INSERT INTO reply (userId, boardId, content, createDate)
VALUES (2, 1, '새로운 댓글입니다', NOW());


-- 특정 사용자의 게시글 해당 게시글의 댓글 수 조회
-- 제목, 내용, 작성자 이름, 댓글 수
-- 글1 , ..., 홍길동,  19
-- 글2 ,  .., 이순신,  10
SELECT b.title, b.content, u.username, COUNT(r.id) as CommentCount
FROM board b
JOIN user u ON b.userId = u.id
LEFT JOIN reply r ON b.id = r.boardId
GROUP BY b.id;


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