How to implement DB Schema[2]

How to implement DB Schema[1]에서 작성한 뒤, workflow가 on right track인지에 관해 질문하였고 답변을 받았다.

DB schema should be way more specific and must include every details not only the relations between attributes and properties. The work you uploaded seems quite rough, sufficient to understand your idea though.

Refer to the link below for further examples and details: https://dbschema.com/documentation/schema.html

The diagram shown on the link is called UML diagram which includes 1. relations (1 to 1, 1 to n, n to n) between attributes, 2. attribute type(INT, DOUBLE, FLOAT, etc), and DB schematic info(Views, Sequences). Your future work should include all of these information precisely.

Kudos for your first step.

그가 보내준 link를 보며 한번 더 정리하는 시간을 가지도록 한다. 영어로 구글링하는 습관을 들여주기 위해 영어로 comment를 달아주는 그의 정성에 박수치며 되도록 영어로 정리해보도록 하겠다.

Schema
- Composed of grouping of tables

Tables
- colleciton of data in specific topic
- tables can be connected(maintain relationship) through foreign keys(used to connect other tables)
*cautions*
1.foreign key should be 'Null' or same as Parent table's primary key
2.Parent table's primary key and unique key can be foreign key
- columns and rows

Columns
- hold specific type such as numeric, dates ....
- can't be duplicated
- always have primary key(one of columns) to identifies other data

Rows
- can be zero to ~~
- should be identified by primary key(column)

Before composing tables, I will organize Demands for implementing DB Schema.


Demands

1. ID, password, Email-address are needed to sign-up
2. User can be recognized by user_id
3. Feed is assgined to Users. There are Posts, Followers, Followings, Story in Feed.
4. Feed is recognized by user_id
5. When we post Posts to Feed, there are date, contents, Photos and user_id.
6. Posts is recognized by user_id and date
7. Users can write comments to others Posts and theirs.
8. There are date, user_id(writers), contents in Comments
9. Comments is recognized by user_id
10. User can express Story in Feed
11. Story is recognized by user_id
12. There are contents, photos, and date when users post Story

I jotted down Demands for Composing DB Schema as workflow roughly.
Then, it's time to determine which ones should be entities, attributes.

1.
Entities : Users
Attributes : user_ID(PK), password, Email-address

2.
Entities : Feed
Attributes : user_ID(PK),Posts, Followers, Following, Story

3.
Entities : Comments
Attributes : user_id(writers)(PK), date, contents

4.
Entities : Posts
Attributes : user_id(PK), date(candidate key), texts, photos,


Story must be in Feed so, I didn't regard Story as Entity.

 

위 내용들을 토대로 DB SCHEMA를 구성해보았다. 

구성을 해놓고 DB의 규칙들에 집중하여 살펴보니 부족한 점들이 이렇게나 많이 나왔다. 

Feed테이블이 중요할 것 같았지만 삭제하기로 결정하였고, 

PK,FK, 정규화 규칙 등에 신경쓰며 수정을 어떻게 해야할지 방향성들을 정립해나가는 중이다 .

계속 하다보니 감도 생기고 흥미를 느껴서 매우 좋다.