이번 5주차에는 관계형 데이터베이스 실습을 진행했습니다.
먼저 테이블을 만들기 전, 인스타그램 기능을 간단하게 ERD 다이어그램으로 만들었습니다.
이후 실습, 과제가 목표로 하는 데이터를 DB에서 가져오기 위해 쿼리(SQL)문 작성을 연습했습니다.
개인적으로 SQL 부분이 부족하다고 느꼈는데 여실히 드러났습니다..
SQL 쿼리문을 더 열심히 공부해야겠다고 느낄 수 있는 좋은 기회였습니다.
그럼 이제 본격적으로 포스팅 시작하겠습니다!!!
1. ERD 작성 (인스타그램)
작성한 인스타그램 ERD 다이어그램은 아래와 같다. 릴스, 스토리, 피드 2명이서 공유 등 훨씬 많은 기능이 있지만 제일 중요하다고 생각하는 인스타그램 특유의 SNS 기능만 작성해보았다. 아래는 작성한 ERD 이미지다.
이제 본인이 생각한 table 구성은 아래와 같다. 간단하게 구상했다. 연결선을 통해 join 관계를 확인할 수 있다.
만약 피드나 댓글을 삭제한다면 row를 삭제하지 않고 속성인 status를 'ACTIVE'에서 'INACTIVE'로 변경한다.
기본적으로 status, created_at, updated_at 속성을 가진다.
- User : 비밀 번호 기능을 생략해 salt 같은 속성은 생략했다. 간단하게 유저를 저장할 테이블이다.
- Post : 피드를 저장할 테이블이다.
- Photo : 사진을 나타내는 테이블이다. 사진을 DB에 저장하지 않고 사진을 저장한 스토리지의 URI를 가진다. 또한 type을 두어 프로필 사진인지, 피드 사진인지, 스토리는 없지만 스토리 사진인지 구분한다.
- Comment : 피드에 대한 댓글을 저장할 테이블이다. 좋아요는 단순히 INT로 구현했다. 페이스북, 인스타그램은 댓글, 대댓글을 계층형으로 보여주기 때문에 이를 위한 속성 level을 만들었다.
- Follow : 팔로워와 팔로잉을 구현하기 위한 테이블이다. 팔로워는 나에게 친구를 걸고 있는 유저, 팔로잉은 내가 친구 추가를 한 유저다.
- Hashtag : 해시태그를 구현할 테이블이다.
- Like_Post : 유저가 피드에 좋아요를 누르는 관계는 다대다라고 판단했다. 다대다는 일대다로 해석해 풀어야하므로 테이블을 하나 만들었다.
- Post_Hashtag : 해시태그와 피드가 다대다라고 판단했다. 마찬가지로 다대다 관계이므로 테이블을 하나 만들었다.
기존에 한 번 노마드코더에서 인스타그램 클론 코딩을 하면서 설계했던 것이 좀 더 도움이 되었고, 커머스 서비스를 한 번 설계해본 적이 있는데 그 또한 도움이 되었다. 물론 지금 ERD는 그 때 프로젝트만큼의 노력을 들이지는 않았지만 확실히 시야가 넓어진 것을 체험할 수 있었다. 이렇게 ERD를 작성했다.
AqueryTool의 편리함 짱짱!!!!
설정한 DBMS와 ERD에 따라 create table 생성문도 만들어주고 test data의 insert 문도 만들어준다.
우리는 간단한 SQL 쿼리 실습을 위해 몇 가지 테이블을 아래와 같이 생성했다. 아래는 테이블 생성 쿼리다.
//유저 테이블 생성 쿼리
CREATE TABLE User
(
id BIGINT NOT NULL AUTO_INCREMENT,
nickname VARCHAR(45) NOT NULL COMMENT '유저 닉네임',
name VARCHAR(45) NOT NULL COMMENT '유저 이름',
introduce VARCHAR(45) NULL COMMENT '유저 웹사이트',
website TEXT NULL COMMENT '유저 소개글',
status VARCHAR(10) NOT NULL DEFAULT 'ACTIVE' COMMENT 'ACTIVE, INACTIVE, DELETED',
created_at TIMESTAMP NOT NULL DEFAULT current_timestamp,
updated_at TIMESTAMP NOT NULL DEFAULT current_timestamp on update current_timestamp,
email VARCHAR(45) NOT NULL,
password VARCHAR(45) NOT NULL,
PRIMARY KEY (id)
);
ALTER TABLE User COMMENT '유저 테이블';
//피드 테이블 생성 쿼리
CREATE TABLE Post
(
id BIGINT NOT NULL AUTO_INCREMENT,
user_id BIGINT NOT NULL,
content VARCHAR(300) NOT NULL,
status VARCHAR(45) NULL DEFAULT 'ACTIVE',
created_at TIMESTAMP NULL DEFAULT current_timestamp,
updated_at TIMESTAMP NULL DEFAULT current_timestamp on update current_timestamp,
like INT NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);
ALTER TABLE Post COMMENT '게시글';
//사진 테이블 생성 쿼리
CREATE TABLE Photo
(
id BIGINT NOT NULL AUTO_INCREMENT,
post_id BIGINT NOT NULL,
photo_url TEXT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT current_timestamp,
updated_at TIMESTAMP NOT NULL DEFAULT current_timestamp on update current_timestamp,
status VARCHAR(45) NOT NULL DEFAULT 'ACTIVE',
user_id BIGINT NOT NULL COMMENT '사진 주인',
type VARCHAR(45) NOT NULL COMMENT '프로필, 피드, 스토리 구분',
PRIMARY KEY (id)
);
//팔로잉 테이블 생성 쿼리
CREATE TABLE Follow
(
id BIGINT NOT NULL AUTO_INCREMENT,
following_id BIGINT NOT NULL,
follower_id BIGINT NOT NULL,
status VARCHAR(45) NOT NULL,
PRIMARY KEY (id)
);이제 본격적으로 SQL 실습을 진행해보겠다.
실습 2. 유저 피드 조회 쿼리 설계
우리는 유저 닉네임, 유저 프로필 사진, 유저 이름, 유저 소개글, 유저 사이트, 게시물 개수, 팔로워 수, 팔로잉 수를 조회할 것이다. 또한 피드의 사진도 가져올 것이다.
아래는 실습 쿼리다.
//피드의 사진 데이터를 제외하고 모든 것을 가져오는 한방 쿼리
select name, nickname, introduce, website,
if(post_Count is null , 0, post_count) as post_count,
if(follower_count is null, 0, follower_count) as follower_count,
if(following_count is null, 0, following_count) as following_count
FROM User
left join
(select user_id,count(id) as post_count from Post where status = 'ACTIVE' group by user_id ) p on p.user_id = User.id
left join
(select follower_id, count(following_id) as follower_count
from Follow where status = 'ACTIVE' group by follower_id ) f1 on f1.follower_id = User.id
left join
(select following_id, count(follower_id) as following_count
from Follow where status = 'ACTIVE' group by following_id) f2 on f2.following_id = User.id
Where User.id = 1;
//게시물들의 첫 번째 사진을 가져오는 것
select p.id, ph.photo_url From Post as p
join User as u on u.id = p.user_id
join Photo as ph on ph.post_id = p.id and ph.status = 'ACTIVE'
where ph.status = 'ACTIVE' and u.id = 1
group by p.id
order by p.created_at desc;group by와 join과 서브쿼리에 대한 내용을 잘 숙지해야겠다고 느꼈다.
챌린지 과제 3. 게시물 리스트 조회 쿼리 설계
여기서 좋아요에 대한 고민이 있었다.
필자가 알기로는 좋아요 같은 것은 computing field라고 DB에 속성으로 값을 저장하는 것이 아닌 값을 가져올 때 계산을 해서 가져온다고 했다. 그리고 예전에 분석한 기억으로는 인스타그램은 팔로우와 팔로잉의 변화를 매번 계산하지 않고 일정시간마다 계산을 했던 걸로 기억한다. 물론 이 경우는 인풀루언서들의 팔로우와 팔로잉의 경우다 ㅎㅎ...
일단은 그래서 그냥 속성 값으로 좋아요를 INT형으로 저장해 가져오기로 했다.
아래는 챌린지 과제 SQL 쿼리문이다.
select p.content, p.created_at, p.like ,ph.photo_url, p.id as post_id, ph.id as photo_id from User as u
left join Follow as f on f.follower_id = u.id
left join Post as p on p.user_id = f.following_id and p.status = 'ACTIVE'
left join Photo as ph on ph.post_id = p.id and ph.status = 'ACTIVE'
where u.id = 2 and u.status = 'ACTIVE'
order by p.created_at desc;이렇게 마무리 했다.
스터디를 하고 복습을 해보면서 여러 부족한 점과 자극이 되었던 부분이 있다.
댓글도 테이블로 만들어 해당 피드의 댓글을 count로 전체 갯수를 가져왔어야 할 것 같았고,
서브쿼리를 언제 써야할지가 개념이 잘 안잡혔다. 이 부분은 공부를 더 진행해야 할 것 같다.
SQL에 관한 공부를 더 하다가, 차후 동아리 스터디에서 spring jdbc template를 사용할 때 이번 챌린지 쿼리를 더 멋지게 작성해봐야겠다. 일단 지금은 시험기간이라.. 핑계일수도 있지만!!
또 자극이 되었던 부분도 많다. 데이터베이스 수업을 듣지 않은 신입생 스터디분이 계셨는데, 엄청 멋지게 스터디를 준비해오셨다. ERD도 열심히 짜신 노력이 보였고 쿼리문도 상당히 좋았다. 다른 스터디원들도 ERD에서 고민을 많이 하신 모습과 서브 쿼리에 대해 진지하게 고민하신 분도 계셨다.
본인의 생각을 공유하면서 스프링 파트장님에게 좋은 이야기도 들을 수 있었다.
더욱 더 파이팅하는걸로!!!
확실한 건 SQL은 더 공부하자..ㅎㅎ 특히 조인과 서브쿼리!!!
그리고.. 이번 주 중간고사 파이팅!!! 중간고사를 마치면 JAVA와 SQL에 대한 공부를 집중적으로 해야겠다.
SQL 공부를 위해 개발자 선배님들 톡방에서 좋다고 추천해주신 SQL 첫걸음 책을 샀다.
이후에는 Real Mysql이나 불친절한 SQL 프로그래밍 책을 볼 예정이다.
이상으로 금주 UMC 활동 포스팅을 마칩니다. 감사합니다!!!
댓글