ERD v0.2433.40

[JBumLee]

• 변경점

  • items 테이블 추가
  • products 테이블에 item_id fk추가
  • itmes csv 아래 참조

• ERD

농축수산물 품목 및 등급 코드표.csv

• DDL

CREATE TABLE `users`
(
    `id`             BIGINT auto_increment NOT NULL,
    `phone_number`   VARCHAR(11)           NOT NULL,
    `password`       VARCHAR(60)           NOT NULL,
    `name`           VARCHAR(30)           NOT NULL,
    `is_seller`      BOOLEAN               NOT NULL,
    `is_resign`      BOOLEAN               NOT NULL,
    `account_number` VARCHAR(14)           NULL,
     primary key (id)
);

CREATE TABLE `addresses`
(
    `id`      BIGINT auto_increment NOT NULL,
    `user_id` BIGINT                NOT NULL,
    `zone_no` VARCHAR(5)            NOT NULL,
    `address` VARCHAR(100)          NOT NULL,
    `detail`  VARCHAR(50)           NOT NULL,
    primary key (id),
    foreign key (user_id) references users (id)
);

CREATE TABLE `farms`
(
    `id`      BIGINT auto_increment NOT NULL,
    `user_id` BIGINT                NOT NULL,
    `detail`  TEXT                  NOT NULL,
    `is_status`  BOOLEAN         NOT NULL  DEFAULT 1,

    primary key (id),
    foreign key (user_id) references users (id)
);

CREATE TABLE `images`
(
    `id`        BIGINT auto_increment NOT NULL,
    `name`      VARCHAR(100)          NOT NULL,
    `extension` VARCHAR(6)            NOT NULL,
    primary key (id)
);

CREATE TABLE `farm_images`
(
    `farm_id`  BIGINT NOT NULL,
    `image_id` BIGINT NOT NULL,
    `is_thumbnail` BOOLEAN NOT NULL,
    foreign key (farm_id) references farms (id),
    foreign key (image_id) references images (id),
    primary key (farm_id, image_id)
);


CREATE TABLE `products`
(
    `id`     BIGINT auto_increment.        NOT NULL,
    `farm_id` BIGINT NOT NULL,
    `name`   VARCHAR(64)           NOT NULL,
    `detail` TEXT                  NOT NULL,
    `price`  INT                   NOT NULL,
    `item_id` BIGINT NOT NULL,
    primary key (id),
    foreign key (farm_id) references farms (id)
    foreign key (item_id) references itmes (id)
);

CREATE TABLE `items`
{
    `id`     BIGINT auto_increment.       NOT NULL,
    `name` VARCHAR(20)        NOT NULL,
     primary key (id)
};

CREATE TABLE `product_images`
(
    `product_id`   BIGINT  NOT NULL,
    `image_id`     BIGINT  NOT NULL,
    `is_thumbnail` BOOLEAN NOT NULL,
    foreign key (product_id) references products (id),
    foreign key (image_id) references images (id),
    primary key (product_id, image_id)
);

CREATE TABLE `carts`
(
    `user_id`    BIGINT NOT NULL,
    `product_id` BIGINT NOT NULL,
    `farm_id`     BIGINT NOT NULL,
    `quantity`   INT    NOT NULL,
    foreign key (user_id) references users (id),
    foreign key (product_id) references products (id),
    foreign key (farm_id) references farms (id),
    primary key (user_id, product_id)
);

CREATE TABLE `order_status`
(
    `id`           VARCHAR(20) NOT NULL,
    `created_date` DATE        NOT NULL,
    primary key (id)
);

CREATE TABLE `orders`
(
    `id`                    BIGINT auto_increment NOT NULL,
    `user_id`               BIGINT                   NOT NULL,
    `status`                VARCHAR(20)           NOT NULL,
    `total_cost`            INT                   NOT NULL,
    `delivery_date`         DATE                  NULL,
    `order_date`            DATE                  NOT NULL,
    `invoice_number`        VARCHAR(5)            NULL,
    `receiver_name`         VARCHAR(30)           NOT NULL,
    `receiver_address`      VARCHAR(100)          NULL,
    `receiver_phone_number` VARCHAR(11)           NOT NULL,
    `meeting_at`            DATETIME              NULL,
    primary key (id),
    foreign key (status) references order_status (id),
    foreign key (user_id) references users (id)
);

CREATE TABLE `reviews`
(
    `id`            BIGINT auto_increment NOT NULL,
    `order_id`      BIGINT                NOT NULL,
    `product_id`    BIGINT                NOT NULL,
    `title`         VARCHAR(50)           NOT NULL,
    `content`       VARCHAR(1000)         NOT NULL,
    `registered_at` DATETIME              NOT NULL,
    `score`         INT                   NOT NULL,
    primary key (id),
    foreign key (order_id) references orders (id),
    foreign key (product_id) references products (id)
);

CREATE TABLE `order_details`
(
    `id`         BIGINT auto_increment NOT NULL,
    `order_id`   BIGINT                NOT NULL,
    `product_id` BIGINT                NOT NULL,
    `quantity`   INT                   NOT NULL,
    `price`      INT                   NOT NULL,
    primary key (id),
    foreign key (order_id) references orders (id),
    foreign key (product_id) references products (id)
);

CREATE TABLE `wish_lists`
(
    `id`         BIGINT auto_increment NOT NULL,
    `farm_id`    BIGINT                NULL,
    `product_id` BIGINT                NULL,
    `user_id`    BIGINT                NOT NULL,
    primary key (id),
    foreign key (farm_id) references farms (id),
    foreign key (product_id) references products (id),
    foreign key (user_id) references users (id)
);

CREATE TABLE `payments`
(
    `id`        bigint auto_increment NOT NULL,
    `order_id`  BIGINT                NOT NULL,
    `create_at` DATETIME              NOT NULL,
    `cost`      INT                   NOT NULL,
    `type`      VARCHAR(20)           NOT NULL,
    primary key (id),
    foreign key (order_id) references orders (id)
);

CREATE TABLE `tags`
(
    `id`   BIGINT auto_increment NOT NULL,
    `name` VARCHAR(10)           NOT NULL,
    primary key (id)
);

CREATE TABLE `product_tags`
(
    `product_id` BIGINT NOT NULL,
    `tag_id`     BIGINT NOT NULL,
    primary key (product_id, tag_id),
    foreign key (product_id) references products (id),
    foreign key (tag_id) references tags (id)
);

CREATE TABLE `categories`
(
    `id`   BIGINT auto_increment NOT NULL,
    `name` VARCHAR(20)           NOT NULL,
    primary key (id)
);

CREATE TABLE `product_categories`
(
    `category_id` BIGINT NOT NULL,
    `product_id`  BIGINT NOT NULL,
    primary key (category_id, product_id),
    foreign key (category_id) references categories (id),
    foreign key (product_id) references products (id)
);