[실습] 요구사항을 보고 DB 설계해보기 - JSCODE 쇼핑몰

2025. 8. 21. 15:30ㆍ개발/DB

반응형

* 이 글은 개발 공부를 위한 블로그글로 지식전달보다는 개발 공부 일지에 초점이 맞춰져 있으니 유의하시길 바랍니다.

✅ 요구 사항(현업에서는 UI를 보고 요구사항을 파악하는 경우가 많으니, 지금하는 실습은 연습용이라는 것을 기억해두자)

  • 회원가입 기능
    • 회원가입 시 이메일, 비밀번호, 이름, 주소, 전화번호의 정보를 받는다.
  • 로그인 기능
    • 로그인할 때 이메일과 비밀번호를 활용해서 로그인한다.
  • 상품 등록 기능
    • 로그인한 사용자만 상품을 등록할 수 있다.
    • 상품에는 상품명, 설명, 가격, 재고량, 카테고리(의류, 신발, 가전제품 등)의 정보가 포함된다.
  • 상품 조회 기능
    • 상품명, 설명, 가격, 재고량, 등록 시간을 조회할 수 있다.
    • 특정 카테고리의 상품만 조회할 수 있다.
  • 주문 기능
    • 한 번 주문할 때 여러 개의 상품을 주문할 수 있다.
    • 각 상품의 수량을 정해서 주문할 수 있다.
    • 주문한 여러개의 상품의 가격과 수량을 기록한다.
    • 주문했을 때 상품의 총 가격을 계산할 수 있다.
    • 로그인한 사용자만 주문할 수 있다.
    • 주문 시 배송 정보(이름, 주소, 전화번호)를 입력해야 한다.
    • 주문한 날짜를 조회할 수 있어야 한다.
  • 리뷰 작성 기능
    • 로그인한 사용자만 리뷰를 작성할 수 있다.
    • 상품에 대한 리뷰를 작성하고, 평점(1~5점)을 매길 수 있다.
    • 다른 사용자들이 작성한 리뷰를 조회할 수 있다.
  • 관리자 기능
    • 관리자 페이지에 접근하려면 관리자용 이메일과 비밀번호를 입력해야 한다.
  • ✅ DB 설계 과정
    1. 저장할 데이터 파악하기
      • 이메일, 비밀번호, 이름, 주소, 전화번호
      • 상품명, 상품 설명, 가격, 재고량, 카테고리(의류, 신발, 가전제품 등), 상품 등록 시간, 누가 상품을 등록했는 지
      • 특정 장바구니에 포함된 상품들, 장바구니에 포함된 상품의 각 개수, 장바구니의 주인이 누군 지
      • 주문 시 배송 정보 (이름, 주소, 전화번호), 주문한 상품 및 수량, 누가 주문했는 지
      • 어떤 상품에 리뷰를 달았는 지, 리뷰 내용, 평점, 누가 리뷰를 달았는 지
      • 관리자용 이메일, 비밀번호
    2. 그룹핑해서 분류하기
      • 위의 요구사항을 봤을 때 여러 데이터를 묶어서 표현할 수 있는 상위 개념은 아래와 같다.
      • 이메일, 비밀번호, 이름, 주소, 전화번호 → 사용자
      • 상품명, 상품 설명, 가격, 재고량, 카테고리(의류, 신발, 가전제품 등), 상품 등록 시간, 누가 상품을 등록했는 지 → 상품
      • 특정 장바구니에 포함된 상품들, 장바구니에 포함된 상품의 각 개수, 장바구니의 주인이 누군 지 → 장바구니
      • 주문 시 배송 정보 (이름, 주소, 전화번호), 주문한 상품 및 수량, 누가 주문했는 지 → 주문
      • 어떤 상품에 리뷰를 달았는 지, 리뷰 내용, 평점, 누가 리뷰를 달았는 지 → 리뷰
      • 관리자용 이메일, 비밀번호 → 관리자
    3. 6가지 규칙 적용시키면서 테이블 분리해나가기
      1. 데이터 파악
        • 이메일, 비밀번호, 이름, 주소, 전화번호 -> users
        • 상품명, 설명, 가격, 재고량, 카테고리, 등록한 사용자, 등록 시간 -> 상품
        • 주문한 사용자, 주문 상품, 주문 수량, 주문 가격, 총가격, 주문 날짜 -> 주문
        • 배송 이름, 배송 주소, 배송 전화번호 -> 배송 정보
        • 리뷰 작성자, 리뷰 내용, 평점, 어떤 상품 -> 리뷰
        • 관리자 이메일, 비밀번호 -> 관리자
      2. 엑셀로 6가지 규칙에 따라 테이블 분리 및 정규화
        사용자(users) id 이메일 비밀번호 이름 주소 전화번호    
        상품(products) id 상품명 설명 가격 재고량 카테고리, categories_id(FK) 등록한 사람, users_id(FK) 등록 시간
        카테고리(categories) id 카테고리명            
        주문(orders) id 주문한 사용자, users_id(FK) 주문 날짜          
        주문 상품(ordered_products) id 주문ID, orders_id(FK) 상품ID, products_id(FK) 주문 수량 주문 가격      
        배송정보(deliveries) id 이름 주소 전화번호 주문ID, orders_id(FK)      
        리뷰(reviews) id 작성자, users_id(FK) 내용 평점 상품,products_id(FK)      
        관리자(administers) id 이메일  비밀번호          
      3. erdcloud로 자료화
        * 이메일, 전화번호, 내용 등 여러가지 자료형이 있는데, 잘 정리된 erd를 보면서 정리해나가야할 것 같다.* 자료형이 다 다르다.                                                                                                                                          * 테이블을 분리해 나가는 과정에서 id 적용하기, 테이블 분리, 적절한 데이터 항목 수정이 잘 이루어 지지 않았던 것 같다. 계속해서 연습을 해나가야 하는 부분이다.
    4. SQL에서의 DDL 명령어
-- INIT database
-- 1) 사용자
CREATE TABLE users (
  users_id      INT NOT NULL AUTO_INCREMENT,
  email         VARCHAR(50) NOT NULL,
  password      VARCHAR(50) NOT NULL,
  name          VARCHAR(50) NOT NULL,
  adress        VARCHAR(50) NOT NULL,   -- 이미지 표기상 address가 아니라 adress
  phone_number  VARCHAR(11) NOT NULL,
  PRIMARY KEY (users_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2) 카테고리
CREATE TABLE categories (
  categories_id   INT NOT NULL AUTO_INCREMENT,
  categories_name VARCHAR(50) NOT NULL,
  PRIMARY KEY (categories_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3) 상품
CREATE TABLE products (
  products_id     INT NOT NULL AUTO_INCREMENT,
  products_name   VARCHAR(50) NOT NULL,
  description     VARCHAR(50) NOT NULL,
  price           INT NOT NULL,
  stock_number    INT NOT NULL,
  categories_id   INT NOT NULL,
  users_id        INT NOT NULL,           -- 등록자(판매자)
  registered_time TIMESTAMP NOT NULL,
  PRIMARY KEY (products_id),
  KEY idx_products_category (categories_id),
  KEY idx_products_user (users_id),
  CONSTRAINT fk_products_category
    FOREIGN KEY (categories_id) REFERENCES categories(categories_id)
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_products_user
    FOREIGN KEY (users_id)      REFERENCES users(users_id)
      ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4) 주문
CREATE TABLE orders (
  orders_id     INT NOT NULL AUTO_INCREMENT,
  users_id      INT NOT NULL,
  ordered_time  TIMESTAMP NOT NULL,
  PRIMARY KEY (orders_id),
  KEY idx_orders_user (users_id),
  CONSTRAINT fk_orders_user
    FOREIGN KEY (users_id) REFERENCES users(users_id)
      ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5) 주문상품(주문-상품 N:M 연결 + 수량/가격)
CREATE TABLE ordered_products (
  prdered_products_id INT NOT NULL AUTO_INCREMENT,  -- 그림 표기 그대로
  orders_id           INT NOT NULL,
  products_id         INT NOT NULL,                 -- 그림엔 '?' 표식이 있지만 FK로 해석
  orders_number       INT NOT NULL,
  orders_price        INT NOT NULL,
  PRIMARY KEY (prdered_products_id),
  KEY idx_op_order (orders_id),
  KEY idx_op_product (products_id),
  CONSTRAINT fk_op_order
    FOREIGN KEY (orders_id)   REFERENCES orders(orders_id)
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_op_product
    FOREIGN KEY (products_id) REFERENCES products(products_id)
      ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6) 배송정보
CREATE TABLE deliveries (
  deliveries_id INT NOT NULL AUTO_INCREMENT,
  name          VARCHAR(50) NOT NULL,
  adress        VARCHAR(50) NOT NULL,   -- 이미지 표기상 address가 아니라 adress
  phone_number  VARCHAR(50) NOT NULL,
  orders_id     INT NOT NULL,
  PRIMARY KEY (deliveries_id),
  UNIQUE KEY uq_deliveries_order (orders_id),  -- 주문 1건당 배송정보 1건이라는 전형적 가정 (그림 표현상 1:1)
  CONSTRAINT fk_deliveries_order
    FOREIGN KEY (orders_id) REFERENCES orders(orders_id)
      ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7) 리뷰
CREATE TABLE reviews (
  reviews_id    INT NOT NULL AUTO_INCREMENT,
  users_id      INT NOT NULL,
  description   TEXT NOT NULL,
  rating_score  INT  NOT NULL,
  products_id   INT  NOT NULL,
  PRIMARY KEY (reviews_id),
  KEY idx_reviews_user (users_id),
  KEY idx_reviews_product (products_id),
  CONSTRAINT fk_reviews_user
    FOREIGN KEY (users_id)    REFERENCES users(users_id)
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_reviews_product
    FOREIGN KEY (products_id) REFERENCES products(products_id)
      ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8) 관리자
CREATE TABLE administers (
  administers_id INT NOT NULL AUTO_INCREMENT,
  email          VARCHAR(50) NOT NULL,
  password       VARCHAR(50) NOT NULL,
  PRIMARY KEY (administers_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 1) 사용자
INSERT INTO users (email, password, name, adress, phone_number) VALUES
('hong@test.com', 'pw1234', '홍길동', '서울시 강남구', '01012345678'),
('lee@test.com',  'pw5678', '이순신', '부산시 해운대구', '01023456789'),
('kim@test.com',  'pw9012', '김철수', '대구시 달서구', '01034567890');

-- 2) 카테고리
INSERT INTO categories (categories_name) VALUES
('전자제품'),
('가구'),
('도서');

-- 3) 상품 (판매자=users_id 참조)
INSERT INTO products (products_name, description, price, stock_number, categories_id, users_id, registered_time) VALUES
('노트북', 'i5 16GB RAM', 800000, 5, 1, 1, NOW()),
('책상', '튼튼한 원목 책상', 150000, 10, 2, 2, NOW()),
('소설책', '베스트셀러 소설', 12000, 50, 3, 3, NOW());

-- 4) 주문 (구매자=users_id 참조)
INSERT INTO orders (users_id, ordered_time) VALUES
(2, NOW()),   -- 이순신이 주문
(3, NOW());   -- 김철수가 주문

-- 5) 주문상품
INSERT INTO ordered_products (orders_id, products_id, orders_number, orders_price) VALUES
(1, 1, 1, 800000),   -- 이순신이 노트북 1대 구매
(2, 2, 2, 300000);   -- 김철수가 책상 2개 구매

-- 6) 배송정보
INSERT INTO deliveries (name, adress, phone_number, orders_id) VALUES
('이순신', '부산시 해운대구 배송지', '01022223333', 1),
('김철수', '대구시 달서구 배송지', '01033334444', 2);

-- 7) 리뷰 (구매자들이 리뷰 작성)
INSERT INTO reviews (users_id, description, rating_score, products_id) VALUES
(2, '노트북 성능이 좋아요!', 5, 1),
(3, '책상 튼튼하고 좋아요.', 4, 2);

-- 8) 관리자
INSERT INTO administers (email, password) VALUES
('admin@test.com', 'admin123'),
('manager@test.com', 'manager123');


-- QUERY database
SELECT * FROM users;

- deliveries 테이블에 대한 설명

CREATE TABLE deliveries (
  deliveries_id INT NOT NULL AUTO_INCREMENT,
  name          VARCHAR(50) NOT NULL,
  adress        VARCHAR(50) NOT NULL,   -- 이미지에 표기된 대로 adress (원래는 address 추천)
  phone_number  VARCHAR(50) NOT NULL,
  orders_id     INT NOT NULL,
  PRIMARY KEY (deliveries_id),
  UNIQUE KEY uq_deliveries_order (orders_id),  -- 주문 1건당 배송정보 1건
  CONSTRAINT fk_deliveries_order
    FOREIGN KEY (orders_id) REFERENCES orders(orders_id)
      ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

📖 컬럼 설명

  • deliveries_id (INT, PK, AUTO_INCREMENT)
    → 배송정보 고유 ID. 각 배송 정보 레코드를 구분하는 기본 키. 자동 증가.
  • name (VARCHAR(50), NOT NULL)
    → 수령자 이름. 주문자가 아닐 수도 있음 (예: 다른 사람에게 선물).
  • adress (VARCHAR(50), NOT NULL)
    → 배송지 주소. (※ 표기상 adress로 되어 있지만 실제 운영에선 address로 쓰는 게 좋음).
  • phone_number (VARCHAR(50), NOT NULL)
    → 수령자 연락처. 배송 기사님이 연락할 수 있도록 저장.
    (숫자만 저장하는 경우도 있지만, 010-1234-5678 같은 포맷을 고려해 문자열 타입 사용).
  • orders_id (INT, NOT NULL)
    → 어떤 주문(orders 테이블)과 연결된 배송정보인지 나타내는 외래 키.
  • 🔗 제약조건 설명
    • PRIMARY KEY (deliveries_id)
      → 각 배송정보는 deliveries_id로 고유하게 식별됨.
    • UNIQUE KEY uq_deliveries_order (orders_id)
      → 하나의 주문(orders_id)에는 배송정보가 반드시 하나만 연결된다는 의미.
      (즉, 주문 1건 = 배송정보 1건 구조. 부분 배송이나 여러 배송지가 필요하다면 이 제약은 제거해야 함).
    • FOREIGN KEY fk_deliveries_order
      • ON UPDATE CASCADE → orders.orders_id가 변경되면 deliveries.orders_id도 자동으로 업데이트됨.
      • ON DELETE CASCADE → 주문이 삭제되면 연결된 배송정보도 함께 삭제됨
    ⚡ 정리
  • deliveries 테이블은 주문과 1:1 관계를 맺으며, 주문별 배송 수령인/주소/연락처를 관리하는 테이블입니다.
    따라서 “한 주문 → 한 배송정보”라는 구조에 적합합니다. 만약 “한 주문 → 여러 배송지(부분 배송)” 같은 시나리오라면, UNIQUE KEY uq_deliveries_order를 제거하고 1:N 관계로 확장하는 게 맞습니다.
반응형

'개발 > DB' 카테고리의 다른 글

[실습] 화면 UI 디자인을 보고 DB 설계해보기  (2) 2025.08.21
[실습] 요구사항을 보고 DB 설계해보기 - JSCODE 커뮤니티  (0) 2025.08.21
⭐️복잡한 개념을 몰라도 누구나 따라할 수 있는, 마법의 DB 설계 규칙 5가지⭐️  (3) 2025.08.20
DB 설계의 핵심 원칙 및 전체 과정  (1) 2025.08.20
DB 설계 전 필수로 알아야 하는 개념  (2) 2025.08.19

관련글

티스토리툴바