Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리하는 글

인스타그램 클론 코딩을 해보자.

단 , 실제 스키마는 무척 복잡하겠지만 여기서는 매우 단순화시켜서 만들어보도록 한다.

1. User Table

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

User Table에서 주목해볼만한 것은 username에 UNIQUE속성을 줘서 이름이 중복되지 않게 했다는 점.

2. Photo Table

CREATE TABLE photos(
	id INTEGER AUTO_INCREMENT,
    image_url VARCHAR(255) NOT NULL,
    user_id INT,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY(id),
    FOREIGN KEY(user_id) REFERENCES users(id)
);

Photo Table에서 주목해볼만한 것은 Foreign key로 users테이블의 id를 설정해줌으로써 어떤유저가 올린 사진인지를 알 수 있게 했다는 점.

3. Comments Table

CREATE TABLE comments(
	id INT AUTO_INCREMENT,
    comment_text VARCHAR(255) NOT NULL,
    user_id INT NOT NULL,
    photo_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY(id),
    FOREIGN KEY(user_id) REFERENCES users(id),
    FOREIGN KEY(photo_id) REFERENCES photos(id)
);

Comments Table에서 주목해볼만한 점은 user_id와 photo_id를 외래키설정해줘서 어떤 유저가 어떤 사진에 코멘트를 남겼는지 알 수 있게 했다는 점.

4. Likes Table(좋아요 테이블)

CREATE TABLE likes(
	user_id INT NOT NULL,
    photo_id INT 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)
);

likes테이블에서 주목해볼만한 점은

• comments table과 마찬가지로 user_id와 photo_id를 외래키로 설정해줘서 어떤 유저가 어떤 사진에 좋아요를 눌렀는지를 알 수 있게 했다는 점
• 다른 테이블에 흔히 있는 id(PRIMARY KEY) 칼럼이 없다는 점 : 다른 테이블에서 likes에 대한 정보를 사용하지 않기 때문에
• PRIMARY KEY로 user_id와 photo_id를 사용했다는 점
  • PRIMARY KEY를 복수지정할 수 있다.
  • 이로인해 중복된 유저가 중복된 사진에 좋아요를 누를 수 없다.

mysql> INSERT INTO likes(user_id, photo_id) VALUES(1,1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO likes(user_id, photo_id) VALUES(1,1);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'likes.PRIMARY'

이렇게 중복된 유저가 중복된 사진에 좋아요를 누르면 PRIMARY KEY중복으로 에러가 나면서 데이터가 들어가지 않는다.

mysql> INSERT INTO likes(user_id, photo_id) VALUES(1,2);
Query OK, 1 row affected (0.00 sec)

물론 이렇게 같은 유저(1)가 다른사진(2)에 데이터를 넣는 것은 가능하다.
그러니깐 PRIMARY KEY를 두개를 설정해놓았을 경우, 두개 모두에 중복된 데이터를 넣으려고 할 때 제약조건에 걸린다. 그러니깐 현재 (1,1)이라는 데이터가 들어가 있다고 가정하면 (1,2), (2,1)등 하나의 키만 걸리는 경우는 제약조건에 걸리지 않는다. 하지만 (1,1) 이렇게 두 개의 키 모두 중복되는 경우에 제약조건에 걸리게 된다.

5. Follow Table

CREATE TABLE follows(
	follower_id	INT NOT NULL,
    followee_id	INT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(follower_id) REFERENCES users(id),
    FOREIGN KEY(followee_id) REFERENCES users(id),
    PRIMARY KEY(follower_id, followee_id)
);

Follower와 Followee를 나누지 않고 Follow Table에서 전부 처리해줬다. 이부분은 생각을 좀 해보면 알 수 있는데, 어떤 사람(1)이 다른사람(2)를 팔로우 하게되면 2는 팔로위가 되고, 1은 팔로워가 된다.

Basically, how do we capture the data when i follow another user and they may or may not following back? It doesn't matter. I'm still able to follow them and that's how we need to capture. It's one way relationship and the easiest way to do it is with a single table that will call followers.

And All that it is, is to user ID. So both of them are foreign keys, referring to userID, a Different User ID. That's important. We can't have a user follow him or herself. So there differnet id's. And I call them follower ID and followee ID, you know user1 ID and user2 ID. But the idea here is that you can tell who is following who, that the follower is the person following that followee.

6.Tag Table

Tag 테이블은 여러가지 방법이 있겠지만 조인테이블을 사용해서 만들어 주기로 한다. 이부분은 250강 강의를 다시 봐보는 것도 괜찮을 거 같다.

CREATE TABLE tags(
	id INTEGER PRIMARY KEY,
    tag_name VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE photo_tags(
	photo_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    FOREIGN KEY(photo_id) REFERENCES photos(id),
    FOREIGN KEY(tag_id) REFERENCES tags(id),
    PRIMARY KEY(photo_id, tag_id)
);