[DB] 초기 ERD 구성

[Judy-Choi]

초기 DB설계를 위한 ERD 구성

ERD script

// 유저 테이블
Table users {    
  id int [PK]
  email varchar
  password  varchar
  name varchar
  mobile varchar
  // DB 에 컬럼 생성 시 초기값 "1"
  level varchar
  // DB 에 컬럼 생성 시 초기값 0
  point decimal
  created_at  timestamp
  updated_at  timestamp
}

// 제품정보
Table products {    
  id int [PK]
  name  varchar
  price decimal
  // 제품 제공 기본 단위 (ex : 600g, 12개)
  base_unit varchar
  product_image_id int
  product_option_id int
  categories int
  // 판매수량
  sold int
  created_at  timestamp
  updated_at  timestamp
}

// 제품 옵션
Table product_options {    
  id int [PK]
  product_id int
  name int
  // 옵션별 재고
  stock int
  created_at  timestamp
  updated_at  timestamp
}

// 제품 이미지
Table product_images {
  id int [pk]
  thumbnail_image varchar
  detail_image varchar
}

// 장바구니
Table carts {
  id int [pk]
  user_id int
  product_id id
  // 주문수량
  quantity int
  created_at timestamp
  updated_at timestamp
}

// 주문 정보
Table orders {
  id int [pk]
  user_id int
  // 배송정보
  delivery_id int
  // 주문할 제품 정보
  // 동시에 여러개의 제품을 주문할 경우
  // order_producs 테이블에서 같은 order_products_number 를 갖는 row 를 가져온다
  order_number int
  // 총 주문금액
  // 초기값 0
  payment int
}

// 배송정보
Table deliveries{
  id int [pk]
  from_name varchar
  from_mobile varchar
  from_email varchar
  to_name varchar
  to_mobile vharchar
  to_address varchar
}

// 주문상품
Table order_producs {
  id int [pk]
  // 같은 주문번호를 갖는 상품은 동시에 주문한 것.
  order_number id
  product_id id
  // 주문수량
  quantity int
  created_at timestamp
  updated_at timestamp
}



Ref: "products"."product_image_id" - "product_images"."id"

Ref: "products"."product_option_id" < "product_options"."id"

Ref: "orders"."delivery_id" - "deliveries"."id"

Ref: "orders"."user_id" > "users"."id"

Ref: "orders"."order_number" - "order_producs"."order_number"


Ref: "users"."id" < "carts"."user_id"

Ref: "carts"."product_id" < "products"."id"

Ref: "order_producs"."product_id" < "products"."id"

SQL script

CREATE TABLE `users` (
  `id` int PRIMARY KEY,
  `email` varchar(255),
  `password` varchar(255),
  `name` varchar(255),
  `mobile` varchar(255),
  `level` varchar(255),
  `point` decimal,
  `created_at` timestamp,
  `updated_at` timestamp
);

CREATE TABLE `products` (
  `id` int PRIMARY KEY,
  `name` varchar(255),
  `price` decimal,
  `base_unit` varchar(255),
  `product_image_id` int,
  `product_option_id` int,
  `categories` int,
  `sold` int,
  `created_at` timestamp,
  `updated_at` timestamp
);

CREATE TABLE `product_options` (
  `id` int PRIMARY KEY,
  `product_id` int,
  `name` int,
  `stock` int,
  `created_at` timestamp,
  `updated_at` timestamp
);

CREATE TABLE `product_images` (
  `id` int PRIMARY KEY,
  `thumbnail_image` varchar(255),
  `detail_image` varchar(255)
);

CREATE TABLE `carts` (
  `id` int PRIMARY KEY,
  `user_id` int,
  `product_id` id,
  `quantity` int,
  `created_at` timestamp,
  `updated_at` timestamp
);

CREATE TABLE `orders` (
  `id` int PRIMARY KEY,
  `user_id` int,
  `delivery_id` int,
  `order_number` int,
  `payment` int
);

CREATE TABLE `deliveries` (
  `id` int PRIMARY KEY,
  `from_name` varchar(255),
  `from_mobile` varchar(255),
  `from_email` varchar(255),
  `to_name` varchar(255),
  `to_mobile` vharchar,
  `to_address` varchar(255)
);

CREATE TABLE `order_producs` (
  `id` int PRIMARY KEY,
  `order_number` id,
  `product_id` id,
  `quantity` int,
  `created_at` timestamp,
  `updated_at` timestamp
);

ALTER TABLE `product_images` ADD FOREIGN KEY (`id`) REFERENCES `products` (`product_image_id`);

ALTER TABLE `product_options` ADD FOREIGN KEY (`id`) REFERENCES `products` (`product_option_id`);

ALTER TABLE `deliveries` ADD FOREIGN KEY (`id`) REFERENCES `orders` (`delivery_id`);

ALTER TABLE `orders` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE `order_producs` ADD FOREIGN KEY (`order_number`) REFERENCES `orders` (`order_number`);

ALTER TABLE `carts` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE `products` ADD FOREIGN KEY (`id`) REFERENCES `carts` (`product_id`);

ALTER TABLE `products` ADD FOREIGN KEY (`id`) REFERENCES `order_producs` (`product_id`);