구디비

Group

CREATE TABLE group_tb (
	group_id	int	NOT NULL PRIMARY KEY,
	group_invite_code	char(7)	NOT NULL,
	group_name	varchar(20)	NOT NULL,
	group_report varchar(50)
);

User

• fk group id

CREATE TABLE `user_tb` (
	`user_id`	int	NOT NULL PRIMARY KEY,
	`group_id`	int	NOT NULL,
	`user_name`	varchar(20)	NOT NULL,
	`user_nickname`	varchar(20)	NOT NULL,
	`user_password`	varchar(60)	NOT NULL,
	`user_email`	varchar(50)	NOT NULL,
	`user_image_path`	varchar(30)	 NOT NULL,
	foreign key(group_id) references group_tb (group_id);
);

식재료

• fk group id

CREATE TABLE `ingredient_tb` (
	`ingredient_id`	int	NOT NULL PRIMARY KEY,
	`group_id`	int	NOT NULL,
	`ingredient_name`	varchar(30)	NOT NULL,
	`ingredient_count`	varchar(10) NOT NULL,
	`ingredient_save_type`	char(10)	NOT NULL,
	`ingredient_category`	char(15)	NOT NULL,
	`ingredient_purchase_date`	date	NOT NULL,
	`ingredient_expiration_date` date NOT NULL,
	`created_date`	date	NOT NULL,
	`modified_date`	date	NOT NULL,
	`ingredient_memo`	varchar(100)	NOT NULL,
	`ingredient_image_path`	varchar(30)	NOT NULL,
foreign key(group_id) references group_tb (group_id)
);

• ingredient_save_type
  • 냉장 FRIDGE
  • 냉동 FREEZER
  • 실온 ROOM_TEMP
• ingredient_category
  • 채소 VEGGIE
  • 과일 FRUIT
  • 수산물 SEA_FOOD
  • 곡물 GRAIN
  • 육류 MEAT
  • 양념 SEASONING
  • 음료 BEVERAGE
  • 가공식품 PROCESSED_FOOD
  • 간식 SNACK
  • 유제품 DAIRY_PRODUCT
  • 반찬 SIDE_DISH
  • 기타 ETC

집안일

• fk user_id, group_id

CREATE TABLE `chore_tb` (
	`chore_id`	int	NOT NULL PRIMARY KEY,
	`chore_user_id`	int	NOT NULL,
	`group_id`	int	NOT NULL,
	`chore_title`	varchar(20)	NOT NULL,
	`chore_category` char(20) not null,
	`chore_date`	date	NOT NULL,
	`created_date`	date	NOT NULL,
	`modified_date`	date	NOT NULL,
	`chore_check`	char(7) NOT NULL,
foreign key(group_id) references group_tb (group_id),
foreign key(chore_user_id) references user_tb(user_id)
);

• chore_catagory
  • 설거지 DISH_WASHING
  • 장보기 SHOPPING
  • 요리 COOK
• chore_check
  • 인증 요청 REQUEST
  • 수락 SUCCESS
  • 거절 FAIL