간단한 인스타그램 스키마 생성

mysql 을 사용하여 간단한 인스타그램 스키마를 만들어보자...

mysql workbench 대신 젯브레인 사의 DataGrip 을 사용하여 작업을 했다.
snippets 가 존재하여 매우! 매우! 편하게 작업을 할 수 있었다...
아직 사용하지 안았다면 적극 추천한다....

1. ig_clone DB 를 생성

create database ig_clone;

use ig_clone;

2-1. users 테이블 생성

# ------------------users-------------------

create table users (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO users (username) VALUES
('anton'), ('blues'), ('capstone');

select * from users;

describe users;

id 에는 인티저 타입, 기본키, 자동 증가 조건을 추가해줬고 username 에는 문자열 타입, unique, not null 조건을 추가했다. not null 은 말 그대로 null 인 값이 올 수 없다는 뜻이며 unique 는 유일한 값이라는 뜻이다.
또한, created_at 에는 timestamp 타입, 기본값으로 현재 시간을 갖도록 설정했다.

insert into 를 사용하여 임의의 데이터를 입력해주었다.

select 문으로 조회하면 아래와 같이 나온다...
id 가 101 로 시작하는 것은 내가 1부터 100까지 임의의 데이터를 채워넣었기 때문이다.

describe users; 를 사용하면 해당 테이블의 구조를 확인할 수 있다.
실제로 create table users 에서 설정한 제약 조건들이 제대로 들어간 것을 확인할 수 있다.

2-2. photos 테이블 생성

# ------------------photos-------------------

create table photos (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  image_url VARCHAR(255) NOT NULL,
  user_id INTEGER NOT NULL ,
  created_at TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY(user_id) REFERENCES users(id)
);
INSERT INTO photos (image_url, user_id) VALUES
('/asdasd77', 1),
('/asdasd77', 2),
('/asdasd77', 3);

select * from photos;

describe photos;

user 와 같이 각 각의 컬럼에 조건을 추가해줬고, 다른 점은 user_id 를 외래키로 갖는다는 것이다.
user_id 를 외래키로 설정해주기 위해 create table photos 맨 아래에서 FOREIGN KEY(user_id) REFERENCES users(id) 를 추가해줬다. 

풀어서 말하자면 photos table 의 user_id 는 users table 의 id 를 참조하는 값이라는 뜻이다.

insert into 를 사용하여 임의의 데이터를 입력해주었다.

select 문으로 조회를 하면 아래와 같이 나온다...
id 가 258 부터 시작하는 것도 위와 같은 이유이다.

describe 도 확인해보자..

2-3. comments 테이블 생성

# ------------------comments-------------------

# 유저, 사진의 외래키를 갖는다.
create table comments(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    comment_text VARCHAR(255) NOT NULL,
    user_id INTEGER NOT NULL ,
    photo_id INTEGER NOT NULL ,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(user_id) REFERENCES users(id),
    FOREIGN KEY(photo_id) REFERENCES photos(id)
);

INSERT INTO comments (comment_text, user_id, photo_id) VALUES
('예쁘다', 1, 5);

select * from comments;
describe comments;

photos 테이블처럼 외래키를 가지는 것은 같으나, comments 테이블은 유저와 사진 두 개의 외래키를 가진다.
외래키로 설정해주기 위해서 FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(photo_id) REFERENCES photos(id) 로 각각의 테이블과 연결해주었다.

insert into 로 임의의 값을 입력했고 잘 입력되는 것을 확인할 수 있다.

2-4. likes 테이블 생성

# ------------------likes-------------------

create table likes(
    user_id INTEGER NOT NULL,
    photo_id INTEGER NOT NULL,
    created_at timestamp default now(),
    foreign key (user_id) references users(id),
    foreign key (photo_id) references photos(id),
    primary key(user_id, photo_id)
);

# 1번 유저가 1번 게시물에 좋아요를 누름 => 한 번 더 insert 해도 데이터가 추가되지 않는다.
insert into likes(user_id, photo_id) VALUE (1, 1);
# 2번 유저가 1번 게시물에 좋아요를 누름
insert into likes(user_id, photo_id) VALUE (2, 1);
# 1번 유저가 2번 게시물에 좋아요를 누름
insert into likes(user_id, photo_id) VALUE (1, 2);
# 1번 유저가 3번 게시물에 좋아요를 누름
insert into likes(user_id, photo_id) VALUE (1, 3);
# 3번 유저가 3번 게시물에 좋아요를 누름
insert into likes(user_id, photo_id) VALUE (3, 3);

select * from likes;
describe likes;

likes 테이블에선 id 가 존재하지 않는데 likes 테이블을 다른 테이블과 외래키로 연결시키지 않을 것이기 때문에 id 를 만들지 않았다.
다른 테이블들은 id 를 기본키로 받아서 사용했는데 likes 테이블은 기본키가 없냐고 물으면 그건 또 아니다.

기본키가 존재하긴 하지만 단지, user_id, photo_id 외래키 두 개를 기본키로써 사용한다.

이렇게 사용하는 이유는 '좋아요' 를 단 한 번만 입력받기 위해서다.

user_id 와 photo_id 를 외래키 겸 기본키로 사용하여서 유저가 어떤 게시물에 좋아요를 클릭하면 좋아요가 DB 에 쌓이고, 다시 똑같은 user_id 와 photo_id 로 db 에 추가를 하면 기본키의 특성 덕분에 DB 에 값이 추가되지 않는다.

기본키는 중복을 허용하지 않고, null 을 허용하지 않는 특징을 가지고 있기 때문에 똑같은 값이 들어오면 에러가 발생한다.

실제로 insert into 로 똑같은 user_id 와 똑같은 photo_id 로 중복해서 insert 하면 데이터가 DB 에 저장되지 않고 오류가 발생하는 것을 확인할 수 있다.

2-5. follows 테이블 생성

# ------------------follows-------------------

create table follows(
    # follower 는 팔로워 계정
    follower_id INTEGER NOT NULL,
    # followee 는 팔로잉 계정
    followee_id INTEGER NOT NULL,
    created_at timestamp default now(),
    foreign key (follower_id) references users(id),
    foreign key (followee_id) references users(id),
    # follower_id, followee_id 를 기본키로 설정해서 not null, unique true 로 설정 => 중복된 값 입력하지 못하게 했다.
    primary key (follower_id, followee_id)
);

# 사용자 3이 사용자 2를 팔로잉 했을 때 또 다시 사용자 3이 사용자 2를 팔로잉 할 수 없어야 한다 => 둘 다 기본키로 설정
# follower_id     followee_id     created_at
# 3               2               2021-12-01    => 사용자 3이 사용자 2를 팔로잉
# 3               1               2021-12-02    => 사용자 3이 사용자 1을 팔로잉
# 2               3               2021-12-02    => 사용자 2가 사용자 3을 팔로잉
# 핵심은 단방향 관계라는 것이다. => 사용자 3이 2를 팔로잉 한다고 2가 3을 팔로잉 하지 않는다.

insert into follows (follower_id, followee_id) values
(1, 2),
(1, 3),
(3, 1),
(2, 3);

select *from follows;
describe follows;

인스타그램은 팔로워, 팔로잉 기능이 존재한다.
내가 아이유를 팔로잉 한다고 해서 아이유가 나를 다시 팔로잉 하는 일은 거의 없을 것이다.
팔로워 팔로잉 관계의 핵심은 단방향 관계라는 것이다.
내가 누군가를 팔로잉 한다 해서 그 사람이 나를 팔로잉 할지 안 할지는 알 수 없는 노릇이기 때문이다.

위의 예제를 한 번 읽으면 쉽게 이해가 갈 것이다.

또한, 내가 아이유를 팔로잉 했을 때 또 다시 내가 아이유를 팔로잉 할 수는 없어야 한다.
다시 말해서, DB 에 아이유를 팔로잉 한 정보가 또 들어와서는 안된다는 말이다. => 중복된 데이터가 들어오면 안된다!!!

그렇기 때문에 follower_id 와 followee_id 를 기본키로 설정해야 한다. 또한, follower_id 와 followee_id 는 user 의 아이디라고 볼 수 있기 때문에 외래키여야 한다.

실제로 insert into follows (follwer_id, followee_id) value(1, 2); 로 DB 에 데이터를 넣은 이후에 또 다시 insert into follows (follwer_id, followee_id) value(1, 2); 로 똑같은 데이터가 중복되서 들어가면 안되기 때문에 follwer_id, followee_id 를 기본키로 설정했다는 것이다!

tags 테이블 생성

# ------------------tags-------------------

# 태그와 사진은 n:m, 다대다 관계이다. => 중간 테이블을 놓아서 1:N, N:1 로 분리해야 한다.
create table tags (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    tag_name VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
);
insert into tags (tag_name) values
('cute'),
('wow'),
('think');

select * from tags;
describe tags;

인스타에서는 여러 장의 사진에 여러 개의 여러 개의 해시 태그를 달 수 있다.
즉, tags 테이블과 photos 테이블은 다대다 관계인 것을 알 수 있다. 그래서 중간 테이블을 생성해서 일대다, 다대일 관계로 분리시켜주는 것이 좋다.

photo_tags 테이블 생성

# ------------------photo_tags-------------------

# photo 와 tags 의 중간 테이블
create table photo_tags(
    photo_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    foreign key (tag_id) references tags(id),
    foreign key (photo_id) references photos(id),
    primary key (photo_id, tag_id)
);

insert into photo_tags (photo_id, tag_id) VALUES
(2, 1),
(2, 2),
(2, 3);

select * from photo_tags;
describe photo_tags;

photos 테이블과 tags 테이블의 중간 테이블인 photo_tags 테이블을 생성했다.
외래키로 tag_id 와 photo_id 를 갖는다. 또한, 중복된 값을 갖지 못하게 하기 위해 tag_id 와 photo_id 를 기본키로써 설정해주었다.