220307(08) D+54(55) Instagram Schema Designing, Learn SQL

1. 인스타그램 스키마 디자인

1. 게시물(Post) 작성 기능

: 인스타그램에서는 여러 개의 사진(A)을 올릴 수 있다.

사진을 업로드할 때, 사진을 설명하는 간단한 글(C)이 올라간다.

 

2. 게시물에 댓글 달기 및 좋아요 기능

: 게시물이 업로드되면 다른 사용자는 이 게시물에 댓글(E)을 달 수 있고,

  좋아요(B) 를 눌러 관심을 표할 수 있다.

 

3. 해시태그 기능

: 게시물에 #감성 #맛집 등의 해시태그(D)를 남길 수 있으며,
이 해시태그를 누르면 이 해시태그가 사용된 모든 게시물을 한 데 모아 볼 수 있다.

 

4. follow 기능

: 인스타그램에서 친구 관계는 팔로워(follower)와 팔로잉(following)으로 나뉜다.

김코딩이 최해커를 following 하면, 최해커의 피드가 김코딩의 홈 화면에 나타난다.

최해커의 입장에서는 김코딩이 follower로 추가된다.

 

 

https://dbdiagram.io/home

사이트를 이용해서 직접 인스타그램 스키마 디자인 해본 것.

 

✷ timestamp()

네트워크 상의 현재 시간을 나타냄.

 

실제 인스타그램에서 기능하는 것들을 생각하며 관계를 이어나가면 많이 어렵지 않게 할 수 있었다.

다만 사용할 때는 그냥 했던 것들을 머리속으로 일대다 관계인지, 다대다 관계인지

하나씩 그려가는 것이 아직 익숙하지 않았다.

인스타그램 뿐만 아니라 다른 테마로도 그려보고 직접 테이블을 만들어보는 시간을 가져야겠다.

 

 

유저와 팔로우 - 다대다 관계

나는 여러명을 팔로우할 수 있다

다른 사람도 여러명을 팔로우할 수 있다

follow_follwer 테이블도 조인 테이블이라고 볼 수 있다.

user_id, follower_id 모두 users테이블에 있는 형태이기 때문에 사이에 끼지 않아도 조인 테이블이라고 볼 수 있다.

(참조하려는 대상이 어떤 것인지 볼 것)

 

 

포스트와 해시태그 - 다대다 관계

하나의 게시글에 여러 해시태그를 달 수 있다.

하나의 해시태그를 타고 들어가면 그 해시태그를 참조할 수 있는 다른 게시글들도 뜬다.

포스트와 좋아요 - 일대다 관계

포스트와 댓글 - 일대다 관계

하나의 포스트에 여러 개의 좋아요와 댓글

하나의 댓글은 하나의 포스트에만 종속되어 있음

 

comment와 like도 user와 관계를 맺을 수 있기 때문에 user_id가 foreign key로 관계 맺을 수 있음.

post_comments.user_id = 코멘트를 단 사람 /  post_likes.user_id = 좋아요 누른 사람

 

한명의 유저는 여러개의 댓글을 달 수 있다.

하나의 댓글은 여러 명의 유저가 쓸 수 있다? X → 한명의 유저에 종속되는 관계

 

 

댓글을 단 모든 유저의 이름과 댓글 내역 가져오기(READ)

SELECT users.username, post_comments.comment FROM users

INNER JOIN post_comments ON users.id=post_comments.user_id;

 

어느 게시글에도 댓글을 달지 않은 사람들 조회하기

SELECT users.username, post_comments.comment FROM users

LEFT OUTER JOIN post_comments ON users.id=post_comments.user_id

WHERE post_comments.user_id IS NULL;

 

SELECT users.username, post_comments.comment FROM post_comments

RIGHT OUTER JOIN users ON users.id=post_comments.user_id

WHERE post_comments.user_id IS NULL;

 

어느 게시글에도 댓글을 달지 않은 유저의 수 가져오기.

SELECT COUNT (users.username) FROM users

LEFT OUTER JOIN post_comments ON users.id=post_comments.user_id

WHERE post_comments.user_id IS NULL;

 

 

김코딩이 박해커를 팔로우한다(CREATE)

INSERT INTO follow_follwer (id, user_id, follower_id) // auto_increment로 해주면 id는 써줄 필요 없음

VALUES (SELECT id FROM users WHERE username='kimcoding', 

SELECT id FROM users WHERE username='parkhacker'); // 서브쿼리 활용

 

 

1번 유저가 21번 포스팅에 달았던 코멘트를 'hello world'로 수정한다(UPDATE)

UPDATE post_comments SET comment='hello world' WHERE user_id=1 AND post_id=21;

 

 

21번 포스팅에 달린 #맞팔 해시태그를 삭제한다(DELETE)

(조건을 걸어 원하는 행만 지울 수 있음)

DELETE FROM post_hashtags WHERE post_id=21

AND hashtag_id = (SELECT id FROM hashtags WHERE name = '맞팔');

 

DROP = 테이블 자체를 전부 지우거나 데이터베이스 자체를 지울 때 사용함

TRUNCATE = 뼈대는 남고 안에 기록된 데이터(각각의 행)들이 전부 지워짐

 

 

2. learn-sql sprint 진행

learn-sql sprint 진행

.env.example 파일을 열어서
DATABASE_USERNAME
DATABASE_PASSWORD
를 설정한다.
(DATABASE_NAME='learnmysql'
데이터베이스 이름은 스프린트에서 learnmysql로 고정함.)

저장 후 파일 이름을 .env로 바꾼다.

mysql -u root -p 명령어를 사용하여 sql 콘솔을 연다.

show databases;
create database learnmysql;
use learnmysql;
show tables;

learn-sql sprint 진행 결과,

sql 명령어들은 많이 해보면서 반복해서 쓰다 보면 자연스럽게 손에 익고 머리에 들어가게 된다.

다만 join은 조금 더 공부해야할 듯...inner join과 outer left / right join