[프로젝트][Back] kakao login 구현하기(7)_MySQL DB 생성하기 #07

이제 본격적으로 DB를 생성해보자!

DB 생성

User Table 생성

CREATE TABLE `User` (
`userId` varchar(100) NOT NULL,
`name` varchar(10) NOT NULL,
`nickname` varchar(15) NOT NULL,
`email` varchar(50) NOT NULL,
`phonenumber` varchar(15) NOT NULL,
`profile` varchar(100) NULL,
`address` varchar(50) NOT NULL,
primary key(`userId`)
);

나의 경우는 귀찮아서...그냥 기존에 사용중이었던 MySQL DB를 사용했다.
우선 User 부터 생성하고 확인해보자!

잘 생성된 것을 볼 수 있다!

• 참고로 설명을 덧붙이자면, userId는 카카오 로그인 이후 파이어베이스에서 날라오는 customToken을 활용할 생각이다.

Category Table 생성

CREATE TABLE `Category` (
`categoryId` integer NOT NULL,
`categoryName` varchar(45) NOT NULL,
`categoryLevel` integer NOT NULL,
`parentCategoryId` integer NOT NULL,
primary key(`categoryId`),
CONSTRAINT `Category_FK` FOREIGN KEY (`parentCategoryId`) REFERENCES `Category` (`categoryId`) ON DELETE CASCADE ON UPDATE CASCADE
);

Order table 생성

CREATE TABLE `Order` (
`orderId` integer NOT NULL,
`userId` varchar(100) NOT NULL,
`orderItemList` varchar(100) NOT NULL,
`totalAmount` integer NOT NULL,
`orderDate` date NOT NULL,
primary key(`orderId`),
CONSTRAINT `Order_FK` FOREIGN KEY (`userId`) REFERENCES `User` (`userId`) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE `OrderItem` (
`orderItemId` integer NOT NULL,
`orderId` integer NOT NULL,
`productId` integer NOT NULL,
primary key(`orderItemId`),
CONSTRAINT `OrderItem_Order_FK` FOREIGN KEY (`orderId`) REFERENCES `Order` (`orderId`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `OrderItem_Product_FK` FOREIGN KEY (`productId`) REFERENCES `Product` (`productId`) ON DELETE CASCADE ON UPDATE CASCADE
);

Product 테이블 생성

CREATE TABLE `Product` (
`productId` integer NOT NULL,
`productPhoto` varchar(100) NOT NULL,
`price` integer NOT NULL,
`size` varchar(45) NOT NULL,
`brandName` varchar(45) NOT NULL,
`count` integer NOT NULL,
`onSale` boolean NOT NULL,
`salePercent` double NOT NULL,
`explanation` varchar(100) NOT NULL,
`creatAt` date NOT NULL,
`categoryId` integer NOT NULL,
primary key(`productId`),
CONSTRAINT `Product_FK` FOREIGN KEY (`categoryId`) REFERENCES `Category` (`categoryId`) ON DELETE CASCADE ON UPDATE CASCADE
);

장바구니 테이블 생성

CREATE TABLE `Cart` (
`cartId` integer NOT NULL,
`countOfProduct` integer NOT NULL,
`date` date NOT NULL,
`userId` varchar(100) NOT NULL,
primary key(`cartId`),
CONSTRAINT `Cart_FK` FOREIGN KEY (`userId`) REFERENCES `User` (`userId`) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE `CartProduct` (
`cartProductId` integer NOT NULL,
`quantity` integer NOT NULL,
`cartId` integer NOT NULL,
`productId` integer NOT NULL,
primary key(`cartProductId`),
CONSTRAINT `CartProduct_Cart_FK` FOREIGN KEY (`cartId`) REFERENCES `Cart` (`cartId`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `CartProduct_Product_FK` FOREIGN KEY (`productId`) REFERENCES `Product` (`productId`) ON DELETE CASCADE ON UPDATE CASCADE
);