데이터 모델링 2 (updated 21.05.07)

 * 모델링 링크 : www.erdcloud.com/d/ND6mfFGPXmRnkwso3

 * 더 나은 구조를 짜려고 생각하는 건 좋은데, 작업이 계속 지체됨. 여기까지 일단 구현해보고 좀 더 확장할 것

 

[ 테이블 유형 ]

1. 데이터 테이블(Data Table) : 조직에서 중요한 주제를 나타내며 데이터베이스에서 제공하는 정보의 기본 토대

 

2. 연결 테이블(Linking Table) : 다대다 관계의 두 테이블의 연결을 설정함

 

3. 부분 집합 테이블(Subset Table) : 특정 데이터 테이블과 연관되어 있으며 데이터 테이블의 주제를 매우 구체적으로 설명하는 필드들을 포함한다.

 

4. 검증 테이블(Validation Table) : 비교적 정적인 데이터를 포함하며 데이터 무결성의 중요한 구성요소

 

 

[ 테이블 이름 생성을 위한 지침 ]

1. 전체 조직에 의미가 있고 서술적인 고유한 이름을 만든다.

 - 고유한 이름을 사용하는 것은 각각의 테이블이 다른 주제를 명확하게 나타내고 조직의 모든 구성원들이 테이블이 표현하는 것을 이해하는 데 도움을 준다.

 

2. 정확하고 명확하며 모호하지 않게 테이블의 주제를 식별할 수 있는 이름을 만든다.

 - 애매하거나 모호한 이름은 일반적으로 테이블이 한 가지 이상의 주제를 나타내도록 하는 이름을 의미한다.

 

3. 테이블 주제를 전달하는 데 필요한 최소 개수의 단어를 사용한다.

 - 조직의 모든 구성원이 테이블에 대한 명세서 없이 테이블이 나타내는 것을 식별할 수 있어야 한다. 목표가 짧고 간결한 테이블 이름을 만드는 것일지라도 최소주의 접근 방식은 피하는 게 좋다. 'TD_1' 같은 매우 짧은 이름이 대표적인 예이다. 이름을 이렇게 짓는 놈들은 뚝배기로 머리를 쳐야 된다. 

 

4. 물리적 특성을 표현하는 단어는 사용하지 않는다.

 - file, record, table과 같은 단어는 필요 이상의 혼란을 가중시키기 때문에 테이블 이름으로 사용하는 것을 피한다. 이러한 유혀으이 단어를 포함한 테이블 이름은 하나 이상의 주제를 표현할 가능성이 매우 높다. Patient Record 같은 것이 대표적인 예. 'Record'라는 단어를 포함하며 잠재적으로 Patients, Doctors, Examinations 이 세 개의 주제를 표현한다. Patient Record를 제거하고 각 주제를 표현하는 세 개의 테이블을 만드는 것이 좋다.

 

5. 머리글자와 약어를 사용하지 않는다.

 - SC 같은 이름이 대표적인 예. 인사과 직원들은 이 단어를 운영 위원회(Steering Committees)라고 이해하고, 정보 시스템 직원들은 시스템 구성(System Configurations)이라고 이해하고, 보안팀 직원들은 보안 코드(Security Codes)라고 주장할 수 있음.

 

6. 테이블에 들어갈 데이터를 제한할 수 있는 독특한 이름이나 다른 단어는 사용하지 않는다.

 - 중복된 테이블 구조를 만들 수 있는 함정제 빠지지 않도록 도와주는 지침. 남서부 지역 직원과 같은 이름은 이 테이블에 입력할 수 있는 데이터를 상당히 제한함. 조직이 성장함에 따라 얼마나 많은 지역의 직원을 다루게 될지 모르니 이런 이름은 반드시 피한다.

 

 * 중복 구조가 가져오는 문제점

1) 사용자는 세 개의 테이블에서 동시에 데이터를 추출하는 데 어려움을 느끼게 된다.

2) 데이터베이스를 운연하는 사람은 테이블들이 항상 구조적으로 동기화되어 있어야 한다는 추가 부담을 갖게 된다. 만약 한 테이블에 필드를 추가, 삭제, 수정하게 된다면 다른 테이블들에도 동일한 작업을 수행해야 한다.

3) 데이터베이스를 운영하는 사람은 데이터 무결성에 대해서도 추가 부담을 갖게 된다. 한 직원이 다른 지역으로 이동할 때 데이터가 완전하고 정확하게 테이블 사이에서 전송될 수 있도록 보장해야 한다.

 

7. 암시적 또는 명시적으로 둘 이상의 주제로 식별되는 이름을 사용하지 않는다.

 - 부서 or 지사, 시설/건물 같은 이름을 말한다.

 

8. 이름의 복수형을 사용한다.

 - 테이블은 개체 또는 이벤트가 될 수 있는 하나의 주제를 나타낸다. 이 정의를 한 단계 확장시켜 테이블은 비슷한 개체가 사건들의 집합을 나타낸다고 주장할 수 있다. 테이블 이름을 복수형하는 사용하는 것은 사용자의 의도가 집합을 나타낸다는 것을 명확히 한다. 반면에 필드를 식별하는 단어들은 항상 단수형이다. 이 규칙을 따르게 되면 데이터베이스에 만드는 임의의 문서 내의 테이블 이름과 필드 이름을 구별하는 것이 쉬워진다.

 - 설명만 봐도 수학에서 나온 개념인 걸 알겠다. Codd, E.F.의 The Relational Model for Database Management : Version 2과 논문을 읽으면 더 잘 이해될 것 같다. 그리고 이 설명을 보니 테이블간의 관계 등이 더 잘 이해되네. 책과 논문을 꼭 찾아서 읽어보자.

 

 

[ 필드 이름 생성을 위한 지침 ]

1. 전체 조직에 의미가 있는 서술적인 고유한 이름을 만든다.

 - 지정된 필드 이름은 전체 데이터베이스에서 한 번만 나타나야만 한다. 유일한 예외는 필드가 두 테이블 사이의 관계를 설정하는 데 사용될 때이다. 이름을 보는 모든 사람에게 뜻이 정확하게 전달되도록 충분히 설명적이 되도록 한다

 

 - 객체처럼 필드를 사용할 때 항상 테이블과 연관되니 중복되는 컨텍스트를 반복하게 만드는 이 지침이 의미가 있나 싶었는데, 그 관점은 개발

 

2. 필드가 나타내는 특성을 정확하고 명확하게 그리고 모호하지 않게 식별하는 이름을 생성한다.

 - 전화번호는 부정확하고 모호한 필드 이름의 좋은 예다. 전화번호도 집 전화번호, 사무실 전화번호, 휴대폰 번호 등으로 종류가 많다. 이 모든 종류의 전화번호를 기록할 필요가 있다면 집 전화번호, 사무실 전화번호, 휴대폰 번호 필드들을 만든다.

 - 테이블 이름을 필드 이름의 접두어로 사용함으로써 주소, 도시, 국가와 같은 일반적인 필드 이름들을 해소할 수 있다. Employee Address, Customer Address, Supplier Address와 같은 필드 이름들을 만나면 테이블 이름의 첫 서너 글자를 접두어로 사용하여 접두어를 간결하게 할 수 있다. 이는 필드 이름들을 EmpAddress, CustAddress, SuppAddress로 변환할 수 있게 해준다.

 

3. 필드가 타나내는 특성의 의미를 전달하기 위해 필요한 최소 개수의 단어들을 사용한다.

 - 직원이 조직에 입사한 날짜를 기록하려고 한다면 Hired는 너무 짧고 Date That The Employee Was Hired는 너무 길다. Date Hired가 가장 적절한 이름이며, 필드가 의미하는 특성을 정확히 나타낸다.

 

4. 두문자어를 사용하지 말고, 약어는 신중하게 사용한다.

 

5. 필드 이름의 의미를 혼동시킬 수 있는 단어들을 사용하지 않는다.

 - Digital Identification Code Number 같은 예를 보자. digital과 number는 중복이다. 이를 제거하면 Identification Code Number가 되고 이는 다시 Identification Code, Identification Number로 나눌 수 있다. 둘 다 같은 의미를 가지므로 둘 중 하나를 선택하여 사용한다.

 

6. 하나 이상의 특성을 암시적 또는 명시적으로 식별하는 이름을 사용하지 않는다.

 - Area or Location, 전화\팩스와 같은 이름

 

7. 단수형의 이름을 사용한다.

 - Skills와 같은 복수형 이름을 가진 필드는 그것이 두 개 이상의 값들을 포함할 수 있다는 것을 암시한다. 이는 정규화의 대상이다.

 

[ 고민 ]

 - date의 경우 모두 date created로 통일해야 되나 아니면 테이블에 맞춰서 date posted, date commented로 명명해야 되나 고민. 일단은 created로 통일

 - date posted, date commented 같은 이름으로 지으면 date가 구분되니 이런 식으로 쓸까 했지만 수정하다는 의미는 edited로 같아서 date edited가 됨. 그냥 테이블 이름을 넣어서 date (that the) post (is) edtied = date post edited로 사용하는 게 나은 것 같다.

 - 식별 용도로 넣는 id는 id라 하지 말고 number라고 하는 게 더 나을 것 같다. number는 숫자로만 돼 있을 때 id는 숫자 + 영문자로 돼 있을 때 쓰도록 구분하면 타입에 대한 힌트를 좀 더 줄 수 있다.

 

 - date의 경우 null을 허용하지 않을 때 이 row가 수정된 것인지 아닌지 체크할 때 곤란을 겪는다. 생성 시각과 수정 시각을 비교해서 이 데이터가 수정됐는지 체크해야 되는데, null을 허용하지 않을 경우 보통 sysdate로 생성 시각, 수정 시각 값을 채운다. 이 두 값이 같다는 보장이 없다. null을 허용하면 처음 row를 생성할 때 수정 시각을 null로 처리해서 넣고 수정됐는지 체크할 때 수정 시각이 null인지 아닌지만 확인하면 된다. 이것보다 더 좋은 방법이 있는지도 알아봐야 한다.

 

 

[ Tips ]

1. char, varchar 쓰는 의미.

 - 다만 오라클에선 varchar2가, mssql에선 어떤 버전 이후의 varchar가 진짜 가변 길이로 저장한다고 알고 있습니다. 그래도 메모리 할당이나 내부io가 길이에 따라 늘어나는건 실행 플랜 보시면 나올겁니다. varchar의 근본 목적은 가변 길이 저장이 아니고 외부 io 성능 향상입니다. 클라이언로 보내는 길이를 가변으로 줄여 전송 부하와 클라이언트에서 할당할 메모리를 줄이는 등입니다. 이런 외부 io개선으로 최종적으로 char보다 나은 성능을 얻게 되는데 그게 목적입니다. (3번 reference)

 

 * Referecne

1. okky.kr/article/217655 

2. coding-restaurant.tistory.com/156

3. m.ppomppu.co.kr/new/bbs_view.php?id=developer&no=17073 : 여기 제일 아래 댓글

 

 

2. Row chaining, Row migration

 

 * Reference

tocsg.tistory.com/51#:~:text=%EC%9D%B4%EA%B2%83%EC%9D%84%20%ED%96%89%20%EC%9D%B4%EC%A0%84%2C%20Row%2DChaining,%EC%9E%88%EB%8A%94%20%EA%B3%B5%EA%B0%84%EC%9D%B4%20%EC%97%86%EA%B2%8C%EB%90%9C%EB%8B%A4.

 

3. 의미있는 정보를 테이블의 primary key로 정하면 나중에 문제가 발생할 수 있다.

 

 * 사용자 아이디를 primary key로 했을 때 문제점

1) 사용자 아이디가 개인정보로 지정되거나 아이디가 변경 가능하다는 정책이 생겼을 경우(비즈니스 변화)

 - 예전에 주민등록번호를 키값으로 했다가 개인정보 이슈로 인해 수집 못하게 되거나 암호화 걸어야 된다는 이슈가 생기면서 시스템이 헬로 변함. 주민등록번호가 필요 없는 테이블까지 주민등록번호를 가지는 문제가 있었음.

2) 관련 테이블 정보 모두 수정

 - 주민등록번호 정정과 같은 경우. 이런 경우 다른 테이블이 개인정보를 가지고 있다는 점도 문제지만 주민등록번호 변경했을 때 관련 테이블을 모두 바꿔야하는 문제가 생김. 그래서 의미 있는 값에 키값을 거는 것을 추천하지 않는다고 함.

3) 보안 문제

 - 보안에 문제가 있을 수 있다고 말하는데, 찾아본 글에선 제대로 설명을 하지 않음. 나중에 찾아볼 것.

 

 

4. 데이터베이스 지정한 문자 집합이 무엇인지 정확히 알아야한다.

select * from nls_database_parameters where parameter like '%CHARACTERSET%';
 => 데이터베이스 지정 character set

select char_length(컬럼) from 테이블명; => 글자수

select length(컬럼) from 테이블명; => byte수

 

 - character set에 따라 한글 한 글자 byte 수가 다르다. KO16KSC5601(한글 완성형), KO16MSWIN949에서는 한글 한 글자가2 Bytes이고  UTF8/AL32UTF8의 경우 한글 한 글자가 3 Bytes이다.

 

 * Refernece

1. studyforus.tistory.com/167 - 한글 인코딩 종류와 문제점

 

 

5. 다른 사람들이 varchar2(255)를 자주 사용하는 이유

 

 * Reference

1. 1. stackoverflow.com/questions/1217466/is-there-a-good-reason-i-see-varchar255-used-so-often-as-opposed-to-another-l : 다른 사람들이 varchar(255)를 자주 사용하는 이유.

 

 

[ Users Table ]

 - 커뮤니티 사용자 데이터.

 

 - 속성

 user number : primary key. 64비트 숫자를 모두 받을 수 있도록 넉넉하게 number(20)으로 받음.

 

 user id : 사용자 아이디. 영문자, 숫자, '-', '_'의 조합으로 6 ~ 12글자로 이루어진 아이디를 만들 수 있음. 유일해야 함.

 

 user password : 사용자 비밀번호로 bycrypt 해시 함수로 암호화하여 저장.  bycrypt 해시 함수로 암호화하면 최대 72글자가 나올 수 있음.

 

 * Reference

1. security.stackexchange.com/questions/39849/does-bcrypt-have-a-maximum-password-length - 왜 최대 72글자인가?

 

 

 user email : 사용자 이메일. 로컬 파트 64자, @ 1자, 도메인 파트 255자로 총 320자까지 허용한다고 한다. 아스키만 쓴다고 가정하고 320 bytes로 정해야겠다. 또 유일해야 한다. (추가 : 최근엔 모두 합쳐서 254가 되도록 제한한다고 한다. 전송할 때 <>가 포함되어 256자가 되도록 한다고 하는데 자세한 건 나중에 더 알아보면 된다. varchar2 1 byte 저장 최대치인 255로 하면 되겠다.)

 

 * Reference

1. blog.daum.net/sualchi/13720145

2. www.ietf.org/rfc/rfc2821.txt (4.5.3.1)

3. kor.go-travels.com/71642-is-email-address-length-limited-1171110-2645721#:~:text=%EC%A0%84%EB%8B%AC%20%EA%B2%BD%EB%A1%9C%EC%97%90%EB%8A%94%20%EA%BA%BE%EC%87%A0%20%EA%B4%84%ED%98%B8,%EB%A5%BC%20254%20%EC%9E%90%EB%A1%9C%20%EC%A0%9C%ED%95%9C%ED%95%98%EC%8B%AD%EC%8B%9C%EC%98%A4. : 최근 꺼

 

 

 user nickname : 내가 좋아했던 게임 롤의 닉네임 길이 제한이 한글 8자, 영어 16자라고 한다. 그래서 나도 16 bytes로 제한하기로 했다. 넷상에서 서로가 서로를 구별하는 방법은 닉네임뿐이므로 유일해야 한다. 깽판을 자주 치는 사람을 걸러낼 때 유용하다.

 

 user activity score : 유저 활동 포인트. 유저가 게시글 작성, 댓글 작성 등의 활동으로 얻은 점수로, 홈페이지에서 판매하는 상품을 구매할 때 사용할 수 있다. 점수는 64bit 길이의 숫자를 받을 수 있도록 number(20)으로 정했다.

 - (수정) point에서 score로 수정했다. 실제 들어가는 값은 1 대부분 1을 초과하는 값이니 points가 되어야 하는데, 필드명이 단수여야 한다는 지침을 어긴다.

 

 user picture : 사용자 이미지. 사용자 닉네임 옆에 표시될 작은 그림이다. 그림의 경로를 저장한다. 파일 경로의 최대 길이는 255자로 제한한다. 

 

 date user created : 계정이 생성된 날짜.

 date user edited : 계정이 마지막으로 수정된 날짜.

 

 user enabled : 사용자 계정 활성화 상태.

 

[ Posts Table ]

 - 게시글 테이블로 사용자들이 작성한 글에 대한 데이터.

 

 - 이름 후보가 board, article, post로 많았다. board는 우리나라에선 많이 사용되지만 구글에서 검색하면 바로 나무 판자랑 컴퓨터 보드가 나오는 걸 보고 제외함. aritcle은 뉴스와 잡지 같이 좀 형식을 갖춘 글을 의미하는 경우가 많아서 제외했다. 내가 기획하는 사이트는 좀 더 자유롭고 격의 없는 대화 느낌이 강해 forum, blog에서 많이 사용하는 단어 post를 선택했다.

 

 - 작성하고 나서 든 생각인데, 다른 DB 예시들 보면 게시판을 Board라 하고 그 튜플은 게시글로 하는 경우가 많았고 내가 웹 배우려고 선택했던 책도 DB가 이렇게 돼 있었다. 그 게시판을 확장해서 게시판에 대한 설정, 게시판의 종류 등을 추가하려고 했을 때 잘 안 됐는데, 이게 애초에 테이블 자체에 문제가 있어서 잘 확장이 안 됐던 거 같다. 이름만 게시판이지 실질 내용물은 게시글이니, 게시글하고 게시판 설정을 연결하는 꼴이 아닌가? 그리고 게시판 종류에 해당하는 테이블에 이름 붙일 때도 뭐 어떻게 붙여야 될지 몰라서 카테고리라고 짓고 종류를 구분하려고 했는데, 카테고리라는 이름이 너무 넓은 의미를 가지는 거 아닌가 고민했는데, 이게 이제 깔끔하게 해결되네 ㅋㅋ 파워 오브 데이터베이스 추천글 썼던 사람 정말 축복받아야 한다. 책에서 테이블 이름에 복수 명사를 붙여야 한다면서 했던 설명이 없었으면 무엇이 문제인지 인지하지도 못하고 고생할 뻔 했다. 이제 테이블이 좀 자연스럽게 보인다. 그리고 또 든 생각이 구조가 유저 - 게시글 - 포럼이면 게시글 테이블을 유저와 포럼의 다대다 관계를 해결하는 연결 테이블로 볼 수 있지 않을까?

 

 

 - 속성

post_number : primary key. 64bit 숫자를 받을 수 있도록 number(20)으로 선언함. post를 구별하기 위한 인조키.

forum_number : 게시물이 어느 게시판에 속해 있는지 표시하기 위한 필드. 게시판이 삭제되면 NULL이 되도록 지정한다. 혹시 관리상의 실수로 게시판을 삭제했을 때 주요 컨텐츠인 게시물을 모두 날려버릴 수 있다고 생각해서 이러한 처리를 하

post_category_name : // 미구현

post_title : 50글자로 제한. 한글은 1자, 영/숫자는 0.5자로 보고 100 bytes 할당. 

 

 * Reference

1. blog.stibee.com/%EC%A0%9C%EB%AA%A9-%EB%AA%87-%EA%B8%80%EC%9E%90%EA%B0%80-%EA%B0%80%EC%9E%A5-%ED%9A%A8%EA%B3%BC%EC%A0%81%EC%9D%BC%EA%B9%8C%EC%9A%94-4b6f11b8ac17 : 제목, 몇 글자가 가장 효과적일까요?

 

post_content : 2000 bytes로 제한했다.

post_writer

date_posted

date_post_modified

post_views_count

 

[ Forums Table ]

 - 게시판 테이블로 사용자들이 커뮤니케이션 활동을 하는 장소 데이터

 

 - 속성

forum number

forum name : 게시판 이름. 총 15자 한글은 1자, 영문/숫자/공백은 0.5자

forum description : 게시판 설명.

forum slug : 게시판 slug. 

 - A slug is the part of a URL which identifies a particular page on a website in an easy to read form. 이런 의미에서 볼 때 url보다는 slug로 이름 짓는 게 낫다. 그리고 후에 홈페이지 url 구조를 바꾸게 되더라도 DB는 안 건드려도 된다.

 

date forum created

 

[ Post_Categories Table ]

 - 게시글을 조회할 때 쓰는 글 카테고리. 게시판마다 사용할 수 있는 글 카테고리는 다르며 운영자가 사용할 수 있는 글 카테고리를 관리할 수 있고 유저는 글 카테고리에 따라 게시글을 검색할 수 있도록 함.

 - Categories for post 같은 이름이 

 

 - 속성

post_category_number

forum_number

post_category_name

 

 

[ Comments Table ]

 - 댓글 테이블로 사용자들이 게시글에 대해 작성한 답글 데이터

 - 위에 게시글을 유저와 포럼의 다대다 관계를 해결하는 연결 테이블로 본다고 했을 때 게시글은 유저가 포럼에 하는 행동으로 해석할 수 있다. 그럼 댓글도 유저가 게시글에 대해 어떤 행동을 한다고 해석할 수 있지 않을까? 그리고 유저가 게시글에 할 수 있는 행동은 댓글뿐만 아니라 좋아요, 싫어요도 있는데, 이것들을 모두 게시글에 대한 행위로 간주하고 합쳐서 관리해도 되지 않을까 생각해 보자.

 

 - 속성

comment_number

post_number

comment_parent_number

comment_content

comment_writer

date_commented

date _comment_modified

comment_is_deleted

 

[ Products Table ]

 - 사용자들이 살 수 있는 상품 데이터

 

 - 속성

product number

product name

product description

product price

product seller

date product registered

product is for sale

 

[ Orders Table ]

 - 유저가 구입한 상품에 대한 이력 데이터.

 

 - 속성

user number

product number

date ordered

 

[ Users_Products Table ]

 - 유저가 구입한 상품에 대한 데이터

 - 처음에는 주문 이력을 통해 유저가 구입한 상품들을 파악하면 된다고 생각했다. 이러한 생각은 내가 무의식적으로 유저가 구입한 상품이 사라지지 않으니 유저가 구입한 상품이 어떤 것이 있는지만 보여주면 된다고 생각한 것. 유저가 상품을 어떻게 사용할지에 대한 고려가 빠졌다. 이모티콘을 예를 들어보자. 유저가 당장은 그 이모티콘이 좋아서 사용한다고 하지만 나중에 더 마음에 드는 이모티콘들이 생겨 이것들을 추가하고 예전에 사용하던 이모티콘을 비활성화시키고 싶다면? 주문 이력만으로는 이러한 요구 사항을 충족시킬 수 없다.

 

 - 속성

user number

product number

date purchased

in use - 사용 중이라는 의미로 사용해야 되는데 이 단어가 맞나 불확실

 

[ Roles Table ]

 - 역할에 대한 데이터

 

 - 속성

role number : primary key로 역할 번호이다. 64bit 정수를 받을 수 있도록 number(20)으로 정함.

role name : 역할 이름. 최대 길이 50자로 제한한다. role_{역할}_{채널명}으로 구성한다. 채널명은 영문자, 숫자, '-', '_'를 허용한다.

 

[ Users_Roles Table ]

 - 유저가 어떤 역할을 맡고 있는지에 대한 데이터

 

 - 속성

user number

role number

 

[ Attachments Table ]

 - 게시글 첨부파일에 대한 데이터

 - 하나의 게시글에 여러 파일이 첨부될 수 있어 분리한 테이블

 

 - 속성

attachment number

post number

attachment name

attachment original name

attachment type

attachment byte size

 

[ Likes Table ]

 - 유저가 어떤 게시글을 좋아하고 싫어하는지에 대한 정보를 나타내는 테이블이다.

 - 테이블명 이게 맞나 ?

 

 - 속성

user number

post number

like

date created

 

 

 * referecne

1. www.erdcloud.com/d/PK2Ae7d4asTRqHpHx

2. 파워 오브 데이터베이스, Michael J. Hernandez

3. 핵심 데이터 모델링, 유동오