명령 예약어 대문자 + 나머지 소문자 + camelCase

띄어쓰기 2번

CREATE TABLE users (
  id uuid NOT NULL PRIMARY KEY ,
  email varchar(50) NOT NULL UNIQUE,
  nickname char(20) NOT NULL UNIQUE,
  password_hash varchar(50) NOT NULL,
  created_at timestamp,
  updated_at timestamp,
  deleted_at timestamp
);

CREATE TYPE article_source AS ENUM ('NAVER', 'KOREA', 'CHOSUN', 'YNA')

CREATE TABLE article(
  id uuid NOT NULL PRIMARY KEY ,
  source article_source,
  resource_link varchar(600) NOT NULL,
  title varchar(200) NOT NULL,
  posted_at date NOT NULL,
  overview varchar(2000) NOT NULL,
  view_count int DEFAULT 0,
  comment_count int DEFAULT 0,
  created_at timestamp,
  updated_at timestamp,
  interest_id uuid NOT NULL REFERENCES interest,
  
  CONSTRAINT uk_same_resource_link UNIQUE (resource_link)
);

CREATE TABLE reply(
  id uuid NOT NULL PRIMARY KEY ,
  article_id uuid NOT NULL REFERENCES article,
  user_id uuid NOT NULL REFERENCES users,
  content VARCHAR(500) NOT NULL,
  created_at timestamp,
  like_count int DEFAULT 0,
  updated_at timestamp
);

CREATE TYPE noticeResource AS ENUM ('reply', 'interest')

CREATE TABLE notification(
  id uuid NOT NULL PRIMARY KEY ,
  user_id uuid NOT NULL REFERENCES users,
  context text NOT NULL,
  resource noticeResource,
  resource_id uuid NOT NULL,
  is_checked boolean default false,
  created_at timestamp,
  updated_at timestamp,
);

CREATE TABLE interest(
  id uuid NOT NULL PRIMARY KEY,
  name varchar(100) NOT NULL,
  keywords varchar(50)[] NOT NULL,
  subscribe_count int default 0,
  creation_at timestamp,
  updated_at timestamp
);

CREATE TABLE subscribe (
  id uuid NOT NULL PRIMARY KEY,
  user_id uuid NOT NULL REFERENCES users,
  interest_id uuid NOT NULL REFERENCES interest,
  creation_at timestamp,
  updated_at timestamp
);

CREATE TABLE comment_likes (
  id uuid NOT NULL PRIMARY KEY,
  created_at timestamp,
  comment_id uuid NOT NULL REFERENCES reply,
  user_id uuid NOT NULL
);

CREATE TABLE article_views (
  id uuid NOT NULL PRIMARY KEY,
  viewed_by uuid NOT NULL REFERENCES users,
  created_at timestamp,
  article_id uuid NOT NULL REFERENCES article,
  
  CONSTRAINT uk_viewed_by_article UNIQUE (viewed_by, article_id)

);

저희 ERD나 테이블을 살펴보면, 알림과 관련된 부분에서 연관 관계를 표현하기 힘든 까닭을 스스로 정리하다보니 외래키 참조의 사유에 대해 정의하게 된 것 같아서,, 이걸로 이해관계자 외의 다른 분들도 이해하기 쉽게 도메인에 관한 문서 만드는? 것이 좋을 것 같다고 생각합

작성

https://dbdiagram.io/d/6920296a228c5bbc1ae216ff