쇼핑몰 구현 3 - db설계

<db 설계>

-- 사용자 테이블
CREATE TABLE members (
    member_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL, 
    password VARCHAR(255) NOT NULL,
    name VARCHAR(100),

    membership VARCHAR(50),  --사용자인지 관리자인지 확인하기 위해
    PRIMARY KEY (member_id)
);

-- 상품 테이블
CREATE TABLE items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT, 
    size VARCHAR(100), 

    tip VARCHAR(100),

    delivery_price INT,
    price INT NOT NULL,

    stock_quantity INT,
    category VARCHAR(50),   

    PRIMARY KEY (item_id)
);

-- 이미지 테이블 
CREATE TABLE upload_images (
    image_id INT AUTO_INCREMENT PRIMARY KEY,
    item_id INT,

    qna_id INT,

    review_id INT,

    original_filename VARCHAR(255),

    saved_filename VARCHAR(255),
    PRIMARY KEY (image_id),
    FOREIGN KEY (item_id) REFERENCES items(item_id),

    FOREIGN KEY (qna_id) REFERENCES qna(qna_id),

    FOREIGN KEY (review_id) REFERENCES reviews(review_id)
);

-- 주문 테이블
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    member_id INT,

    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_price DECIMAL(10, 2) NOT NULL,
    status VARCHAR(50),  -- status: 발송전, 발송완료
    PRIMARY KEY (order_id),
    FOREIGN KEY (member_id) REFERENCES members(member_id)
);

-- 주문 상세 테이블
CREATE TABLE order_details (
    order_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    item_id INT, 

    order_price INT,
    quantity INT NOT NULL,
    PRIMARY KEY (order_detail_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (item_id) REFERENCES items(item_id)
);

-- 배송지 테이블
CREATE TABLE deliverys (
    delivery_id INT AUTO_INCREMENT PRIMARY KEY,
    member_id INT,

    order_id INT,

    delivery_name VARCHAR(100) NOT NULL,
    member_name VARCHAR(100) NOT NULL,
    zipcode VARCHAR(255) NOT NULL,  --우편번호
    street_address VARCHAR(255) NOT NULL,  --도로명주소

    detail_address VARCHAR(255) NOT NULL,  --상세주소
    phone_number VARCHAR(20) NOT NULL,
    PRIMARY KEY (delivery_id),
    FOREIGN KEY (member_id) REFERENCES members(member_id)

    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- 위시리스트 테이블
CREATE TABLE wishlists (
    wishlist_id INT AUTO_INCREMENT PRIMARY KEY,
    member_id INT,
    item_id INT,
    PRIMARY KEY (wishlist_id),
    FOREIGN KEY (member_id) REFERENCES members(member_id),
    FOREIGN KEY (item_id) REFERENCES items(item_id)
);

-- 장바구니 테이블
CREATE TABLE carts (
    cart_id INT AUTO_INCREMENT PRIMARY KEY,
    member_id INT,
    item_id INT,
    quantity INT NOT NULL,
    PRIMARY KEY (cart_id),
    FOREIGN KEY (member_id) REFERENCES members(member_id),
    FOREIGN KEY (item_id) REFERENCES items(item_id)
);

-- 리뷰 테이블
CREATE TABLE reviews (
    review_id INT AUTO_INCREMENT PRIMARY KEY,
    member_id INT,
    item_id INT,
    rating INT NOT NULL,  --별점
    title VARCHAR(50),
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (review_id),
    FOREIGN KEY (member_id) REFERENCES members(member_id),
    FOREIGN KEY (item_id) REFERENCES items(item_id)
);

-- Q&A 테이블
CREATE TABLE qna (
    qna_id INT AUTO_INCREMENT PRIMARY KEY,
    member_id INT,
    item_id INT,

    title VARCHAR(50),
    content TEXT,
    answer TEXT,  --답변하면 answer에 기록되며 answer_status COMPLETE으로 변경

    answer_status VARCHAR(50), --answer_status: READY, COMPLETE
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (qna_id),
    FOREIGN KEY (member_id) REFERENCES members(member_id),
    FOREIGN KEY (item_id) REFERENCES items(item_id)
);