데이터베이스 소개

배달의 민족 서비스를 구현해보기 위해 테이블을 만들어 보았다.

• DDL 코드

-- 테이블 순서는 관계를 고려하여 한 번에 실행해도 에러가 발생하지 않게 정렬되었습니다.

-- Restaurant Table Create SQL
CREATE TABLE Restaurant
(
    `restaurantIdx`      BIGINT          NOT NULL    AUTO_INCREMENT, 
    `name`               VARCHAR(45)     NOT NULL, 
    `delivery_category`  VARCHAR(45)     NOT NULL    COMMENT '배민1, 배달, 포장', 
    `category`           VARCHAR(45)     NOT NULL    COMMENT '한식, 중식 등등', 
    `minimumCost`        INT UNSIGNED    NOT NULL, 
    `deliveryCost`       INT UNSIGNED    NOT NULL, 
    `deliveryTime`       INT UNSIGNED    NOT NULL, 
    `location`           VARCHAR(45)     NOT NULL, 
    `operatingTime`      VARCHAR(45)     NOT NULL    COMMENT '운영 시간', 
    `holiday`            VARCHAR(45)     NOT NULL    COMMENT '휴무일', 
    `explantion`         TEXT            NOT NULL    COMMENT '가계 설명', 
     PRIMARY KEY (restaurantIdx)
);


-- Menu Table Create SQL
CREATE TABLE Menu
(
    `menuIdx`        BIGINT          NOT NULL    AUTO_INCREMENT, 
    `restaurantIdx`  BIGINT          NOT NULL, 
    `name`           VARCHAR(45)     NOT NULL, 
    `price`          INT UNSIGNED    NOT NULL, 
    `explantion`     Text            NOT NULL    COMMENT '메뉴 설명', 
    `tag1`           VARCHAR(45)     NOT NULL    COMMENT '일반 메뉴, 인기 메뉴, 추천 메뉴 등', 
    `tag2`           VARCHAR(45)     NULL        COMMENT '인기 메뉴, 추천 메뉴 등등', 
     PRIMARY KEY (menuIdx)
);

ALTER TABLE Menu
    ADD CONSTRAINT FK_Menu_restaurantIdx_Restaurant_restaurantIdx FOREIGN KEY (restaurantIdx)
        REFERENCES Restaurant (restaurantIdx) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- User Table Create SQL
CREATE TABLE User
(
    `userIdx`        BIGINT          NOT NULL    AUTO_INCREMENT, 
    `email`          VARCHAR(100)    NOT NULL, 
    `password`       VARCHAR(45)     NOT NULL, 
    `name`           VARCHAR(45)     NOT NULL, 
    `phoneNumber`    VARCHAR(45)     NOT NULL, 
    `date of Birth`  VARCHAR(45)     NOT NULL, 
    `createAt`       TIMESTAMP       NOT NULL    DEFAULT CURRENT_TIMESTAMP, 
    `updateAt`       TIMESTAMP       NOT NULL    DEFAULT current_timestamp on update current_timestamp, 
    `status`         VARCHAR(1)      NOT NULL    DEFAULT 'A' COMMENT 'A:활성상태, D:탈퇴, B:블랙리스트', 
    `agree1`         BOOLEAN         NOT NULL    DEFAULT 0 COMMENT '1:개인정보 제3자 제공 동의, 0:개인정보 제3자 제공 비동의', 
    `agree2`         BOOLEAN         NOT NULL    DEFAULT 0 COMMENT '1:마케팅 정보 메일, SNS 수신동의, 0:마케팅 정보 메일, SNS 수신비동의', 
     PRIMARY KEY (userIdx)
);


-- Order_part Table Create SQL
CREATE TABLE Order_part
(
    `orderIdx`           BIGINT          NOT NULL    AUTO_INCREMENT, 
    `restaurantRequest`  TEXT            NOT NULL, 
    `deliveryRequest`    TEXT            NOT NULL, 
    `payment`            VARCHAR(45)     NOT NULL, 
    `coupon`             BOOLEAN         NOT NULL    DEFAULT 0 COMMENT '0:쿠폰 사용 안함,1:쿠폰 사용', 
    `cashReceipts`       BOOLEAN         NOT NULL    DEFAULT 0 COMMENT '0:현금영수증X, 1:현금영수증O', 
    `totalprice`         INT UNSIGNED    NOT NULL, 
     PRIMARY KEY (orderIdx)
);


-- OrderHistory Table Create SQL
CREATE TABLE OrderHistory
(
    `historyIdx`     BIGINT    NOT NULL    AUTO_INCREMENT, 
    `userIdx`        BIGINT    NOT NULL, 
    `restaurantIdx`  BIGINT    NOT NULL, 
    `menuIdx`        BIGINT    NOT NULL, 
    `orderIdx`       BIGINT    NOT NULL, 
     PRIMARY KEY (historyIdx)
);

ALTER TABLE OrderHistory
    ADD CONSTRAINT FK_OrderHistory_userIdx_User_userIdx FOREIGN KEY (userIdx)
        REFERENCES User (userIdx) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE OrderHistory
    ADD CONSTRAINT FK_OrderHistory_restaurantIdx_Restaurant_restaurantIdx FOREIGN KEY (restaurantIdx)
        REFERENCES Restaurant (restaurantIdx) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE OrderHistory
    ADD CONSTRAINT FK_OrderHistory_menuIdx_Menu_menuIdx FOREIGN KEY (menuIdx)
        REFERENCES Menu (menuIdx) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE OrderHistory
    ADD CONSTRAINT FK_OrderHistory_orderIdx_Order_part_orderIdx FOREIGN KEY (orderIdx)
        REFERENCES Order_part (orderIdx) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- Coupon Table Create SQL
CREATE TABLE Coupon
(
    `couponidx`       BIGINT          NOT NULL    AUTO_INCREMENT, 
    `userIdx`         BIGINT          NOT NULL, 
    `name`            VARCHAR(45)     NOT NULL, 
    `discount_price`  INT UNSIGNED    NOT NULL, 
    `status`          BOOLEAN         NOT NULL    DEFAULT 0 COMMENT '0:미사용.1:사용', 
     PRIMARY KEY (couponidx)
);

ALTER TABLE Coupon
    ADD CONSTRAINT FK_Coupon_userIdx_User_userIdx FOREIGN KEY (userIdx)
        REFERENCES User (userIdx) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- Plus_option Table Create SQL
CREATE TABLE Plus_option
(
    `optionIdx`  BIGINT         NOT NULL    AUTO_INCREMENT, 
    `menuIdx`    BIGINT         NOT NULL, 
    `option1`    VARCHAR(45)    NULL, 
    `option2`    VARCHAR(45)    NULL, 
    `option3`    VARCHAR(45)    NULL, 
    `option4`    VARCHAR(45)    NULL, 
    `option5`    VARCHAR(45)    NULL, 
     PRIMARY KEY (optionIdx)
);

ALTER TABLE Plus_option
    ADD CONSTRAINT FK_Plus_option_menuIdx_Menu_menuIdx FOREIGN KEY (menuIdx)
        REFERENCES Menu (menuIdx) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- Image Table Create SQL
CREATE TABLE Image
(
    `ImageIdx`       BIGINT    NOT NULL    AUTO_INCREMENT, 
    `restaurantIdx`  BIGINT    NOT NULL, 
    `image1`         TEXT      NOT NULL, 
    `image2`         TEXR      NOT NULL, 
    `image3`         TEXT      NOT NULL, 
    `menuIdx`        BIGINT    NOT NULL, 
     PRIMARY KEY (ImageIdx)
);

ALTER TABLE Image
    ADD CONSTRAINT FK_Image_restaurantIdx_Restaurant_restaurantIdx FOREIGN KEY (restaurantIdx)
        REFERENCES Restaurant (restaurantIdx) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE Image
    ADD CONSTRAINT FK_Image_menuIdx_Menu_menuIdx FOREIGN KEY (menuIdx)
        REFERENCES Menu (menuIdx) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- Review Table Create SQL
CREATE TABLE Review
(
    `reviewIdx`      BIGINT         NOT NULL    AUTO_INCREMENT, 
    `userIdx`        BIGINT         NOT NULL, 
    `restaurantIdx`  BIGINT         NOT NULL, 
    `grade`          INT            NOT NULL, 
    `review`         TEXT           NOT NULL, 
    `tag`            VARCHAR(45)    NULL        COMMENT '추천 등', 
    `menuIdx`        BIGINT         NOT NULL, 
     PRIMARY KEY (reviewIdx)
);

ALTER TABLE Review
    ADD CONSTRAINT FK_Review_userIdx_User_userIdx FOREIGN KEY (userIdx)
        REFERENCES User (userIdx) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE Review
    ADD CONSTRAINT FK_Review_restaurantIdx_Restaurant_restaurantIdx FOREIGN KEY (restaurantIdx)
        REFERENCES Restaurant (restaurantIdx) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE Review
    ADD CONSTRAINT FK_Review_menuIdx_Menu_menuIdx FOREIGN KEY (menuIdx)
        REFERENCES Menu (menuIdx) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- Basket Table Create SQL
CREATE TABLE Basket
(
    `basketIdx`      BIGINT          NOT NULL    AUTO_INCREMENT, 
    `restaurantIdx`  BIGINT          NOT NULL, 
    `menuIdx`        BIGINT          NOT NULL, 
    `quantity`       INT UNSIGNED    NOT NULL, 
    `deliveryType`   VARCHAR(45)     NOT NULL, 
    `userIdx`        BIGINT          NOT NULL, 
     PRIMARY KEY (basketIdx)
);

ALTER TABLE Basket
    ADD CONSTRAINT FK_Basket_restaurantIdx_Restaurant_restaurantIdx FOREIGN KEY (restaurantIdx)
        REFERENCES Restaurant (restaurantIdx) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE Basket
    ADD CONSTRAINT FK_Basket_menuIdx_Menu_menuIdx FOREIGN KEY (menuIdx)
        REFERENCES Menu (menuIdx) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE Basket
    ADD CONSTRAINT FK_Basket_userIdx_User_userIdx FOREIGN KEY (userIdx)
        REFERENCES User (userIdx) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- UserDeliveryAddress Table Create SQL
CREATE TABLE UserDeliveryAddress
(
    `addressIdx`       BIGINT         NOT NULL    AUTO_INCREMENT, 
    `userIdx`          BIGINT         NOT NULL, 
    `address`          VARCHAR(45)    NOT NULL, 
    `detailedAddress`  VARCHAR(45)    NOT NULL, 
    `addressType`      VARCHAR(45)    NOT NULL    COMMENT '집, 회사 등등', 
    `createAt`         TIMESTAMP      NOT NULL    DEFAULT CURRENT_TIMESTAMP, 
    `updateAt`         TIMESTAMP      NOT NULL    DEFAULT current_timestamp on update current_timestamp, 
     PRIMARY KEY (addressIdx)
);

ALTER TABLE UserDeliveryAddress
    ADD CONSTRAINT FK_UserDeliveryAddress_userIdx_User_userIdx FOREIGN KEY (userIdx)
        REFERENCES User (userIdx) ON DELETE RESTRICT ON UPDATE RESTRICT;