[DataBase] 스키마(Schema) 설계

스키마(Schema)

• 데이터베이스에서 자료의 구조, 자료의 표현 방법, 자료 간의 관계를 형식 언어로 정의한 구조이다
• 데이터베이스의 구조를 설계할때 스키마 디자인 한다고 표현 하기도 한다.
• https://dbdiagram.io - 스키마 다이어그램 그리기 좋은 사이트

관계형 데이터베이스

• 구조화된 데이터는 하나의 테이블로 표현, 이것을 Relational(관계가 있는) 테이블로도 부르며 관계형 데이터베이스는  관계가 있는 테이블들의 집합이다.

 

용어정리

• 테이블(table)
  • 구조화된 데이터로 행과 열을 가진다. 
• 행(row)=튜플=레코드 
  • 데이터
• 열(coulmn)=필드=속성
  • 데이터의 속성
• key (식별자 - Identifier)
  • 테이블내 레코드를 구분하거나 찾기 위한 수단
  • key를통해 데이터를 식별 할 수 있다.
• PK (primary key)
  • 테이블내 각 레코드를 고유하게 식별하는데 사용하는 key
  • not null (null값을 가질수 없음)과 unique (데이터가 중복될수 없다)의 속성을 기본적으로 가진다.
  • 모든 테이블에는 pk가 존재하는것이 좋다. 
• 대체 키 (Alternate Key) 
  • 테이블에서 pk가 아닌 필드중 unique한 필드를 사용하여 레코드를 식별 하는데 사용 한다. 
• 참조 키 (Foreign Key)
  • 테이블 간의 관계를 표현하기 위해 사용하는 key
  • 다른 테이블의 PK나 unique한 Key를 참조키(FK)로 가질수 있다.
• 후보 키 (Candidate Key)
  • pk가 될 수 있는 후보가 되는 key - pk와 같은 속성을 가져야 한다. (not null, unique)
• 복합 키 (composite Key)
  • 둘 이상의 필드를 결합하여 레코드를 구분 할 수 있는 key

 

관계형데이터베이스에서 테이블간의 관계

• 1:1 관계
• 1:N 관계
• N:N 관계

 

1:1 관계 (일대일)

• 하나의 레코드가 다른 테이블의 레코드 하나와 연결된 경우
• user는 하나의 nickname만 가질수 있는 경우
• 일반적으로 사용하지 않는다. 이러한 관계에 경우 users 테이블에 nickname이라는 필드를 생성한다.

 

create table users(
	id int primary key,
	name varchar(20) unique not null,
);

create table nickname(
	user_id int primary key,
	nickname varchar(20),
	CONSTRAINT fk_user_id
	foreign key (user_id)
	references users(id) on update cascade
);

 

1:N 관계 (일대다)

• 하나의 레코드가 다른 테이블의 레코드를 여러개 가질 수 있는 경우
• user는 post를 여러개 가질수 있다.
• post(N)에서 users(1)의 레코드(unique한)를 참조키(FK)로 갖는다 (주로 PK(Primary Key)를 참조키(FOREIGN KEY)로 갖는다.)

N:N 관계 (다대다)

• 여러개의 레코드가 다른 테이블의 레코드를 여러개 가질 수 있는 경우
• post는 여러개의 hash태그를 가질수 있고, hash 태그는 여러개의 post를 가질수 있다.
• N:N의 관계인 경우 조인테이블을 생성하여 관계를 표현한다.
• 조인테이블에서 다대다의 관계를 가지는 테이블들의 레코드(unique한)를 참조키로 갖는다. (주로 PK를 사용)

post_hashtag라는 조인 테이블이 둘을 N:N관계로 설정한다.

자기 참조 관계

• 테이블내의 레코드가 테이블내 레코드를 가지는 경우
• user는 user들을 팔로우 할 수 있다. 팔로우는 여러명 할수 있다.(N)
• 테이블내 레코드를 참조키로 갖는다.

create table users(
	id int primary key,
	name varchar(30) unique not null,
	nickname varchar(30) unique not null,
	phone_number varchar(30) unique not null,
	follow int,
	foreign key (id)
	references users(follow) on update cascade
);

 

 

인스타그램의 스키마를 디자인 해보기 (연습)

• 유저(user)는 여러개의 게시물(post)를 작성 할 수 있다.
• 게시물(post)에는 여러개의 사진이 있을 수있고 해쉬태그(hashtag)를 남길수 있다.
• 해쉬태그를 선택하면 해당 해쉬태그를 포함한 게시물들을 묶어서 볼 수 있다.
• 유저들은 게시물에 댓글(comment)를 남기고 좋아요(like)를 할 수 있다. 
• 유저는 유저들을 follow할 수 있다. 

create table users(
	id int primary key AUTO_INCREMENT,
	name varchar(30) unique not null,
	password varchar(30) not null,
	phone_number varchar(30) unique not null,
);

create table post(
	id int primary key AUTO_INCREMENT,
	data text(5000),
	date datetime,
	users_id int,
	foreign key (users_id)
	references users(id) on update cascade
);

create table photo(
	id int primary key AUTO_INCREMENT,
	image_data BOLB,
	post_id int,
	foreign key (post_id)
	references post(id) on update cascade
);

create table hashtag(
	id int primary key AUTO_INCREMENT,
	data varchar(30)
);

create table post_hashtag(
	id int primary key AUTO_INCREMENT,
	post_id int,
	foreign key (post_id)
	references post(id) on update cascade,
	hashstag_id int,
	foreign key (hashstag_id)
	references hashtag(id) on update cascade
);

create table comment(
	id int primary key AUTO_INCREMENT,
	data text(1000),
	date datetime,
	users_id int,
	foreign key (users_id)
	references users(id) on update cascade,
	post_id int,
	foreign key (post_id)
	references post(id) on update cascade
);

create table like(
	id int primary key AUTO_INCREMENT,
	is_like boolean,
	users_id int,
	foreign key (users_id)
	references users(id) on update cascade,
	post_id int,
	foreign key (post_id)
	references post(id) on update cascade
);

create table follow(
	id int primary key AUTO_INCREMENT,
	user_id int,
	foreign key (user_id)
	references users(id) on update cascade,
	follow_id int unique,
	foreign key (follow_id)
	references users(id) on update cascade,
);