관계형 데이터 베이스 설계

위와 같이 인스타그램의 관계도를 간략하게 만들어 봤다. (데이터베이스 설계도 툴: https://dbdiagram.io/home)

 

1:N 관계

users테이블의 id를 통해 followers, followings, posts, likes, comments 테이블들과 1:N 관계를 가지고 있다. 위의 관계도처럼 users에 관계를 설정하는 것이 아닌 다른 테이블들에 users의 id를 foriegn key(외래키)로 지정해서 관계를 맺어준 이유는 한명의 유저가 다른 테이블들의 많은 데이터를 가질수 있기 때문이다.

 

posts테이블을 예를 들면 만약 users에 posts_id라는 column을 만들어 관계를 형성하게되면 한명의 유저가 많은 post들을 가지고 있게 되었을때 posts_id 컬럼은 수평적으로 데이터가 늘어나게 되고 처음 해당 컬럼을 생성할때 데이터 타입과 크기를 정해주어야 되는데 어느정도의 크기를 정해줘야될지 예측하기 힘들게 된다.

또한 해당 유저의 특정한 포스트를 탐색할때 해당유저를 먼저 탐색한뒤 특정한 포스트를 또 한번 탐색해주는것 보다 데이터베이스를 수직적이고 독립적으로 설계한뒤 인덱싱을 이용하는것이 더 효율적이다. 이와 같은 특징은 N:N관계에서도 동일하다

 

N:N 관계

1:N관계에서는 N쪽의 테이블에 foriegn key를 설정해주었다. 하지만 N:N이라면 어느 한쪽에 설정을 해주어도 위와같은 문제가 발생하게된다. 이경우 위의 설계도에서 likes테이블처럼 하나의 테이블을 새로 만들어주어 N:N관계를 정리해줄수 있다. 만약 한 유저가 어떠한 포스트에 좋아요를 누르면 likes테이블에 해당 유저와 포스트의 id와 함께 likes테이블에 레코드가 생성되며 유저와 포스트간에 관계가 형성이 된다.

 

1:1관계와 자기참조 관계(Self Referencing Relationship)

위의 관계도에서는 안나온 관계들이다.

 

1:1 관계의 경우 메인이되는 테이블에 해당 레코드만 가질수있는 다른 테이블의 primary key(기본키)를 foreign key로 설정하는것이다. 하지만 1:1 관계라면 그냥 관계를 설정하지않고 하나의 테이블로 만들면 되므로 특별한 상황이 아니면 잘 안쓰일 것 같다...

 

자기참조 관계의 예시로는 어떠한 유저가 다른 특정한 유저의 정보를 포함하는경우, 즉 어떠한 레코드가 자기자신의 레코드를 참조하는 것이 아닌 동일한 테이블의 다른 레코드를 참조하는 경우 사용된다.

(맨위의 관계도의 경우 followers와 followings를 자기참조 형태로 만들수 있지만 followers와 followings의 수가 불규칙적이고 많아질것을 예상하여 1:N관계로 만들었다.)

 

 

SQL문

CREATE TABLE `followings` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `user_from_id` int,
  `user_to_id` int
);

CREATE TABLE `followers` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `user_from_id` int,
  `user_to_id` int
);

CREATE TABLE `users` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(255),
  `createdAt` DATETIME default now(),
  `updatedAt` DATETIME default current_timestamp on update current_timestamp
);

CREATE TABLE `posts` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `image` varchar(255),
  `text` text,
  `user_id` int,
  `createdAt` DATETIME default now(),
  `updatedAt` DATETIME default current_timestamp on update current_timestamp
);

CREATE TABLE `comments` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `post_id` int,
  `user_id` int,
  `text` text,
  `createdAt` DATETIME default now(),
  `updatedAt` DATETIME default current_timestamp on update current_timestamp
);

CREATE TABLE `likes` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `post_id` int,
  `user_id` int
);

CREATE TABLE `hashtag` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `post_id` int,
  `hashtag_name` nvarchar(30)
);

ALTER TABLE `followings` ADD FOREIGN KEY (`user_from_id`) REFERENCES `users` (`id`) on delete cascade;

ALTER TABLE `followings` ADD FOREIGN KEY (`user_to_id`) REFERENCES `users` (`id`)on delete cascade;

ALTER TABLE `followers` ADD FOREIGN KEY (`user_from_id`) REFERENCES `users` (`id`)on delete cascade;

ALTER TABLE `followers` ADD FOREIGN KEY (`user_to_id`) REFERENCES `users` (`id`)on delete cascade;

ALTER TABLE `posts` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)on delete cascade;

ALTER TABLE `comments` ADD FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)on delete cascade;

ALTER TABLE `comments` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)on delete cascade;

ALTER TABLE `likes` ADD FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)on delete cascade;

ALTER TABLE `likes` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)on delete cascade;

ALTER TABLE `hashtag` ADD FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) on delete cascade;

 위의 관계도를 쿼리문으로 작성한 것이다. 쿼리문을 작성하면서 몇가지 특이한 점을 발견하여 조금 끄적여 보겟다.

 

첫번째로 users, posts, comments 테이블의 updatedAt 컬럼이다. now()와 CURRENT_TIMESTAMP 둘다 동일하게 현재시간을 뜻하며 default값으로 주었고 ON UPDATE 옵션을 주어 해당 레코드의 정보를 변경할시 시간이 재설정되도록 설정해 주었다. (Timestamp 관련 블로그 : https://thisisprogrammingworld.tistory.com/133)

 

두번째로 모든 REFERENCE들의 옵션으로 ON DELETE CASCADE를 설정해 주었다. 해당 옵션은 만약 어떤한 데이터가 삭제된다면 연결된 모든 데이터도 자동으로 삭제된다. 예를들어 어떤 유저의 계정을 삭제하면 그 계정에 연결된 모든 포스트 댓글등이 함께 삭제된다.

 

세번째로 관계를 형성할때 ALTER ADD를 사용한다는 것이다.

나는 당연히 관계로 설정할 키에 ALTER MODIFY를 사용하여 foriegn key 속성을 부여하여 관계를 설정할줄 알았다. 

 

hashtag테이블의 post_id로 예를 들자면 

ALTER TABLE hashtag MODIFY post_id int foreign key REFERENCES "posts"("id)
// 잘못된 쿼리문입니다. 따라하지 마세요!

위와 같이 컬럼에 옵션을 추가하여 관계를 설정해주는 줄 알았는데 아니었다. ALTER ADD를 사용하여 해당 테이블에 새로운 컬럼을 생성하듯이 관계를 설정해주는 것이다.