[DB] PostgreSQL 진행 리뷰

[MR-39] db 테이블 setting 

 

table 리스트 

table 명	용도
workbook	문제집과 관련된 정보를 저장하는 테이블
workbook_try	특정 문제집을 사용한 이력이 있는 유저를 저장해두는 테이블
workbook_level	문제집 난이도 투표수를 저장하는 테이블
workbook_star	특정 문제집을 즐겨찾기 해둔 특정 유저를 저장하는 테이블
chapter	문제집의 목차(단원명)과 관련된 정보를 저장하는 테이블
user_info	유저의 정보를 저장하는 테이블
token	로그인 (리프레시 토큰)을 저장하는 테이블 -> redis를 이용하기로 하여 빠짐
problem	문제를 저장하는 테이블
solution	문제의 답안을 저장하는 테이블
problem_try	특정 문제를 시도한 특정유저를 저장하기 위한 테이블
user_level	유저들의 레벨과 관련된 정보 -> 레벨의 수가 적다면 불필요할 수 있음

 

 

데이터 베이스 & 스키마 리뷰

 

포함 관계 

DB instance > database > schema > table

Mathrone-gcp > mathrone-db > mathrone-schema, public > ...

*서로 다른 database내의 table끼리는 JOIN이 불가능하다.

 

Schema?

데이터베이스의 구조와 제약조건에 관해 전반적인 명세를 기술 한 것 

Attribute(개체의 특성), Entity(Attribute의 집합), Relation(Entity의 관계), Constraint(제약조건)

 

postgreSQL에서의 Schema

• table의 집합
• 하나의 database를 논리적으로 나누는 개념
• database가 schema의 상위 개념(*MySQL에서는 논리Database == schema)

 

 

default로 생성되는 database인 postgres

https://stackoverflow.com/questions/2370525/default-database-named-postgres-on-postgresql-server

Default database named postgres on Postgresql server

 

안쓰면 삭제해도 되는가? X

데이터베이스 자체는 사용하지 않지만, 외부접근(external utility program)에서 필요로 할 수도 있음

 

 

 

schema 왜 여러개를 제공할까? (왜 이런 기능이 있을까?)

• database의 object들을 organize (관리하기 편한 logical한 그룹으로 나눠서 관리에 용이)
• 서로서로 개입없이 여러 유저가 하나의 데이터베이스에 접근 가능(ROLE/USER/PREVILIAGE) 

 

*같은 데이터베이스 내(mathrone-db)에 있다면, 서로 다른 schema내 테이블 (mathrone-schema/public)끼리의 join이 가능하다.

스키마는 단순하게 순수한 logical한 구조와 (특히 보안상) 권한이 있는 사용자의 접근과 관계된 개념이다. 

즉, 우리 프로젝트에 대입하면,

workbook / user / problem 등 별로 나누어 관리에 용이하게 스키마를 분리 가능 

but, 다음과 같은 이유가 아니라면 굳이 public만을 이용해도 됨 

https://www.quora.com/Should-you-create-a-new-schema-or-just-use-the-public-schema-of-PostgreSQL

 

 

public schema 

별도의 스키마를 생성하지 않은 DB에 table(object)를 생성하면, public schema에 생성된다

모든 유저가 접근 가능하기에 보안상 취약함 

-> 주로 운영 DB에서는 public schema에는 object생성을 못하도록 revoke

 

public schema 관리하는 방법 

https://m.blog.naver.com/hanccii/221701395102

PostgreSQL db, schema, user 권한 관리

https://www.postgresql.org/docs/9.1/ddl-schemas.html

Schemas

https://www.postgresqltutorial.com/postgresql-schema/

 

 

**최종 리뷰

public schema를 사용하되, ROLE/USER/PREVILIAGE를 이용하여 보안이슈만을 챙기기 

mathrone-schema는 제거할 것 

 

 

 

 

테이블 리뷰(public schema)

 

 

CONSTRAINT 

ON DELETE / ON UPDATE

referenced vs referencing

 

 

 

제한	내용
NO ACTION	constraint를 검사했을 때, referencing row가 존재한다면, 에러를 발생시킨다.(롤백?)  아무것도 지정하지 않았을 경우 default이다. ( RESTRICT와는 다르게 trasaction이후로 constraint check를 연기한다.)
CASCADE	referenced row가 삭제/업데이트되면, referecing row도 삭제/업데이트 된다.
RESTRICT	referenced row의 삭제/업데이트를 막는다.
SET NULL( or DEFAULT )	referenced row가 삭제/업데이트 되면, 해당 columns이 default로 가지고있던 값이나 NULL로 referencing row들이 자동 setting된다

 

*timestamp 열이 있는 외래 키나 기본 키에 대해 CASCADE를 지정할 수 없다. CASCADE / RESTRICT가 가장 많이 사용됨 

https://m.blog.naver.com/jaebum85/110180097159

[mysql] 제약조건 관계의 테이블 데이터 삭제(delete)

https://www.postgresql.org/docs/9.5/ddl-constraints.html

Constraints

https://vertabelo.com/blog/on-delete-restrict-vs-on-delete-no-action/

ON DELETE RESTRICT vs NO ACTION

RESTRICT vs NO ACTION

 

workbook 

 

기획

• category 칼럼 하나 추가 해야 할 것 같음!
  • 수능완성/ 모의고사 등등 

 

 

구현

• workbook_id : 2자리 캐릭터로 고정되었음
• month / year : 2자리 캐릭터로 고정 
  
  • 01월 19년도 등등..이므로 2자리 고정이 나을 것 같음 
• type : 문제집인가 , 모의고사 인가 이므로 255자가 많다
• context : 기획에는 존재하지 않음

 

기타

test_workbook이라는 테이블이 존재함 (무슨용도? 삭제?)

 

 

수정제안

 

 

 

 

workbook _try

 

기획

book_id -> workbook_id 으로 변경 

메인화면에서 숨기기를 누르면, 메인화면에서 없어지는 기능인데,

데베에서도 뺀 후, 유저가 재시도하면 다시 데베에 넣는 방식을 사용 할 것인지 

hide (true)로 변경 후 유저가 재시도하면 다시 false로 변경할 것인지 

 

 

구현

• workbook_id : 2자리 캐릭터로 고정되었음
• hide :  default값 false 
• is_vote : default값 false (난이도 투표 중복 참여 불가)
• FOREIGN KEY(user_id) : CASCADE
  • DELETE : 유저가 사라지면, 문제집 시도(row)도 같이 사라져야함 
  • UPDATE : 유저가 변경되면, 문제집 시도에 존재하는 유저도 같이 변경되어야함
• FOREIGN KEY(workbook_id) : CASCADE
  • DELETE : 문제집이 사라지면, 문제집 시도 기록도 같이 사라져야함
  • UPDATE  : 문제집이 변경되면, 문제집 시도 기록도 같이 변경되어야 함 

 

 

기타

user_id : user_t참고 (기획 serial-nextval / 구현 integer) 

 

 

 

 

workbook_level

 

기획

 

 

구현

• workbook_id : 2자리 캐릭터로 고정되었음
• low/mid/high_cnt :  default값 0
• FOREIGN KEY(workbook_id) CASCADE
  • DELETE : 문제집이 사라지면, 문제집 난이도를 기록해 둘 필요 없음
  • UPDATE : 문제집 정보가 변경되면, 

 

 

기타

workbook에 새로운 값이 입력되면, 자동으로 workbook_level에 추가되어야 함 (어떻게..하지..)

=> 그냥 3개의 칼럼을 workbook에 넣어버리는건? 

=> 특정 테이블에 새로운 값이 입력되면, 다른 테이블에 자동으로 추가되어야함( 알아봐야함 )

 

 

 

workbook_star

 

기획

 

 

구현

• workbook_id : 2자리 캐릭터로 고정되었음
• FOREIGN KEY(workbook_id) CASCADE
  • DELETE
  • UPDATE 
• FOREIGN KEY(user_id) CASCADE
  • DELETE
  • UPDATE

 

 

기타

workbook_try/ workbook_star테이블을 하나로 통합 하는게 어떤지

• workbook_id (PK/FK)
• user_id (PK/FK) 
• star (default : false) 
• hide  (default : false) 
• is_vote  (default : false)  
• progress (default : 0 ) -> 맞은 문제 수 / 전체 문제 수 

특정 유저가 문제집을 한문제라도 풀 때 / 좋아요를 눌렀을 때 마다 데이터가 추가됨 

 

 

 

 

chapter

 

기획

 

 

구현

• chapter_id : 2자리 캐릭터로 고정되었음 (이거 우리끼리 고정한거라 아마 협의?필요 자리수맞아야 problem_id됨)

-> 2자리로 미리 고정을 해두던지 아니면 수동으로 2자리로 넣던지..해야함 2자리 이상불가능 

 

 

기타

 

 

user_t

user_info 로 이름을 하는 것도 괜찮을 지도 

 

기획

• 가입시 프로필 사진 지정 질문을 하지 않아서, 프론트에서 추가를 하거나 default이미지를 넣어주거나 해야함
• 폰번호를 입력하는 칸이 프론트에 없음

 

 

구현

• user_id : nextval이 아닌 integer인데 (얘만) 이유가?
• exp : default 0
• premium : default false
• email /phone number : NOT NULL???

 

기타

 

 

 

 

 

problem

 

 

기획

 

• level_of_diff 상중하밖에 없으면 integer안해도 될지도? ( 문제가 가진 경험치점수로 하기로했던듯) 

 

 

구현

• problem_id : 글자수 고정임
• workbook_id : 2자
• chapter_id :  2자
• FOREIGN KEY(workbook_id) CASCADE
  • DELETE
  • UPDATE 
• FOREIGN KEY(chapter_id) CASCADE
  • DELETE
  • UPDATE

 

기타

 

 

 

solution

 

 

기획

 

 

구현

• solution_id : 글자수 고정임
• problem_id : 글자수 고정
• FOREIGN KEY(problem_id) CASCADE
  • DELETE : 문제가 삭제되면, 자동으로 답안도 없어야함 
  • UPDATE : 문제 업데이트시 답안 업데이트 필요

 

기타

 

 

problem_try

 

 

기획

• date : 시도한 최신날짜로 할 것인지 ? / 기록을 다할것인지 ? 맞은날은 기록하지 않을 것인지? 

 

 

구현

• problem_id : 글자수 고정
• FOREIGN KEY(problem_id) RESTRICT
  • DELETE : 문제가 사라지면 문제 푼 기록이 삭제되어야 하나? 
  • UPDATE 
• FOREIGN KEY(user_id) CASCADE
  • DELETE
  • UPDATE

 

기타

 

 

 

 

user_level

 

 

기획

• 없애고 프론트에서 처리? 

 

 

구현

기타

 

 

테이블 데이터 검증

problem/solution : 03번 문제집만 있음

나머지는 데이터가 1개쯤씩 있던데 기능이 없는데 그냥 넣어둔 것인지?