당근마켓, 배달의민족, 인스타그램, 야놀자 중 프로젝트를 정해서 DB 설계(고객 관점만 고려하기)
1. 당근마켓 DB 구상
Town 테이블을 제외한 나머지 테이블에 created(생성시간), updated(수정시간), status(상태) 필드 추가
2. QuickDBD를 이용하여 당근마켓 DB 설계
QuickDBD 접속 후 회원가입
파일 생성 후 ERD 작성
User
--
userId PK int IDENTITY
name varchar(20)
nickname varchar(30)
email varchar(50)
phone vatchar(20)
password text
imgUrl text null
temperature decimal default=36.5
created timestamp default=CURRENT_TIMESTAMP
updated timestamp default=CURRENT_TIMESTAMP
status varchar(10) default='ACTIVE'
Post
---
postId PK int IDENTITY
userId FK >- User.userId int
townId FK int
title varchar(100)
categoryId FK >- Category.categoryId int
cost int null
content text
created timestamp default=CURRENT_TIMESTAMP
updated timestamp default=CURRENT_TIMESTAMP
status varchar(10) default='ACTIVE'
PostImg
---
postImgId PK int IDENTITY
postId FK >- Post.postId int
imageUrl text
created timestamp default=CURRENT_TIMESTAMP
updated timestamp default=CURRENT_TIMESTAMP
status varchar(10) default='ACTIVE'
Category
---
categoryId PK int IDENTITY
name varchar(20)
imgUrl text
created timestamp default=CURRENT_TIMESTAMP
updated timestamp default=CURRENT_TIMESTAMP
status varchar(10) default='ACTIVE'
Room
---
roomId PK int
sellerId FK >- User.userId int
buyerId FK >- User.userId int
postId FK >- Post.postId int
created timestamp default=CURRENT_TIMESTAMP
updated timestamp default=CURRENT_TIMESTAMP
status varchar(10) default='ACTIVE'
Chat
---
chatId PK int IDENTITY
roomId FK >- Room.roomId int
message text
created timestamp default=CURRENT_TIMESTAMP
updated timestamp default=CURRENT_TIMESTAMP
status varchat(10) default='ACTIVE'
Town
---
townId PK int
name varchar(20)
city varchar(20)
district varchar(20)
Address
---
addressId PK int
userId FK >- User.userId int
townId FK >- Town.townId int
created timestamp default=CURRENT_TIMESTAMP
updated timestamp default=CURRENT_TIMESTAMP
status varchar(10) default='ACTIVE'
Area
---
areaId PK int
userId FK >- User.userId int
addressId FK >- Address.addressId int
area int
created timestamp default=CURRENT_TIMESTAMP
updated timestamp default=CURRENT_TIMESTAMP
status varchar(10) default='ACTIVE'
Wishlist
---
wishlistId PK int
userId FK >- User.userId int
postId FK >- Post.postId int
created timestamp default=CURRENT_TIMESTAMP
updated timestamp default=CURRENT_TIMESTAMP
status varchar(10) default='ACTIVE'
EXPORT - PNG Image
EXPORT - MySQL
CREATE TABLE `User` (
`userId` int AUTO_INCREMENT NOT NULL ,
`name` varchar(20) NOT NULL ,
`nickname` varchar(30) NOT NULL ,
`email` varchar(50) NOT NULL ,
`phone` vatchar(20) NOT NULL ,
`password` text NOT NULL ,
`imgUrl` text NULL ,
`temperature` decimal NOT NULL DEFAULT 36.5,
`created` timestamp NOT NULL DEFAULT current_timestamp,
`updated` timestamp NOT NULL DEFAULT current_timestamp,
`status` varchar(10) NOT NULL DEFAULT 'active',
PRIMARY KEY (
`userId`
)
);
CREATE TABLE `Post` (
`postId` int AUTO_INCREMENT NOT NULL ,
`userId` int NOT NULL ,
`townId` int NOT NULL ,
`title` varchar(100) NOT NULL ,
`categoryId` int NOT NULL ,
`cost` int NULL ,
`content` text NOT NULL ,
`created` timestamp NOT NULL DEFAULT current_timestamp,
`updated` timestamp NOT NULL DEFAULT current_timestamp,
`status` varchar(10) NOT NULL DEFAULT 'active',
PRIMARY KEY (
`postId`
)
);
CREATE TABLE `PostImg` (
`postImgId` int AUTO_INCREMENT NOT NULL ,
`postId` int NOT NULL ,
`imageUrl` text NOT NULL ,
`created` timestamp NOT NULL DEFAULT current_timestamp,
`updated` timestamp NOT NULL DEFAULT current_timestamp,
`status` varchar(10) NOT NULL DEFAULT 'active',
PRIMARY KEY (
`postImgId`
)
);
CREATE TABLE `Category` (
`categoryId` int AUTO_INCREMENT NOT NULL ,
`name` varchar(20) NOT NULL ,
`imgUrl` text NOT NULL ,
`created` timestamp NOT NULL DEFAULT current_timestamp,
`updated` timestamp NOT NULL DEFAULT current_timestamp,
`status` varchar(10) NOT NULL DEFAULT 'active',
PRIMARY KEY (
`categoryId`
)
);
CREATE TABLE `Room` (
`roomId` int NOT NULL ,
`sellerId` int NOT NULL ,
`buyerId` int NOT NULL ,
`postId` int NOT NULL ,
`created` timestamp NOT NULL DEFAULT current_timestamp,
`updated` timestamp NOT NULL DEFAULT current_timestamp,
`status` varchar(10) NOT NULL DEFAULT 'active',
PRIMARY KEY (
`roomId`
)
);
CREATE TABLE `Chat` (
`chatId` int AUTO_INCREMENT NOT NULL ,
`roomId` int NOT NULL ,
`message` text NOT NULL ,
`created` timestamp NOT NULL DEFAULT current_timestamp,
`updated` timestamp NOT NULL DEFAULT current_timestamp,
`status` varchat(10) NOT NULL DEFAULT 'active',
PRIMARY KEY (
`chatId`
)
);
CREATE TABLE `Town` (
`townId` int NOT NULL ,
`name` varchar(20) NOT NULL ,
`city` varchar(20) NOT NULL ,
`district` varchar(20) NOT NULL ,
PRIMARY KEY (
`townId`
)
);
CREATE TABLE `Address` (
`addressId` int NOT NULL ,
`userId` int NOT NULL ,
`townId` int NOT NULL ,
`created` timestamp NOT NULL DEFAULT current_timestamp,
`updated` timestamp NOT NULL DEFAULT current_timestamp,
`status` varchar(10) NOT NULL DEFAULT 'active',
PRIMARY KEY (
`addressId`
)
);
CREATE TABLE `Area` (
`areaId` int NOT NULL ,
`userId` int NOT NULL ,
`addressId` int NOT NULL ,
`area` int NOT NULL ,
`created` timestamp NOT NULL DEFAULT current_timestamp,
`updated` timestamp NOT NULL DEFAULT current_timestamp,
`status` varchar(10) NOT NULL DEFAULT 'active',
PRIMARY KEY (
`areaId`
)
);
CREATE TABLE `Wishlist` (
`wishlistId` int NOT NULL ,
`userId` int NOT NULL ,
`postId` int NOT NULL ,
`created` timestamp NOT NULL DEFAULT current_timestamp,
`updated` timestamp NOT NULL DEFAULT current_timestamp,
`status` varchar(10) NOT NULL DEFAULT 'active',
PRIMARY KEY (
`WishlistId`
)
);
ALTER TABLE `Post` ADD CONSTRAINT `fk_Post_userId` FOREIGN KEY(`userId`)
REFERENCES `User` (`userId`);
ALTER TABLE `Post` ADD CONSTRAINT `fk_Post_categoryId` FOREIGN KEY(`categoryId`)
REFERENCES `Category` (`categoryId`);
ALTER TABLE `PostImg` ADD CONSTRAINT `fk_PostImg_postId` FOREIGN KEY(`postId`)
REFERENCES `Post` (`postId`);
ALTER TABLE `Room` ADD CONSTRAINT `fk_Room_sellerId` FOREIGN KEY(`sellerId`)
REFERENCES `User` (`userId`);
ALTER TABLE `Room` ADD CONSTRAINT `fk_Room_buyerId` FOREIGN KEY(`buyerId`)
REFERENCES `User` (`userId`);
ALTER TABLE `Room` ADD CONSTRAINT `fk_Room_postId` FOREIGN KEY(`postId`)
REFERENCES `Post` (`postId`);
ALTER TABLE `Chat` ADD CONSTRAINT `fk_Chat_roomId` FOREIGN KEY(`roomId`)
REFERENCES `Room` (`roomId`);
ALTER TABLE `Address` ADD CONSTRAINT `fk_Address_userId` FOREIGN KEY(`userId`)
REFERENCES `User` (`userId`);
ALTER TABLE `Address` ADD CONSTRAINT `fk_Address_townId` FOREIGN KEY(`townId`)
REFERENCES `Town` (`townId`);
ALTER TABLE `Area` ADD CONSTRAINT `fk_Area_userId` FOREIGN KEY(`userId`)
REFERENCES `User` (`userId`);
ALTER TABLE `Area` ADD CONSTRAINT `fk_Area_addressId` FOREIGN KEY(`addressId`)
REFERENCES `Address` (`addressId`);
ALTER TABLE `Wishlist` ADD CONSTRAINT `fk_Wishlist_userId` FOREIGN KEY(`userId`)
REFERENCES `User` (`userId`);
ALTER TABLE `Wishlist` ADD CONSTRAINT `fk_Wishlist_postId` FOREIGN KEY(`postId`)
REFERENCES `Post` (`postId`);