3.5Implementing Database Design Patterns

이번 섹션에선 인스타그램의 데이터베이스 스키마를 클로닝하는 프로젝트를 진행중인데, dbdiagram.io로 도식을 그린 데이터베이스 스키마를 PostgreSQL에 옮겨보자.

1. PGAdmin을 써서 새로운 db 생성하기
2. 디자인한 스키마를 CREATE TABLE문들을 통해 테이블들로 옮기기
3. 데이터베이스에 데이터 넣기
4. 쿼리 써보기

Creating Tables with Checks

PGAdmin에서 새로 database를 만들고, 해당 데이터베이스의 Query Tool을 연다.

users 테이블 추가하기

앞서 디자인한 users 테이블을 추가한다. phone 또는 email로 가입할 수 있으므로 CHECK문에 둘 다 NULL인 경우는 방지한다.

sql
CREATE TABLE users (
	id SERIAL PRIMARY KEY,
	created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
	username VARCHAR(30) NOT NULL,
	bio VARCHAR(400),
	avatar VARCHAR(200),
	phone VARCHAR(25),
	email VARCHAR(40),
	password VARCHAR(50),
	status VARCHAR(15),
	CHECK(COALESCE(phone, email) IS NOT NULL)
);

이렇게 테이블을 추가하면, PGAdmin에서는 해당 데이터베이스의 Schemas>Tables에서 users 테이블이 들어간 것을 볼 수 있다.

posts 테이블 추가하기

posts 테이블에서 제약에 관해 고려해야할 컬럼은 lat과 lng다. 위치 정보는 모든 post에 필수가 아니므로 NULL이어도 되지만, 만약에 들어간다면 유효성 범위(lat의 경우 -90에서 90사이, lng의 경우 -180에서 180사이)에 속해야만 한다.

또, foreign key로 user_id를 제약해야한다. 앞서 만든 users 테이블의 id와 연결되는 user_id 컬럼이 필요하고, 만약 users 테이블의 id가 삭제된다면 이 포스트 데이터 역시 삭제되어야 한다.

sql
CREATE TABLE posts (
	id SERIAL PRIMARY KEY,
	created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
	url VARCHAR(200) NOT NULL,
	caption VARCHAR(240),
	lat REAL CHECK(lat IS NULL OR (lat >= -90 AND lat <= 90)),
	lng REAL CHECK(lng IS NULL OR (lng >= -1800 AND lng <= 180)),
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE
);

comments 테이블 추가하기

sql
CREATE TABLE comments (
	id SERIAL PRIMARY KEY,
	created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
	contents VARCHAR(240) NOT NULL,
	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE
);

likes 테이블 추가하기

앞서 likes 기능 추가에서 결정했듯이, 이번 클로닝 프로젝트에선 post_id와 comment_id를 모두 포함하는 likes 테이블을 생성한다. 이렇게 하려면 아래와 같은 CHECK 조건문과 UNIQUE가 필요하다.

sql
CREATE TABLE likes (
	id SERIAL PRIMARY KEY,
	created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
	comment_id INTEGER REFERENCES comments(id) ON DELETE CASCADE,
	CHECK(
		COALESCE((post_id)::BOOLEAN::INTEGER, 0)
		+
		COALESCE((comment_id)::BOOLEAN::INTEGER, 0)
		= 1
	),
	UNIQUE(user_id, post_id, comment_id)
);

photo_tags 테이블 추가하기

사진에 태깅하는 것은 x, y 값이 필요하다.

sql
CREATE TABLE photo_tags (
	id SERIAL PRIMARY KEY,
	created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
	x INTEGER NOT NULL,
	y INTEGER NOT NULL,
	UNIQUE(user_id, post_id)
);

caption_tags 테이블 추가하기

캡션에 태깅하는 것은 updated_at이 필요하지 않다. 캡션에서 한 유저를 여러번 태그할 수 있으므로 UNIQUE 조건문은 필요 없을것 처럼 보이지만, 실제로 이 테이블에 들어가는 데이터는 태깅된 유저에게 알림을 보내는 기능 때문에 필요한 것이므로 여러번 중복 알림이 가지 않게 하기 위해서는 필요하다.

sql
CREATE TABLE caption_tags (
	id SERIAL PRIMARY KEY,
	created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
	UNIQUE(user_id, post_id)
);

hashtags 테이블 추가하기

sql
CREATE TABLE hashtags (
	id SERIAL PRIMARY KEY,
	created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
	title VARCHAR(20) NOT NULL UNIQUE
);

hashtags_posts 테이블 추가하기

sql
CREATE TABLE hashtags_posts (
	id SERIAL PRIMARY KEY,
	hashtag_id INTEGER NOT NULL REFERENCES hashtags(id) ON DELETE CASCADE,
	post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  UNIQUE(hashtag_id, post_id)
);

followers 테이블 추가하기

sql
CREATE TABLE followers (
	id SERIAL PRIMARY KEY,
	created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
	leader_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	follower_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	UNIQUE(leader_id, follower_id)
);

다음 포스팅에선 이렇게 만든 테이블들에 (더미) 데이터를 넣고 쿼리를 작성해보자.