Database

Database 라이브 세션 과제 1 [데이터베이스 설계를 위한 관계정의]

Chansman 2025. 3. 25. 10:30

📌 개념 정리

이 글에서는 Amazon.com을 기반으로 한 온라인 쇼핑몰 데이터베이스 설계를 위한 관계를 정의하고, 이를 바탕으로 관계형 데이터베이스 테이블을 설계하는 방법을 설명합니다. 각 관계는 실제 Amazon과 유사한 서비스를 제공하는 데 필요한 데이터 모델을 반영하고 있습니다.

주요 관계는 사용자와 주문, 상품과 카테고리, 상품과 장바구니, 상품과 리뷰 등 여러 주요 엔티티 간의 상호작용을 중심으로 정의됩니다.

🚦 동작 원리 및 구조

관계형 데이터베이스는 여러 테이블 간의 관계를 정의하며, 이를 통해 데이터를 효과적으로 관리하고 추출할 수 있습니다. 각 관계를 잘 정의하는 것이 중요합니다. 예를 들어, 한 사용자는 여러 주문을 할 수 있으며, 각 주문은 여러 상품을 포함할 수 있습니다. 이를 기반으로 테이블을 설계하면, 데이터 중복을 방지하고 효율적인 쿼리 성능을 유지할 수 있습니다.

각 관계는 명사+명사+동사 형태로 정의됩니다. 예를 들어, 사용자와 주문 관계는 '사용자는 여러 주문을 한다'는 관계를 통해 users 테이블과 orders 테이블을 연결합니다.

💻 관계 정의

아래는 주요 관계와 각 관계의 설명을 표로 정리한 것입니다.

관계 설명

회원과 주문 회원은 여러 주문을 한다.
하나의 주문은 여러 상품을 포함한다.
상품과 카테고리 상품은 여러 카테고리에 속한다.
하나의 카테고리는 여러 상품을 포함한다.
상품과 장바구니 회원은  여러 상품을 장바구니에 담는다.
하나의 장바구니는 여러 상품을 포함한다.
상품과 리뷰 사용자는 여러 상품에 리뷰를 남긴다.
하나의 상품은 여러 리뷰를 받는다.
주문과 결제 주문은 하나의 결제를 요청한다.
하나의 결제는 하나의 주문에 연결된다.
주문과 배송 주문은 하나의 배송을 요청한다.
하나의 배송은 하나의 주문에 연결된다.
상품과 재고 상품은 하나의 재고를 가진다.
하나의 재고는 여러 상품을 관리한다.
회원과 할인 쿠폰 회원은 여러 할인 쿠폰을 사용한다.
하나의 할인 쿠폰은 여러 회원에게 적용될 수 있다.

데이터베이스 설계도를 위한 주요 엔티티

  1. 사용자 (User)
    • user_id: 사용자 고유 ID
    • name: 사용자 이름
    • email: 이메일 주소
    • password: 비밀번호
    • address: 사용자 주소
    • phone_number: 전화번호
  2. 상품 (Product)
    • product_id: 상품 고유 ID
    • name: 상품 이름
    • description: 상품 설명
    • price: 가격
    • category_id: 카테고리 ID (외래키)
    • stock_id: 재고 ID (외래키)
  3. 주문 (Order)
    • order_id: 주문 고유 ID
    • user_id: 사용자 ID (외래키)
    • order_date: 주문 날짜
    • status: 주문 상태 (배송 중, 완료 등)
  4. 결제 (Payment)
    • payment_id: 결제 고유 ID
    • order_id: 주문 ID (외래키)
    • payment_date: 결제 날짜
    • amount: 결제 금액
    • payment_method: 결제 방식 (카드, 계좌이체 등)
  5. 배송 (Shipping)
    • shipping_id: 배송 고유 ID
    • order_id: 주문 ID (외래키)
    • shipping_date: 배송 시작 날짜
    • status: 배송 상태 (배송 중, 완료 등)
    • address: 배송 주소
  6. 리뷰 (Review)
    • review_id: 리뷰 고유 ID
    • product_id: 상품 ID (외래키)
    • user_id: 사용자 ID (외래키)
    • rating: 평점
    • comment: 리뷰 내용
    • review_date: 리뷰 작성 날짜
  7. 장바구니 (Cart)
    • cart_id: 장바구니 고유 ID
    • user_id: 사용자 ID (외래키)
    • created_at: 장바구니 생성 날짜
  8. 카테고리 (Category)
    • category_id: 카테고리 고유 ID
    • category_name: 카테고리 이름
  9. 재고 (Inventory)
    • inventory_id: 재고 고유 ID
    • product_id: 상품 ID (외래키)
    • quantity: 재고 수량
  10. 할인 쿠폰 (Coupon)
    • coupon_id: 쿠폰 고유 ID
    • discount: 할인율
    • expiry_date: 유효 기간

예시쿼리)

 

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    password VARCHAR(100),
    address TEXT,
    phone_number VARCHAR(15)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    description TEXT,
    price DECIMAL(10, 2),
    category_id INT,
    stock_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id),
    FOREIGN KEY (stock_id) REFERENCES inventories(inventory_id)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    status VARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE payments (
    payment_id INT PRIMARY KEY,
    order_id INT,
    payment_date DATE,
    amount DECIMAL(10, 2),
    payment_method VARCHAR(50),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

CREATE TABLE shippings (
    shipping_id INT PRIMARY KEY,
    order_id INT,
    shipping_date DATE,
    status VARCHAR(50),
    address TEXT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

CREATE TABLE reviews (
    review_id INT PRIMARY KEY,
    product_id INT,
    user_id INT,
    rating INT,
    comment TEXT,
    review_date DATE,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE carts (
    cart_id INT PRIMARY KEY,
    user_id INT,
    created_at DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(100)
);

CREATE TABLE inventories (
    inventory_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE coupons (
    coupon_id INT PRIMARY KEY,
    discount DECIMAL(5, 2),
    expiry_date DATE
);
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    password VARCHAR(100),
    address TEXT,
    phone_number VARCHAR(15)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    description TEXT,
    price DECIMAL(10, 2),
    category_id INT,
    stock_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id),
    FOREIGN KEY (stock_id) REFERENCES inventories(inventory_id)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    status VARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE payments (
    payment_id INT PRIMARY KEY,
    order_id INT,
    payment_date DATE,
    amount DECIMAL(10, 2),
    payment_method VARCHAR(50),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

CREATE TABLE shippings (
    shipping_id INT PRIMARY KEY,
    order_id INT,
    shipping_date DATE,
    status VARCHAR(50),
    address TEXT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

CREATE TABLE reviews (
    review_id INT PRIMARY KEY,
    product_id INT,
    user_id INT,
    rating INT,
    comment TEXT,
    review_date DATE,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE carts (
    cart_id INT PRIMARY KEY,
    user_id INT,
    created_at DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(100)
);

CREATE TABLE inventories (
    inventory_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE coupons (
    coupon_id INT PRIMARY KEY,
    discount DECIMAL(5, 2),
    expiry_date DATE
);

 

다음은 위의 관계를 반영한 ERD 코드입니다. Mermid 문법을 사용하여 각 테이블 간의 관계를 표현합니다.

 

erDiagram
    USERS {
        int user_id
        string name
        string email
        string password
        string address
        string phone_number
    }
    ORDERS {
        int order_id
        int user_id
        date order_date
        string status
    }
    PRODUCTS {
        int product_id
        string name
        string description
        decimal price
    }
    CATEGORIES {
        int category_id
        string category_name
    }
    CARTS {
        int cart_id
        int user_id
        date created_at
    }
    REVIEWS {
        int review_id
        int product_id
        int user_id
        int rating
        string comment
        date review_date
    }
    PAYMENTS {
        int payment_id
        int order_id
        date payment_date
        decimal amount
        string payment_method
    }
    SHIPPINGS {
        int shipping_id
        int order_id
        date shipping_date
        string status
        string address
    }
    INVENTORIES {
        int inventory_id
        int product_id
        int quantity
    }
    COUPONS {
        int coupon_id
        decimal discount
        date expiry_date
    }

    USERS ||--o| ORDERS: "places"
    ORDERS ||--o| PRODUCTS: "contains"
    PRODUCTS ||--o| CATEGORIES: "belongs_to"
    USERS ||--o| CARTS: "adds_to"
    CARTS ||--o| PRODUCTS: "includes"
    USERS ||--o| REVIEWS: "writes"
    REVIEWS ||--o| PRODUCTS: "reviews"
    ORDERS ||--o| PAYMENTS: "requests"
    ORDERS ||--o| SHIPPINGS: "requests"
    PRODUCTS ||--o| INVENTORIES: "has_stock"
    USERS ||--o| COUPONS: "uses"
    COUPONS ||--o| USERS: "is_used_by"
    CATEGORIES ||--o| PRODUCTS: "includes"

 

설명:

  • USERS (사용자) 테이블은 사용자의 정보를 저장합니다. 하나의 사용자는 여러 **ORDERS (주문)**을 만들 수 있습니다.
  • ORDERS는 여러 **PRODUCTS (상품)**을 포함할 수 있습니다.
  • PRODUCTS는 여러 **CATEGORIES (카테고리)**에 속할 수 있습니다.
  • **CARTS (장바구니)**와 PRODUCTS는 여러 USERS와 연결됩니다. 즉, 사용자가 여러 상품을 장바구니에 담을 수 있습니다.
  • **REVIEWS (리뷰)**는 USERS가 PRODUCTS에 남길 수 있는 리뷰를 나타냅니다.
  • ORDERS는 **PAYMENTS (결제)**와 SHIPPINGS (배송) 테이블과 각각 연결됩니다. 하나의 주문은 하나의 결제 및 배송과 연결됩니다.
  • INVENTORIES (재고) 테이블은 PRODUCTS의 재고 정보를 관리합니다.
  • **COUPONS (할인 쿠폰)**은 USERS가 사용할 수 있는 할인 쿠폰을 나타냅니다. 하나의 쿠폰은 여러 사용자에게 적용될 수 있습니다.

 

🧪 실전 사례

이 관계를 바탕으로 실제 데이터베이스 설계를 통해 상품 관리, 주문 처리, 결제 및 배송, 리뷰 시스템 등을 포함한 전반적인 eCommerce 시스템을 구축할 수 있습니다. 예를 들어, 사용자가 장바구니에 여러 상품을 담고, 이를 주문하여 결제 및 배송 절차를 거치며, 그 후 상품에 대한 리뷰를 남기는 전반적인 과정을 모델링할 수 있습니다.

이 설계를 기반으로 데이터베이스를 구축한 후, SQL 쿼리를 활용해 데이터를 효율적으로 관리하고, 고객의 구매 패턴을 분석할 수 있습니다.

💡 주요 엔티티

  1. 사용자 (User)
    • user_id: 사용자 고유 ID
    • name: 사용자 이름
    • email: 이메일 주소
    • password: 비밀번호
    • address: 사용자 주소
    • phone_number: 전화번호
  2. 상품 (Product)
    • product_id: 상품 고유 ID
    • name: 상품 이름
    • description: 상품 설명
    • price: 가격
    • category_id: 카테고리 ID (외래키)
    • stock_id: 재고 ID (외래키)
  3. 주문 (Order)
    • order_id: 주문 고유 ID
    • user_id: 사용자 ID (외래키)
    • order_date: 주문 날짜
    • status: 주문 상태 (배송 중, 완료 등)
  4. 결제 (Payment)
    • payment_id: 결제 고유 ID
    • order_id: 주문 ID (외래키)
    • payment_date: 결제 날짜
    • amount: 결제 금액
    • payment_method: 결제 방식 (카드, 계좌이체 등)
  5. 배송 (Shipping)
    • shipping_id: 배송 고유 ID
    • order_id: 주문 ID (외래키)
    • shipping_date: 배송 시작 날짜
    • status: 배송 상태 (배송 중, 완료 등)
    • address: 배송 주소
  6. 리뷰 (Review)
    • review_id: 리뷰 고유 ID
    • product_id: 상품 ID (외래키)
    • user_id: 사용자 ID (외래키)
    • rating: 평점
    • comment: 리뷰 내용
    • review_date: 리뷰 작성 날짜
  7. 장바구니 (Cart)
    • cart_id: 장바구니 고유 ID
    • user_id: 사용자 ID (외래키)
    • created_at: 장바구니 생성 날짜
  8. 카테고리 (Category)
    • category_id: 카테고리 고유 ID
    • category_name: 카테고리 이름
  9. 재고 (Inventory)
    • inventory_id: 재고 고유 ID
    • product_id: 상품 ID (외래키)
    • quantity: 재고 수량
  10. 할인 쿠폰 (Coupon)
  • coupon_id: 쿠폰 고유 ID
  • discount: 할인율
  • expiry_date: 유효 기간

🧠 고급 팁

이 데이터베이스 설계는 단순한 구조이지만, 실제로 성능 최적화나 데이터 무결성을 고려할 때 여러 가지 방법을 적용할 수 있습니다.

  • 인덱스 설정: 자주 조회되는 컬럼에 인덱스를 추가하면 성능이 향상됩니다.
  • 트랜잭션 관리: 결제 및 주문 처리 등 중요한 작업은 트랜잭션으로 관리하여 ACID 속성을 보장합니다.

✅ 마무리 요약 및 복습 포인트

  • 관계형 데이터베이스 설계는 데이터의 중복을 최소화하고, 효율적으로 데이터를 관리할 수 있는 중요한 과정입니다.
  • 다양한 엔티티 간 관계를 정의하고 이를 바탕으로 테이블을 설계하면, 실제 운영에 필요한 기능을 잘 구현할 수 있습니다.
  • 실제 Amazon.com과 같은 대규모 시스템을 구축할 때 이러한 관계형 설계를 바탕으로 확장성 있는 시스템을 만들 수 있습니다.

이제 여러분도 Amazon.com과 유사한 데이터베이스를 설계할 준비가 되었습니다. 데이터베이스 설계의 핵심은 명확한 관계 정의입니다. 이 글을 참고하여 데이터베이스 설계를 진행해 보세요!