디비설계

강재영

Dec 18, 2024

Contents

DB주소 더미데이터

DB주소

오늘의집밴치마킹

Draw ERD with your team members. All states are shared in real time. And it's FREE. Database modeling tool.

https://www.erdcloud.com/d/kqzYwBnefgvNN2eFX

더미데이터


CREATE TABLE `USERS` (
	`ID`	NOT NULL	NOT NULL,
	`USERNAME`	NOT NULL	NULL,
	`PASSWORD`	NOT NULL	NULL,
	`NAME`	NOT NULL	NULL,
	`AGE`	VARCHAR(255)	NULL,
	`EMAIL`	NOT NULL	NULL,
	`PHONE`	NOT NULL	NULL,
	`ADDRESS`	NOT NULL	NULL
);

CREATE TABLE `POST` (
	`ID`	NOT NULL	NOT NULL,
	`BRAND_ID`	NOT NULL	NOT NULL,
	`SUB_CATEGORY_ID`	NOT NULL	NOT NULL,
	`TITLE`	NOT NULL	NULL,
	`CONTENT`	NOT NULL	NULL,
	`PRICE`	NOT NULL	NULL,
	`MAINPHOTO`	NOT NULL	NULL
);

CREATE TABLE `PHOTO` (
	`ID`	NOT NULL	NOT NULL,
	`POST_ID`	NOT NULL	NOT NULL,
	`URL`	NOT NULL	NULL,
	`Field`	VARCHAR(255)	NULL
);

CREATE TABLE `REVIEW` (
	`ID`	NOT NULL	NOT NULL,
	`POST_ID`	NOT NULL	NOT NULL,
	`USER_ID`	NOT NULL	NOT NULL,
	`CONTENT`	NOT NULL	NULL,
	`URL`	NOT NULL	NULL,
	`STAR`	NOT NULL	NULL,
	`DATE`	NOT NULL	NULL
);

CREATE TABLE `MAINCATEGORY` (
	`ID`	NOT NULL	NOT NULL,
	`MAIN_CATEGORY_NAME`	NOT NULL	NOT NULL
);

CREATE TABLE `SUBCATEGORY` (
	`ID`	NOT NULL	NOT NULL,
	`CATEGORY_ID`	NOT NULL	NOT NULL,
	`SUB_CATEGORY_NAME`	NOT NULL	NOT NULL
);

CREATE TABLE `ORDER` (
	`ID`	NOT NULL	NOT NULL,
	`USER_ID`	NOT NULL	NOT NULL,
	`PARTNER_ORDER_ID`	NOT NULL	NULL,
	`TOTAL_AMOUNT`	NOT NULL	NULL,
	`QUANTITY`	NOT NULL	NULL
);

CREATE TABLE `PAYMENT` (
	`ID`	NOT NULL	NOT NULL,
	`ORDER_ID`	NOT NULL	NOT NULL,
	`TID`	NOT NLL	NULL,
	`PG_TOKEN`	NOT NULL	NULL,
	`TOTAL_AMOUNT`	NOT NULL	NULL
);

CREATE TABLE `ORDERDETAIL` (
	`ID`	NOT NULL	NOT NULL,
	`ORDER_ID`	NOT NULL	NOT NULL,
	`INVENTORY_ID`	NOT NULL	NOT NULL,
	`COUNT`	NOT NULL	NOT NULL,
	`PRICE`	NOT NULL	NULL,
	`TOTAL_PRICE`	NOT NULL	NULL
);

CREATE TABLE `BRAND` (
	`ID`	NOT NULL	NOT NULL,
	`NAME`	NOT NULL	NULL
);

CREATE TABLE `PRODUCT` (
	`ID`	NOT NULL	NOT NULL,
	`POST_ID`	NOT NULL	NOT NULL,
	`NAME`	NOT NULL	NULL,
	`PRICE`	NOT NULL	NULL
);

CREATE TABLE `OPTIONTYPE` (
	`ID`	NOT NULL	NOT NULL,
	`TYPE`	NOT NULL	NULL
);

CREATE TABLE `OPTION` (
	`ID`	NOT NULL	NOT NULL,
	`OPTIONTYPE_ID`	NOT NULL	NOT NULL,
	`PRODUCT_ID`	NOT NULL	NOT NULL,
	`CONTENT`	NOT NULL	NULL,
	`PRICE`	VARCHAR(255)	NULL,
	`PRIORITY`	NOTNULL	NULL,
	`Field`	VARCHAR(255)	NULL
);

CREATE TABLE `CART` (
	`ID`	NOT NULL	NOT NULL,
	`USER_ID`	NOT NULL	NOT NULL,
	`INVENTORY_ID`	NOT NULL	NOT NULL,
	`COUNT`	NOT NULL	NOT NULL,
	`PRICE`	NOT NULL	NULL,
	`TOTAL_PRICE`	NOT NULL	NULL
);

CREATE TABLE `INVENTORY` (
	`ID`	NOT NULL	NOT NULL,
	`OPTION1`	NOT NULL	NOT NULL,
	`OPTION2`	NOT NULL	NOT NULL,
	`PRODUCTID`	NOT NULL	NOT NULL,
	`stock`	NOTNUL	NULL
);

CREATE TABLE `PRODUCTOPTION` (
	`ID`	NOT NULL	NOT NULL,
	`PRODUCTID`	NOT NULL	NOT NULL,
	`OPTIONID`	NOT NULL	NOT NULL,
	`priorty`	NOT NULL	NULL
);

ALTER TABLE `USERS` ADD CONSTRAINT `PK_USERS` PRIMARY KEY (
	`ID`
);

ALTER TABLE `POST` ADD CONSTRAINT `PK_POST` PRIMARY KEY (
	`ID`,
	`BRAND_ID`,
	`SUB_CATEGORY_ID`
);

ALTER TABLE `PHOTO` ADD CONSTRAINT `PK_PHOTO` PRIMARY KEY (
	`ID`,
	`POST_ID`
);

ALTER TABLE `REVIEW` ADD CONSTRAINT `PK_REVIEW` PRIMARY KEY (
	`ID`,
	`POST_ID`,
	`USER_ID`
);

ALTER TABLE `MAINCATEGORY` ADD CONSTRAINT `PK_MAINCATEGORY` PRIMARY KEY (
	`ID`
);

ALTER TABLE `SUBCATEGORY` ADD CONSTRAINT `PK_SUBCATEGORY` PRIMARY KEY (
	`ID`,
	`CATEGORY_ID`
);

ALTER TABLE `ORDER` ADD CONSTRAINT `PK_ORDER` PRIMARY KEY (
	`ID`,
	`USER_ID`
);

ALTER TABLE `PAYMENT` ADD CONSTRAINT `PK_PAYMENT` PRIMARY KEY (
	`ID`,
	`ORDER_ID`
);

ALTER TABLE `ORDERDETAIL` ADD CONSTRAINT `PK_ORDERDETAIL` PRIMARY KEY (
	`ID`,
	`ORDER_ID`,
	`INVENTORY_ID`
);

ALTER TABLE `BRAND` ADD CONSTRAINT `PK_BRAND` PRIMARY KEY (
	`ID`
);

ALTER TABLE `PRODUCT` ADD CONSTRAINT `PK_PRODUCT` PRIMARY KEY (
	`ID`,
	`POST_ID`
);

ALTER TABLE `OPTIONTYPE` ADD CONSTRAINT `PK_OPTIONTYPE` PRIMARY KEY (
	`ID`
);

ALTER TABLE `OPTION` ADD CONSTRAINT `PK_OPTION` PRIMARY KEY (
	`ID`,
	`OPTIONTYPE_ID`,
	`PRODUCT_ID`
);

ALTER TABLE `CART` ADD CONSTRAINT `PK_CART` PRIMARY KEY (
	`ID`,
	`USER_ID`,
	`INVENTORY_ID`
);

ALTER TABLE `INVENTORY` ADD CONSTRAINT `PK_INVENTORY` PRIMARY KEY (
	`ID`,
	`OPTION1`,
	`OPTION2`,
	`PRODUCTID`
);

ALTER TABLE `PRODUCTOPTION` ADD CONSTRAINT `PK_PRODUCTOPTION` PRIMARY KEY (
	`ID`,
	`PRODUCTID`,
	`OPTIONID`
);

ALTER TABLE `POST` ADD CONSTRAINT `FK_BRAND_TO_POST_1` FOREIGN KEY (
	`BRAND_ID`
)
REFERENCES `BRAND` (
	`ID`
);

ALTER TABLE `POST` ADD CONSTRAINT `FK_SUBCATEGORY_TO_POST_1` FOREIGN KEY (
	`SUB_CATEGORY_ID`
)
REFERENCES `SUBCATEGORY` (
	`ID`
);

ALTER TABLE `PHOTO` ADD CONSTRAINT `FK_POST_TO_PHOTO_1` FOREIGN KEY (
	`POST_ID`
)
REFERENCES `POST` (
	`ID`
);

ALTER TABLE `REVIEW` ADD CONSTRAINT `FK_POST_TO_REVIEW_1` FOREIGN KEY (
	`POST_ID`
)
REFERENCES `POST` (
	`ID`
);

ALTER TABLE `REVIEW` ADD CONSTRAINT `FK_USERS_TO_REVIEW_1` FOREIGN KEY (
	`USER_ID`
)
REFERENCES `USERS` (
	`ID`
);

ALTER TABLE `SUBCATEGORY` ADD CONSTRAINT `FK_MAINCATEGORY_TO_SUBCATEGORY_1` FOREIGN KEY (
	`CATEGORY_ID`
)
REFERENCES `MAINCATEGORY` (
	`ID`
);

ALTER TABLE `ORDER` ADD CONSTRAINT `FK_USERS_TO_ORDER_1` FOREIGN KEY (
	`USER_ID`
)
REFERENCES `USERS` (
	`ID`
);

ALTER TABLE `PAYMENT` ADD CONSTRAINT `FK_ORDER_TO_PAYMENT_1` FOREIGN KEY (
	`ORDER_ID`
)
REFERENCES `ORDER` (
	`ID`
);

ALTER TABLE `ORDERDETAIL` ADD CONSTRAINT `FK_ORDER_TO_ORDERDETAIL_1` FOREIGN KEY (
	`ORDER_ID`
)
REFERENCES `ORDER` (
	`ID`
);

ALTER TABLE `ORDERDETAIL` ADD CONSTRAINT `FK_INVENTORY_TO_ORDERDETAIL_1` FOREIGN KEY (
	`INVENTORY_ID`
)
REFERENCES `INVENTORY` (
	`ID`
);

ALTER TABLE `PRODUCT` ADD CONSTRAINT `FK_POST_TO_PRODUCT_1` FOREIGN KEY (
	`POST_ID`
)
REFERENCES `POST` (
	`ID`
);

ALTER TABLE `OPTION` ADD CONSTRAINT `FK_OPTIONTYPE_TO_OPTION_1` FOREIGN KEY (
	`OPTIONTYPE_ID`
)
REFERENCES `OPTIONTYPE` (
	`ID`
);

ALTER TABLE `OPTION` ADD CONSTRAINT `FK_PRODUCT_TO_OPTION_1` FOREIGN KEY (
	`PRODUCT_ID`
)
REFERENCES `PRODUCT` (
	`ID`
);

ALTER TABLE `CART` ADD CONSTRAINT `FK_USERS_TO_CART_1` FOREIGN KEY (
	`USER_ID`
)
REFERENCES `USERS` (
	`ID`
);

ALTER TABLE `CART` ADD CONSTRAINT `FK_INVENTORY_TO_CART_1` FOREIGN KEY (
	`INVENTORY_ID`
)
REFERENCES `INVENTORY` (
	`ID`
);

ALTER TABLE `INVENTORY` ADD CONSTRAINT `FK_OPTION_TO_INVENTORY_1` FOREIGN KEY (
	`OPTION1`
)
REFERENCES `OPTION` (
	`ID`
);

ALTER TABLE `INVENTORY` ADD CONSTRAINT `FK_OPTION_TO_INVENTORY_2` FOREIGN KEY (
	`OPTION2`
)
REFERENCES `OPTION` (
	`OPTIONTYPE_ID`
);

ALTER TABLE `INVENTORY` ADD CONSTRAINT `FK_PRODUCT_TO_INVENTORY_1` FOREIGN KEY (
	`PRODUCTID`
)
REFERENCES `PRODUCT` (
	`ID`
);

ALTER TABLE `PRODUCTOPTION` ADD CONSTRAINT `FK_PRODUCT_TO_PRODUCTOPTION_1` FOREIGN KEY (
	`PRODUCTID`
)
REFERENCES `PRODUCT` (
	`ID`
);

ALTER TABLE `PRODUCTOPTION` ADD CONSTRAINT `FK_OPTION_TO_PRODUCTOPTION_1` FOREIGN KEY (
	`OPTIONID`
)
REFERENCES `OPTION` (
	`ID`
);

Contents

DB주소 더미데이터

CREATE TABLE `USERS` ( `ID` NOT NULL NOT NULL, `USERNAME` NOT NULL NULL, `PASSWORD` NOT NULL NULL, `NAME` NOT NULL NULL, `AGE` VARCHAR(255) NULL, `EMAIL` NOT NULL NULL, `PHONE` NOT NULL NULL, `ADDRESS` NOT NULL NULL ); CREATE TABLE `POST` ( `ID` NOT NULL NOT NULL, `BRAND_ID` NOT NULL NOT NULL, `SUB_CATEGORY_ID` NOT NULL NOT NULL, `TITLE` NOT NULL NULL, `CONTENT` NOT NULL NULL, `PRICE` NOT NULL NULL, `MAINPHOTO` NOT NULL NULL ); CREATE TABLE `PHOTO` ( `ID` NOT NULL NOT NULL, `POST_ID` NOT NULL NOT NULL, `URL` NOT NULL NULL, `Field` VARCHAR(255) NULL ); CREATE TABLE `REVIEW` ( `ID` NOT NULL NOT NULL, `POST_ID` NOT NULL NOT NULL, `USER_ID` NOT NULL NOT NULL, `CONTENT` NOT NULL NULL, `URL` NOT NULL NULL, `STAR` NOT NULL NULL, `DATE` NOT NULL NULL ); CREATE TABLE `MAINCATEGORY` ( `ID` NOT NULL NOT NULL, `MAIN_CATEGORY_NAME` NOT NULL NOT NULL ); CREATE TABLE `SUBCATEGORY` ( `ID` NOT NULL NOT NULL, `CATEGORY_ID` NOT NULL NOT NULL, `SUB_CATEGORY_NAME` NOT NULL NOT NULL ); CREATE TABLE `ORDER` ( `ID` NOT NULL NOT NULL, `USER_ID` NOT NULL NOT NULL, `PARTNER_ORDER_ID` NOT NULL NULL, `TOTAL_AMOUNT` NOT NULL NULL, `QUANTITY` NOT NULL NULL ); CREATE TABLE `PAYMENT` ( `ID` NOT NULL NOT NULL, `ORDER_ID` NOT NULL NOT NULL, `TID` NOT NLL NULL, `PG_TOKEN` NOT NULL NULL, `TOTAL_AMOUNT` NOT NULL NULL ); CREATE TABLE `ORDERDETAIL` ( `ID` NOT NULL NOT NULL, `ORDER_ID` NOT NULL NOT NULL, `INVENTORY_ID` NOT NULL NOT NULL, `COUNT` NOT NULL NOT NULL, `PRICE` NOT NULL NULL, `TOTAL_PRICE` NOT NULL NULL ); CREATE TABLE `BRAND` ( `ID` NOT NULL NOT NULL, `NAME` NOT NULL NULL ); CREATE TABLE `PRODUCT` ( `ID` NOT NULL NOT NULL, `POST_ID` NOT NULL NOT NULL, `NAME` NOT NULL NULL, `PRICE` NOT NULL NULL ); CREATE TABLE `OPTIONTYPE` ( `ID` NOT NULL NOT NULL, `TYPE` NOT NULL NULL ); CREATE TABLE `OPTION` ( `ID` NOT NULL NOT NULL, `OPTIONTYPE_ID` NOT NULL NOT NULL, `PRODUCT_ID` NOT NULL NOT NULL, `CONTENT` NOT NULL NULL, `PRICE` VARCHAR(255) NULL, `PRIORITY` NOTNULL NULL, `Field` VARCHAR(255) NULL ); CREATE TABLE `CART` ( `ID` NOT NULL NOT NULL, `USER_ID` NOT NULL NOT NULL, `INVENTORY_ID` NOT NULL NOT NULL, `COUNT` NOT NULL NOT NULL, `PRICE` NOT NULL NULL, `TOTAL_PRICE` NOT NULL NULL ); CREATE TABLE `INVENTORY` ( `ID` NOT NULL NOT NULL, `OPTION1` NOT NULL NOT NULL, `OPTION2` NOT NULL NOT NULL, `PRODUCTID` NOT NULL NOT NULL, `stock` NOTNUL NULL ); CREATE TABLE `PRODUCTOPTION` ( `ID` NOT NULL NOT NULL, `PRODUCTID` NOT NULL NOT NULL, `OPTIONID` NOT NULL NOT NULL, `priorty` NOT NULL NULL ); ALTER TABLE `USERS` ADD CONSTRAINT `PK_USERS` PRIMARY KEY ( `ID` ); ALTER TABLE `POST` ADD CONSTRAINT `PK_POST` PRIMARY KEY ( `ID`, `BRAND_ID`, `SUB_CATEGORY_ID` ); ALTER TABLE `PHOTO` ADD CONSTRAINT `PK_PHOTO` PRIMARY KEY ( `ID`, `POST_ID` ); ALTER TABLE `REVIEW` ADD CONSTRAINT `PK_REVIEW` PRIMARY KEY ( `ID`, `POST_ID`, `USER_ID` ); ALTER TABLE `MAINCATEGORY` ADD CONSTRAINT `PK_MAINCATEGORY` PRIMARY KEY ( `ID` ); ALTER TABLE `SUBCATEGORY` ADD CONSTRAINT `PK_SUBCATEGORY` PRIMARY KEY ( `ID`, `CATEGORY_ID` ); ALTER TABLE `ORDER` ADD CONSTRAINT `PK_ORDER` PRIMARY KEY ( `ID`, `USER_ID` ); ALTER TABLE `PAYMENT` ADD CONSTRAINT `PK_PAYMENT` PRIMARY KEY ( `ID`, `ORDER_ID` ); ALTER TABLE `ORDERDETAIL` ADD CONSTRAINT `PK_ORDERDETAIL` PRIMARY KEY ( `ID`, `ORDER_ID`, `INVENTORY_ID` ); ALTER TABLE `BRAND` ADD CONSTRAINT `PK_BRAND` PRIMARY KEY ( `ID` ); ALTER TABLE `PRODUCT` ADD CONSTRAINT `PK_PRODUCT` PRIMARY KEY ( `ID`, `POST_ID` ); ALTER TABLE `OPTIONTYPE` ADD CONSTRAINT `PK_OPTIONTYPE` PRIMARY KEY ( `ID` ); ALTER TABLE `OPTION` ADD CONSTRAINT `PK_OPTION` PRIMARY KEY ( `ID`, `OPTIONTYPE_ID`, `PRODUCT_ID` ); ALTER TABLE `CART` ADD CONSTRAINT `PK_CART` PRIMARY KEY ( `ID`, `USER_ID`, `INVENTORY_ID` ); ALTER TABLE `INVENTORY` ADD CONSTRAINT `PK_INVENTORY` PRIMARY KEY ( `ID`, `OPTION1`, `OPTION2`, `PRODUCTID` ); ALTER TABLE `PRODUCTOPTION` ADD CONSTRAINT `PK_PRODUCTOPTION` PRIMARY KEY ( `ID`, `PRODUCTID`, `OPTIONID` ); ALTER TABLE `POST` ADD CONSTRAINT `FK_BRAND_TO_POST_1` FOREIGN KEY ( `BRAND_ID` ) REFERENCES `BRAND` ( `ID` ); ALTER TABLE `POST` ADD CONSTRAINT `FK_SUBCATEGORY_TO_POST_1` FOREIGN KEY ( `SUB_CATEGORY_ID` ) REFERENCES `SUBCATEGORY` ( `ID` ); ALTER TABLE `PHOTO` ADD CONSTRAINT `FK_POST_TO_PHOTO_1` FOREIGN KEY ( `POST_ID` ) REFERENCES `POST` ( `ID` ); ALTER TABLE `REVIEW` ADD CONSTRAINT `FK_POST_TO_REVIEW_1` FOREIGN KEY ( `POST_ID` ) REFERENCES `POST` ( `ID` ); ALTER TABLE `REVIEW` ADD CONSTRAINT `FK_USERS_TO_REVIEW_1` FOREIGN KEY ( `USER_ID` ) REFERENCES `USERS` ( `ID` ); ALTER TABLE `SUBCATEGORY` ADD CONSTRAINT `FK_MAINCATEGORY_TO_SUBCATEGORY_1` FOREIGN KEY ( `CATEGORY_ID` ) REFERENCES `MAINCATEGORY` ( `ID` ); ALTER TABLE `ORDER` ADD CONSTRAINT `FK_USERS_TO_ORDER_1` FOREIGN KEY ( `USER_ID` ) REFERENCES `USERS` ( `ID` ); ALTER TABLE `PAYMENT` ADD CONSTRAINT `FK_ORDER_TO_PAYMENT_1` FOREIGN KEY ( `ORDER_ID` ) REFERENCES `ORDER` ( `ID` ); ALTER TABLE `ORDERDETAIL` ADD CONSTRAINT `FK_ORDER_TO_ORDERDETAIL_1` FOREIGN KEY ( `ORDER_ID` ) REFERENCES `ORDER` ( `ID` ); ALTER TABLE `ORDERDETAIL` ADD CONSTRAINT `FK_INVENTORY_TO_ORDERDETAIL_1` FOREIGN KEY ( `INVENTORY_ID` ) REFERENCES `INVENTORY` ( `ID` ); ALTER TABLE `PRODUCT` ADD CONSTRAINT `FK_POST_TO_PRODUCT_1` FOREIGN KEY ( `POST_ID` ) REFERENCES `POST` ( `ID` ); ALTER TABLE `OPTION` ADD CONSTRAINT `FK_OPTIONTYPE_TO_OPTION_1` FOREIGN KEY ( `OPTIONTYPE_ID` ) REFERENCES `OPTIONTYPE` ( `ID` ); ALTER TABLE `OPTION` ADD CONSTRAINT `FK_PRODUCT_TO_OPTION_1` FOREIGN KEY ( `PRODUCT_ID` ) REFERENCES `PRODUCT` ( `ID` ); ALTER TABLE `CART` ADD CONSTRAINT `FK_USERS_TO_CART_1` FOREIGN KEY ( `USER_ID` ) REFERENCES `USERS` ( `ID` ); ALTER TABLE `CART` ADD CONSTRAINT `FK_INVENTORY_TO_CART_1` FOREIGN KEY ( `INVENTORY_ID` ) REFERENCES `INVENTORY` ( `ID` ); ALTER TABLE `INVENTORY` ADD CONSTRAINT `FK_OPTION_TO_INVENTORY_1` FOREIGN KEY ( `OPTION1` ) REFERENCES `OPTION` ( `ID` ); ALTER TABLE `INVENTORY` ADD CONSTRAINT `FK_OPTION_TO_INVENTORY_2` FOREIGN KEY ( `OPTION2` ) REFERENCES `OPTION` ( `OPTIONTYPE_ID` ); ALTER TABLE `INVENTORY` ADD CONSTRAINT `FK_PRODUCT_TO_INVENTORY_1` FOREIGN KEY ( `PRODUCTID` ) REFERENCES `PRODUCT` ( `ID` ); ALTER TABLE `PRODUCTOPTION` ADD CONSTRAINT `FK_PRODUCT_TO_PRODUCTOPTION_1` FOREIGN KEY ( `PRODUCTID` ) REFERENCES `PRODUCT` ( `ID` ); ALTER TABLE `PRODUCTOPTION` ADD CONSTRAINT `FK_OPTION_TO_PRODUCTOPTION_1` FOREIGN KEY ( `OPTIONID` ) REFERENCES `OPTION` ( `ID` );