DB설계) 밥집 찾는 테이블 설계

1. 데이터베이스 설계하기

db 이름: yummy_db
1) 맛집 테이블

열 이름	place	데이터타입	최대 길이	null 허용 여부
순번 (PK)	num	int, auto_increment		x
식당 이름 (Unique)	place_name	char	20	x
음식 종류	place_kind	char	10 (한식, 중식, 양식, 베트남음)	x
거리 (도보)	place_howfar	tinyint, unsigned		o

가까운 거리에 위치한 맛집만 정리할 것이므로 tinyint를 사용한다.
2) 음식 테이블

열 이름	food	데이터타입	최대 길이	null 허용 여부
순번 (PK)	num	int, auto_increment		x
음식 종류	food_kind	char	10	x
음식 이름	food_name	char	20	x
식당 이름 (FK)	place_name	char	20	x
음식 가격	food_price	int		x
음식 만족도	food_rating	tinyint	1점~5점	o, 0점 이상이고 5점이 넘지 않는지 체크
비고	food_memo	char	30	o

 
최종적으로 출력할 테이블은 맛집의 이름을 입력하면 그곳의 음식에 대한 정보가 나오는 테이블과
음식의 종류를 넣으면 음식의 정보와 그 식당의 정보가 나오는 테이블이다.

맛집 테이블
식당 이름	place_name
음식 종류	food_kind
거리 (도보)	place_howfar
음식 이름	food_name
가격	food_price
만족도 (5점)	food_rating
비고	food_memo

 

2. 테이블 만들 순서 정하기

food 테이블은 place 테이블의 place_name을 FK로 받고 있으므로 place 테이블을 먼저 만든다.
 

3. place 테이블 만들고 데이터 넣기

create table place (
	num int not null primary key auto_increment,
    place_name char(20) not null unique,
    place_kind char(10) not null,
    place_howfar tinyint unsigned
);

show columns from place;

insert into place(place_name, place_kind, place_howfar) values ('올바른스시', '일식', 5);
insert into place(place_name, place_kind, place_howfar) values ('이현우스시', '일식', 6);
insert into place(place_name, place_kind, place_howfar) values ('미분당', '베트남음식', 5);
insert into place(place_name, place_kind, place_howfar) values ('롤링파스타', '양식', 4);
insert into place(place_name, place_kind, place_howfar) values ('육쌈냉면', '한식', 3);
insert into place(place_name, place_kind, place_howfar) values ('홍콩반점0410', '중식', 8);
insert into place(place_name, place_kind, place_howfar) values ('틸란', '베트남음식', 6);
insert into place(place_name, place_kind, place_howfar) values ('헬로비에트', '베트남음식', 7);
insert into place(place_name, place_kind, place_howfar) values ('김희라 한국식왕돈까스', '한식', 8);
insert into place(place_name, place_kind, place_howfar) values ('그래도국수', '한식', 4);

select * from place;

 

4. food 테이블 만들고 데이터 넣기

create table food (
	num int not null primary key auto_increment,
    food_kind char(10) not null,
    food_name char(20) not null,
    place_name char(20) not null,
    food_price int not null,
    food_rating tinyint unsigned check (food_rating <=5),
    food_memo char(30),
    foreign key (place_name) references place(place_name)
);

show columns from food;

insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('일식', '올바른초밥10ps', '올바른스시', 14000, 5, '');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('일식', '점심특선 우동정식', '올바른스시', 9500, 5, '가성비 굿');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('일식', '점심특선 모밀정식', '올바른스시', 9500, 5, '가성비 굿, 여름메뉴');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('일식', '알밥', '올바른스시', 7000, 5, '');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('일식', '평일 런치초밥', '이현우스시', 12000, 5, '긴꼬리초밥');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('양식', '스테이크 필라프', '롤링파스타', 8500, 2, '맛있는 고기가 아님..');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('양식', '토마토 파스타', '롤링파스타', 5000, 4, '');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('양식', '바질페스토 크림 파스타', '롤링파스타', 8500, 2, '맛없진 않은데 굳이 또 먹진 않을듯');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('중식', '짜장면', '홍콩반점0410', 6500, 3, '그냥짜장면');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('배트남음식', '양지 쌀국수', '틸란', 9000, 5, '그릇이 매우 큼');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('한식', '왕돈까스', '김희라 한국식왕돈까스', 10000, 4, '양이 너무 많음');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('한식', '잔치국수', '그래도국수', 5000, 4, '');
    
select * from food;

 

5. view 만들고 데이터 조회하기

create view find_place
as
select place.place_name, food.food_name, place.place_howfar, food.food_name, food.food_price, food.food_rating, food.food_memo
	from food
    join place
		on place.place_name = food.place_name
	order by food.food_rating desc;
    
 select * from find_place;

 
일식 중에서 찾기

select * from find_place
	where food_kind='일식';

 
올바른 초밥 메뉴 보기

select * from find_place
	where place_name='올바른스시';

 

6. 인덱스 만들기

인덱스는 검색에 자주 쓰이는 열을 선택하는 것이 좋으므로 place_name과 food_kind를 만들었다.

create index idx_place_place_name
	on place(place_name);
create index idx_food_food_kind
	on food(food_kind);

 

PK와 Unique한 데이터는 이미 index가 만들어져 있는 것을 볼 수 있다.

그런데 생각해보니까 food_kind는 한식, 일식 등 중복도가 높으므로 인덱스가 있는 것이 무의미할 것 같아서 삭제하였다.

alter table food drop index idx_food_food_kind;

 
 
전체코드

drop database if exists yummy_db;
create database yummy_db;
use yummy_db;


-- place 테이블
create table place (
	num int not null primary key auto_increment,
    place_name char(20) not null unique,
    place_kind char(10) not null,
    place_howfar tinyint unsigned
);

show columns from place;

insert into place(place_name, place_kind, place_howfar) values ('올바른스시', '일식', 5);
insert into place(place_name, place_kind, place_howfar) values ('이현우스시', '일식', 6);
insert into place(place_name, place_kind, place_howfar) values ('미분당', '베트남음식', 5);
insert into place(place_name, place_kind, place_howfar) values ('롤링파스타', '양식', 4);
insert into place(place_name, place_kind, place_howfar) values ('육쌈냉면', '한식', 3);
insert into place(place_name, place_kind, place_howfar) values ('홍콩반점0410', '중식', 8);
insert into place(place_name, place_kind, place_howfar) values ('틸란', '베트남음식', 6);
insert into place(place_name, place_kind, place_howfar) values ('헬로비에트', '베트남음식', 7);
insert into place(place_name, place_kind, place_howfar) values ('김희라 한국식왕돈까스', '한식', 8);
insert into place(place_name, place_kind, place_howfar) values ('그래도국수', '한식', 4);

select * from place;



-- food 테이블
create table food (
	num int not null primary key auto_increment,
    food_kind char(10) not null,
    food_name char(20) not null,
    place_name char(20) not null,
    food_price int not null,
    food_rating tinyint unsigned check (food_rating <=5),
    food_memo char(30),
    foreign key (place_name) references place(place_name)
);

show columns from food;

insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('일식', '올바른초밥10ps', '올바른스시', 14000, 5, '');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('일식', '점심특선 우동정식', '올바른스시', 9500, 5, '가성비 굿');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('일식', '점심특선 모밀정식', '올바른스시', 9500, 5, '가성비 굿, 여름메뉴');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('일식', '알밥', '올바른스시', 7000, 5, '');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('일식', '평일 런치초밥', '이현우스시', 12000, 5, '긴꼬리초밥');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('양식', '스테이크 필라프', '롤링파스타', 8500, 2, '맛있는 고기가 아님..');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('양식', '토마토 파스타', '롤링파스타', 5000, 4, '');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('양식', '바질페스토 크림 파스타', '롤링파스타', 8500, 2, '맛없진 않은데 굳이 또 먹진 않을듯');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('중식', '짜장면', '홍콩반점0410', 6500, 3, '그냥짜장면');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('배트남음식', '양지 쌀국수', '틸란', 9000, 5, '그릇이 매우 큼');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('한식', '왕돈까스', '김희라 한국식왕돈까스', 10000, 4, '양이 너무 많음');
insert into food(food_kind, food_name, place_name, food_price, food_rating, food_memo) values
	('한식', '잔치국수', '그래도국수', 5000, 4, '');
    
select * from food;


-- view 만들기
create view find_place
as
select place.place_name, food.food_kind, place.place_howfar, food.food_name, food.food_price, food.food_rating, food.food_memo
	from food
    join place
		on place.place_name = food.place_name
	order by food.food_rating desc;
    
    
-- 데이터 조회하기
select * from find_place;
select * from find_place
	where food_kind='일식';
select * from find_place
	where food_kind='한식';
select * from find_place
	where place_name='올바른스시';