1. 저장할 데이터 파악
-
이메일, 비밀번호, 이름, 주소, 전화번호
-
상품등록자, 상품명, 설명, 가격, 재고량, 등록시간, 카테고리
-
주문 : 상품 = 1 : N
상품의 수량을 정해 주문 가능
상품의 가격, 수량
상품의 총 가격
주문한 사람, 배송 정보(이름, 주소, 전화번호), 날짜 -
리뷰 작성자, 상품명, 내용, 평점
-
이메일, 비밀번호
2. 그룹핑 및 분류
-
사용자(users)
이메일, 비밀번호, 이름, 주소, 전화번호 -
상품(products)
상품등록자, 상품명, 설명, 가격, 재고량, 등록시간, 카테고리 -
주문(orders)
주문 : 상품 = 1 : N
상품의 수량을 정해 주문 가능
상품의 가격, 수량
상품의 총 가격
주문한 사람, 배송 정보(이름, 주소, 전화번호), 날짜 -
리뷰(reviews)
리뷰 작성자, 상품명, 내용, 평점 -
관리자(admins)
이메일, 비밀번호
3. 테이블 생성 및 분리
1) 데이터 분류에 따른 테이블 생성
먼저, 위에서 분류한대로 테이블을 생성한다. mock 데이터도 넣어본다.
users
| id | 이메일 | 비밀번호 | 이름 | 주소 | 전화번호 | 관리자 유무 |
|---|---|---|---|---|---|---|
| 1 | k@k.com | kkkkk | 김동이 | 서울시 xx | 0100000000 | o |
| 2 | n@n.com | nnnnn | 이지아 | 강원도 xx | 0100000001 | o |
| 3 | b@b.com | bbbbb | 한정민 | 경기도 xx | 0100000002 | x |
products
| id | 상품명 | 설명 | 가격 | 재고량 | 등록시간 | 판매자 | 카테고리 |
|---|---|---|---|---|---|---|---|
| 1 | 고구마 | 고구마 | 30,000 | 10 | 2024-01-01 00:00:00 | 김동이 | 식품 |
| 2 | 이어폰 | 이어폰 | 200,000 | 5 | 2024-01-01 00:00:00 | 김동이 | 전자 |
| 3 | 감자 | 감자 | 10,000 | 10 | 2024-01-01 00:00:00 | 이지아 | 식품 |
orders
| id | 상품 | 가격 | 수량 | 총가격 | 주문자 | 주소 | 전화번호 | 날짜 |
|---|---|---|---|---|---|---|---|---|
| 1 | 고구마 | 30,000 | 1 | 230,000 | 한정민 | 경기 | 0100000002 | 2024-01-01 |
| 2 | 이어폰 | 200,000 | 1 | 230,000 | 한정민 | 경기 | 0100000002 | 2024-01-01 |
| 3 | 고구마 | 30,000 | 2 | 60,000 | 이지아 | 식품 | 0100000001 | 2024-01-02 |
reviews
| id | 작성자 | 상품명 | 내용 | 평점 |
|---|---|---|---|---|
| 1 | 한정민 | 고구마 | 맛있어요 | 5 |
| 2 | 이지아 | 고구마 | 고구마는 역시 호구마 | 4 |
admins
| id | 이메일 | 비밀번호 |
|---|---|---|
| 1 | manymany@m.com | mnmn |
| 2 | money@m.com | m11111 |
2) 테이블 분리 및 생성 (iiingkeep ver)
위에 생성된 테이블에서 중복 데이터를 찾아 테이블을 분리 및 생성한다.
내 임의로 진행한 과정이며 이유를 밑에 적어놓았다.
이 과정으로 수정되거나 생성된 테이블은 아래와 같다.
products
| id | 상품명 | 설명 | 가격 | 등록시간 | 판매자 id (admins FK) | 카테고리 id (FK) |
|---|---|---|---|---|---|---|
| 1 | 고구마 | 고구마 | 30,000 | 2024-01-01 00:00:00 | 1 | 1 |
| 2 | 이어폰 | 이어폰 | 200,000 | 2024-01-01 00:00:00 | 1 | 2 |
| 3 | 감자 | 감자 | 10,000 | 2024-01-01 00:00:00 | 2 | 1 |
categories
| id | 카테고리명 |
|---|---|
| 1 | 식품 |
| 2 | 전자 |
orders
| id | 상품 id (FK) | 가격 | 수량 | 주문자 id (FK) | 배송 정보 id (FK) | 날짜 |
|---|---|---|---|---|---|---|
| 1 | 1 | 30,000 | 1 | 3 | 1 | 2024-01-01 12:00:00 |
| 2 | 2 | 200,000 | 1 | 3 | 1 | 2024-01-01 12:00:00 |
| 3 | 1 | 30,000 | 2 | 2 | 2 | 2024-01-02 |
deliveries
| id | 주문자 id (FK) | 이름 | 주소 | 전화번호 |
|---|---|---|---|---|
| 1 | 3 | 한정민 | 경기도 xx | 0100000002 |
| 2 | 2 | 김가은 | 충청도 xx | 0100000009 |
reviews
| id | 작성자 id (FK) | 상품 id (FK) | 내용 | 평점 |
|---|---|---|---|---|
| 1 | 3 | 1 | 맛있어요 | 5 |
| 2 | 2 | 1 | 고구마는 역시 호구마 | 4 |
admins
| id | 이메일 | 비밀번호 | 회원 id (FK) |
|---|---|---|---|
| 1 | manymany@m.com | mnmn | 1 |
| 2 | money@m.com | m11111 | 2 |
테이블 분리 및 생성의 과정
products
- 진짜 중복 찾기
- 판매자
판매자 : 상품 = 1 : N 의 관계를 가지므로 상품에 Foreign key를 둔다.
여기에서 판매자는 users 테이블이 아닌 admins 테이블에 있는 관리자를 뜻한다. - 카테고리
테이블을 분리한 뒤 상품 : 카테고리 = N : 1 의 관계를 가지므로 상품에 Foreign key를 둔다.
- 판매자
- 숨은 중복 찾기
- 재고량
재고량은 주문과 동시에 값이 줄어드는 변화가 있어야 하는 데이터이다. 이런 데이터는 테이블에서 제거한다.
- 재고량
orders
- 진짜 중복 찾기
- 배송 정보(이름, 주소, 전화번호)
배송 정보(이름, 주소, 전화번호)는 같은 사람이 같은 주소나 전화번호로 주문을 여러 번 할 수 있기 때문에 진짜 중복에 해당한다. 그렇기 때문에 배송 테이블로 분리한다.
- 배송 정보(이름, 주소, 전화번호)
- 숨은 중복 찾기
- 총 가격
총 가격은 주문 내역에 따라 바뀌어야한다. 그렇기때문에 총 가격은 한 데이터를 변경할 경우 다른 데이터도 변경되는 숨은 중복에 해당하며 동시에 통계 데이터이기 때문에 테이블에서 제외시킨 뒤 이후에 쿼리를 통해 구한다.
- 총 가격
deliveries
배송 정보의 경우 회원 정보에 등록되지 않은 다른 이름이나 주소, 전화번호를 사용할 수 있다고 가정하여 진행했다. 다른 사람에게 보내는 상품일 수도 있기 때문이다.
reviews
- 진짜 중복
- 작성자
한 작성자가 여러 개의 상품에 리뷰를 달 수 있기 때문에 같은 작성자의 리뷰 데이터가 중복으로 나타날 수 있는 진짜 중복이다.
작성자 : 리뷰 = 1 : N 이기 때문에 리뷰에 Foreign key를 둔다. - 상품
같은 상품에 여러 개의 리뷰가 달릴 수 있기 때문에 같은 상품명이 중복해 등장할 수 있는 진짜 중복이다.
상품 : 리뷰 = 1 : N 이기 때문에 리뷰에 Foreign key를 둔다.
- 작성자
admins
가장 어려웠던 부분이다. 일단 모든 유저 중 관리자이기도 한 유저를 관리자 유무 컬럼을 통해 유저 테이블에서 확인할 수 있도록 하고, admins 테이블을 따로 두었다. 이 웹사이트의 유저가 먼저 되야지만 관리자도 될 수 있을거라고 생각했기 때문이다. 회원 id를 Foreign key로 두어 어떤 유저가 이 관리자인지 파악할 수 있도록 했다.
3) 최종적으로 설계된 DB (강사님 ver)
강사님이 설계한 최종 버전 DB이다.
위에서 내가 진행한 과정과 강사님이 진행한 과정을 비교하며 내가 다르게 설계한 이유도 함께 기재했다.
users
| id | 이메일 | 비밀번호 | 이름 | 주소 | 전화번호 |
|---|---|---|---|---|---|
| 1 | k@k.com | kkkkk | 김동이 | 서울시 xx | 0100000000 |
| 2 | n@n.com | nnnnn | 이지아 | 강원도 xx | 0100000001 |
| 3 | b@b.com | bbbbb | 한정민 | 경기도 xx | 0100000002 |
products
| id | 상품명 | 설명 | 가격 | 재고량 | 등록시간 | 등록자 id (FK) | 카테고리 id (FK) |
|---|---|---|---|---|---|---|---|
| 1 | 고구마 | 고구마 | 30,000 | 10 | 2024-01-01 00:00:00 | 1 | 1 |
| 2 | 이어폰 | 이어폰 | 200,000 | 5 | 2024-01-01 00:00:00 | 1 | 2 |
| 3 | 감자 | 감자 | 10,000 | 5 | 2024-01-01 00:00:00 | 2 | 1 |
orders
| id | 주문자 id (FK) | 날짜 | 배송 id (FK) |
|---|---|---|---|
| 1 | 3 | 2024-01-01 12:00:00 | 1 |
| 2 | 3 | 2024-01-01 12:00:00 | 2 |
ordered_products
| id | 주문 id (FK) | 상품 id (FK) | 수량 | 가격 |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 30,000 |
| 2 | 1 | 2 | 1 | 200,000 |
| 3 | 2 | 1 | 2 | 60,000 |
deliveries
| id | 주문자 id (FK) | 이름 | 주소 | 전화번호 |
|---|---|---|---|---|
| 1 | 3 | 한정민 | 경기도 xx | 0100000002 |
| 2 | 2 | 김가은 | 충청도 xx | 0100000009 |
reviews
| id | 작성자 id (FK) | 상품 id (FK) | 내용 | 평점 |
|---|---|---|---|---|
| 1 | 3 | 1 | 맛있어요 | 5 |
| 2 | 2 | 1 | 고구마는 역시 호구마 | 4 |
admins
| id | 이메일 | 비밀번호 |
|---|---|---|
| 1 | manymany@m.com | mnmn |
| 2 | money@m.com | m11111 |
테이블 분리 및 생성의 과정
products
재고량은 순간순간 주문에 따라 수량이 변화하는 값이라 컬럼에서 제외해야 한다고 생각했는데, 그렇지 않았다. 다른 데이터에 기반한 통계 데이터도 아니었다. 상품 테이블에 그대로 두면 된다. 헷갈릴 수 있으니 주의!
orders
처음부터 설계를 잘 못했던 테이블로, 주문한 상품과 수량, 가격 등의 데이터는 한 칸에 넣어야 하는 데이터였지만 여러 행으로 분리해 넣었었다. 하나의 주문에 담긴 여러 상품들과 그 가격들은 모두 한 칸에 여러 데이터가 들어가 제 1 정규형을 위반한다. 상품과 주문의 관계는 상품 : 주문 = N : M 이므로 '주문된 상품' 테이블을 새로 생성한 뒤 상품 id와 주문 id를 FK로 가져왔다.
deliveries
누가 주문했는지 users 테이블의 주문자 id를 Foreign key로 가져와야한다고 생각했었는데 주문할 때 배송정보를 입력하는 것이고, orders 테이블에 이미 주문자에 대한 정보가 있기 때문에 orders 테이블과의 관계를 따져봐야 하는 것이었다.
주문 : 배송 = 1 : 1 이므로 주문 또는 배송 테이블 어디에나 Foreign key를 둬도 된다. 컬럼이 더 적은 orders 테이블에 배송 id를 FK로 넣어주었다.
1 : 1 관계이므로 만약 주문 정보를 확인할 때 배송정보까지 한 번에 확인하는 경우가 많은 서비스라면 두 테이블을 합치는 것이 더 성능상 유리할 수 있다.
users & admins
일단 관리자에 대한 나의 생각이 완전히 잘 못 되었다는 것을 알았다.
나는 관리자 = 이 웹사이트의 유저이자 동시에 판매자인 사람으로 생각했었다.
오히려 복잡하게 생각했던 것 같은데, 관리자를 오롯이 관리자로만 여기니 결과는 훨씬 간단했다.
** 요구사항의 경우 강의 자료를 그대로 가져와야해서 생략했습니다.
참고
이 게시글은 박재성님의 비전공자도 이해할 수 있는 DB 설계 입문/실전 강의를 토대로 작성되었습니다.
https://www.inflearn.com/course/%EB%B9%84%EC%A0%84%EA%B3%B5%EC%9E%90-db-%EC%84%A4%EA%B3%84-%EC%9E%85%EB%AC%B8/dashboard
