[DB] 프로젝트 데이터베이스 모델링 기록
1/ 개념 정리
부모테이블, 자식 테이블
부모 테이블은 다른 테이블과 관계에서 상위에 있는 테이블을 의미한다. 부모 테이블은 주로 기본 데이터를 저장하고, 다른 테이블과의 관계에서 외래 키(Foreign Key) 역할을 한다. 예를 들어, "Users"라는 부모 테이블이 있고, 이 테이블은 사용자의 기본 정보를 저장하는 역할을 할 수 있다.
자식 테이블은 다른 테이블과 관계에서 하위에 있는 테이블을 의미한다. 자식 테이블은 부모 테이블과의 관계에서 외래 키(Foreign Key)를 가지며, 부모 테이블과의 관계를 통해 추가적인 데이터를 저장한다. 자식 테이블은 부모 테이블의 주 키(Primary Key)를 외래 키(Foreign Key)로 참조하여, 부모 테이블과의 관계를 유지한다. 예를 들어, "Orders"라는 자식 테이블이 있을 수 있고, "Users" 테이블의 주 키를 외래 키로 참조하여 해당 주문이 어떤 사용자에게 속하는지를 나타낼 수 있다.
부모 테이블과 자식 테이블 사이의 관계를 통해 데이터베이스에서 데이터를 구조화하고 관리할 수 있으며, 이를 통해 데이터 간의 관계를 표현하고 데이터의 무결성을 유지할 수 있다.
위의 사례를 보면 부모, 자식, 손자까지 식별 관계로 기본 키를 전달하고 있다.
식별 관계에서 자식 테이블은 부모 테이블의 기본 키를 포함해서 복합 키를 구성해야 한다.
위의 경우는 식별관계이다.
한편, 비식별 관계로 구현할 시, 자식 테이블의 일반키로 외래키가 들어가게 된다.
식별 관계 vs. 비식별 관계
식별 관계
식별관계란, 부모 엔티티의 주식별자를 상속 받아 자식 엔티티의 주식별자로 사용하는 경우를 말한다.
위의 예시를 보면, PARENT_ID라는 PARENT 테이블의 PK가 CHILD 테이블의 PK이자 FK로 사용되고 있다.
식별관계는 부모와 자식 간의 관계가 강한 종속적 관계일 때 사용한다.
가장 중요한 특징은 부모 엔티티가 반드시 생성되어야 자식 엔티티가 생성될 수 있다는 것이다.
부모 엔티티 없이 자식 엔티티는 생성이 불가하다.
부모 엔티티의 PK가 자식 엔티티의 PK이기도 하기 때문에, 이러한 일이 발생하는 것이다.
비식별 관계
비식별 관계란, 부모 엔티티의 주식별자를 상속 받아, 자식 엔티티의 일반 (비식별자) 속성으로 사용하는 경우이다.
비식별 관계는 부모와 자식 엔티티 간의 관계가 더 느슨한 경우 사용한다.
부모 없는 자식 엔티티가 생성될 수 있거나 서로 생명 주기가 달라 부모가 먼저 소멸하는 경우에 사용 가능하다.
또는 부모의 주식별자가 참조 성격이라 별도의 비즈니스적 의미가 있는 주식별자를 생성하는 게 더 유리하다고 판단될 때나,
자식과 관련된 다른 엔티티로 주식별자가 상속되는 것을 차단하기 위해 비식별자 관계를 정의하기도 한다.
이번에 DB 모델링을 할 때 계속 고민되었던 부분이다.
보드게임 정보와 보드게임 후기 테이블을 비식별 관계 또는 식별 관계 중 어떻게 연결해야 할지 고민이 들었다.
모델링 책을 찾아보니, 원칙적으로는 두 엔티티 간의 관계가 종속적일 때는 식별자로서 상속(식별 관계)하고,
하위 엔티티가 상위에 종속되지 않으면, 즉 상위 부모 엔티티가 존재하지 않아도 하위 자식 엔티티가 존재할 수 있으면
일반 속성으로 상속하는 것이 원칙이라고 한다.
(종속/참조 관계는 두 엔티티 사이의 성격을 파악하는 논리적인 개념이고 식별/비식별 관계는 CASE 등의 물리적인 표기 방법)
비식별 관계에는 두 가지 유형이 있다.
1. 필수적 비식별 관계
2. 선택적 비식별 관계
필수적 비식별 관계 vs. 선택적 비식별 관계
두 관계는 외래키의 NULL 값을 허용하느냐 여부에 따라 차이가 있다.
필수적 비식별 관계
필수적 비식별 관계에서는 부모 엔티티의 기본 키 값을 참조하는 자식 엔티티의 외래 키(Foreign Key)는 NULL 값을 허용하지 않는다.
즉, 자식 엔티티가 부모 엔티티와의 관계를 항상 가져야 한다는 의미이다.
이는 식별 관계처럼 자식 테이블에 데이터를 추가할 때 반드시 부모 테이블과 관계를 맺어주어야 한다는 것을 의미한다.
NOT NULL로 항상 관계가 있다는 것을 보장하므로 내부 조인이 사용 가능하다.
(이러한 이유 때문에 필수적 비식별 관계를 추천하곤 한다)
따라서 부모 엔티티의 기본 키 값이 삭제되면, 이와 연관된 자식 엔티티들도 함께 삭제되거나 다른 방식으로 처리되어야 한다.
ex. 주문 테이블 - 주문 상품 테이블
주문 ID 컬럼은 NULL 값을 허용하지 않음
선택적 비식별 관계
선택적 비식별 관계에서는 부모 엔티티의 기본 키 값을 참조하는 자식 엔티티의 외래 키가 NULL 값을 허용한다.
이 경우 자식 엔티티와 부모 엔티티 간의 관계가 선택적이므로, 자식 엔티티가 부모 엔티티와의 관계를 가지지 않을 수 있다.
NULL을 허용하므로 JOIN 할 때에 외부 조인을 사용해야 한다.
ex. 학생 테이블 - 학생 주소 테이블
학생 주소가 등록되어 있지 않을 수도 있음.
따라서 학생 ID 컬럼은 NULL 값 허용
cf.
내부 조인 (Inner Join)
내부 조인은 두 테이블 간에 일치하는 레코드만을 선택하여 결과를 반환한다.
이 조인 방식은 특정 조건을 충족하는 레코드만을 반환하므로 결과 집합이 작고, 이에 따라 처리 속도가 빠를 수 있다.
또한, 내부 조인은 보통 인덱스를 잘 활용할 수 있으므로 성능이 향상될 수 있다.
따라서 대부분의 경우에는 내부 조인이 성능적으로 더 좋을 수 있다.
외부 조인 (Outer Join)
외부 조인은 왼쪽 테이블 또는 오른쪽 테이블에 일치하는 레코드가 없어도 결과를 반환한다.
이는 특정 조건을 만족하지 않는 레코드들도 결과에 포함되기 때문에 결과 집합이 크고, 이에 따라 처리 속도가 느릴 수 있다.
또한, 외부 조인은 더 복잡한 처리 과정을 필요로 하며 인덱스 활용이 더 어려울 수 있다.
하지만 특정 상황에서는 외부 조인이 필요한 경우도 있다.
예를 들어, 왼쪽 테이블의 모든 레코드를 보존하면서 오른쪽 테이블과 일치하는 레코드를 가져오는 경우에 유용할 수 있다.
식별 관계와 비식별 관계의 장단점
DB 설계 관점에서 보면 다음과 같은 이유로 식별 관계 보다는 비식별 관계를 선호한다.
- 식별 관계는 부모 테이블의 기본키를 자식 테이블로 전파하면서 자식 테이블의 기본키 컬럼이 점점 늘어난다. 예를 들어 부모 테이블은 기본 키 컬럼이 하나 였지만 자식 컬럼은 2개, 손자는 3개 결국 쿼리는 복잡해지고 기본 키 인덱스가 불필요하게 늘어날 수 있다.
- 식별 관계는 2개 이상의 컬럼을 합해서 복합 기본키로 만들어야 하는 경우가 많다.
- 식별 관계를 사용할 때 기본키로 비지니스 의미가 있는 자연 키 컬럼을 조합하는 경우가 많다. 반면에 비식별 관계의 기본키는 비즈니스와 전혀 관계없는 대리키를 주로 이용한다. 비즈니스 요구사항은 시간이 지남에 따라 언젠가 변하기에 식별 관계의 자연 키 컬럼들은 변경이 어려워진다.
- 식별 관계는 부모 테이블의 기본 키를 자식 테이블의 기본 키로 사용하므로 비식별 관계보다 테이블 구조가 유연하지 못하다.
- 일대일 관계를 제외하고 식별 관계는 2개 이상의 컬럼을 묶은 복합 기본키를 사용한다. JPA 에서 복합 기본키는 별도의 복합키 클래스를 만들어 관리해야 한다. 따라서 컬럼이 하나인 기본키를 매핑하는 것보다 많은 노력이 필요하다.
식별 관계의 장점은 아래와 같다.
- 기본키 인덱스를 활용하기 좋고 상위 테이블의 기본 키 컬럼을 자식, 손자 테이블들이 가지고 있으므로 특정 상황에 조인 없이 하위 테이블 만으로 검색을 완료할 수 있다.
SELF JOIN 이란?
동일한 테이블을 두 번 조인하는 유형
각 테이블 구분을 위해 별칭(Alias) 필수
각 팀별 매핑되는 상위 조직을 조회 가능
cf. SELF JOIN을 사용하는 이유
한 행에 있는 값을 같은 행에 있는 다른 값과 비교해야 할 때가 생기고는 함.
이를 해결하려면 자기 자신 테이블을 조인하여 같은 행의 데이터 값을 가지고 비교하는 방법밖에는 없기 때문.
SELF JOIN을 사용하는 케이스
1. 위계성 데이터를 다룰 때
2. 순차성 데이터 다룰 때
3. 1개의 테이블 안에 관계성이 명시되어야 할 데이터가 여러 개 존재할 때
정리해보면,
1. 자기 자신을 합치는 경우
2. 뎁스(다단계) 관계를 나타내기 위해 테이블을 조인하는 경우에 SELF JOIN을 사용한다.
SELECT D.DEPT_NAME AS "팀명"
, DE.DEPT_NAME AS "담당명"
FROM DEPARTMENT D
JOIN DEPARTMENT DE ON (D.PARENT_DEPT_ID = DE.DEPT_ID);
본인 팀이 담당 탐인 경우를 구하는 것
SELECT
cust.customer_id,
cust.firstname,
cust.lastname,
cust.birthdate,
cust.spouse_id,
spouse.firstname AS spouse_firstname,
spouse.lastname AS spouse_lastname
FROM customer AS cust
INNER JOIN customer AS spouse
ON cust.spouse_id = spouse_id;
SELF JOIN을 카테고리 테이블에 적용해보면 category id 컬럼과 parent category id 컬럼을 둔다고 생각해보자.
이때 새로운 카테고리를 추가하면 새로운 카테고리를 추가하기만 하면 되고,
뎁스 최하위의 카테고리만 가지고 있으면 parent category id 필드를 통해 연관된 모든 category를 찾아낼 수 있다.
2/ 본격적인 ERD 설계
채팅 ERD 설계
유저는 여러 개의 채팅방을 가질 수 있고 또 채팅방 하나는 여러 명의 유저를 가질 수 있기 때문에 둘은 N:M 관계라고 할 수 있다.
근데 유저 테이블과 채팅방 테이블, 그리고 채팅 메시지 테이블까지 어떻게 연결해줘야 할지 고민이 되었다.
고민의 결과 관계 테이블을 만들고 원래는 채팅 메시지가 해당 관계 테이블과 연결되어 있었는데, 이를 채팅방과 연결해주었다.
이유는 다음과 같다.
before 케이스의 경우, 채팅 메시지를 가져오려면 채팅 메시지 테이블은 채팅 참여 테이블과 연결되어 있기 때문에, 채팅 참여 테이블의 복합키인 유저 id와 채팅방 id가 반드시 필요하다. 그래서 채팅 메시지는 해당 두 컬럼들을 가지고 같이 조회해주어야 한다.
즉 쿼리문이 좀 복잡해진다.
채팅 메시지에서 원하는 메시지를 조회해주기 위해서 두 컬럼이 필수적으로 필요하기 때문에
채팅 참여 테이블과 유저, 채팅방 간에 연결이 식별관계이거나 필수적 비식별 관계이어야만 한다. (이건 수정 후에도 똑같긴 하다)
그러나 after 케이스의 경우, 채팅 메시지를 가져올 때 채팅방과 연결해줌으로써 채팅방 id를 식별자로 가지는 테이블을 만들어주었는데, 이렇게 되면 테이블이 더 간결해지고 복합키로 기본키를 관리할 필요 없이 채팅방 id만 기본키로 관리해주면 된다.
또한 해당 채팅방에 있는 모든 채팅을 가져온다고 했을 때 쿼리문이 간결해진다.
근데 또 고민이 되는 부분은 채팅 참여와 부모 테이블들을 비식별 관계로 할건지 식별 관계로 할건지 였다.
만약 식별관계로 한다면, 유저가 들어간 채팅방을 조회한다고 했을 때, 유저 id와 채팅방 id가 모두 복합키로서 동작하기 때문에 두 컬럼을 모두 알아야만 조회가 가능하다. 즉, 유저 id만 알 때 중간 테이블에서 채팅방 및 채팅메시지 조회가 어려운 것이다.
따라서, 비식별 관계로 해서 유저 id와 채팅방 id를 모두 일반 컬럼으로 두고,
중간 테이블에서 유저 id만 알아도 채팅방 조회가 가능하도록 했다.
유저와 마이페이지 관계
유저와 마이페이지는 1:1 관계를 갖고 있기 때문에 1:1 식별 관계를 설정해주었다.
지금 생각해보니까 유저 id만 pk로 가지는 게 식별 관계를 더 잘 살릴 수 있지 않을까 하는 생각이 든다.
위처럼 하면 마이페이지 id와 유저 id를 모두 pk로 가지게 된다.
여러 종류의 게시판 카테고리
먼저, 관리의 용이성을 위해 모든 글을 포함하는 전체 게시글 테이블을 부모테이블로 두기로 했다.
그리고 각 게시글 테이블에서 전체게시글 테이블을 일대다 관계로 참조하여 전체게시글 테이블에 없지만 자신의 테이블에는 필요한 컬럼을 추가하기로 했다.
그런데 고민되는 부분이 있었다.
게시판에 뎁스가 존재했다.
커뮤니티 - 자유게시판, 모임, 자료실, 유저소식, 문의글
게임 - 보드게임 정보, 보드게임 평가 (한줄평, 코멘트, 평점)
두 게시판의 성격이 약간 달랐다.
각 탭을 눌렀을 때 보이는 게시글의 종류가 달랐던 것이다.
따라서 이 뎁스를 나타내기 위해 게시글 종류를 뜻하는 Article Category 테이블을 약간 수정하면 좋겠다는 생각을 했다.
현재 전체 게시글 테이블과 게시글 종류 테이블은 아래와 같다.
하나의 카테고리가 부모 카테고리와 연관되도록 자가참조(Self Join) 관계를 맺어주었다.
이제 새로운 카테고리가 추가된다면 '게시글종류' 테이블에 새로운 카테고리를 추가하기만 하면 된다. 또한 '전체게시글' 테이블에 있는 각각의 글은 뎁스 최하위의 카테고리를 갖고 있으면 '부모 카테고리 id' 필드를 통하여 추후에 게시판 카테고리 뎁스가 추가되더라도 연관된 모든 카테고리를 찾아낼 수 있다.
--- 작성 중 ----
보드게임 정보 테이블과 전체 게시글의 관계를 어떻게 설정해줘야 할지?
복합키로 하면 되나?
self join이란?
보드게임 정보 글도 전체게시글에 포함시킬지 상의해봐야할것같습니다!
저희가 아까 그 부분에서 이미지 연관관계 맺을때 어려움을 겪었어서요!
그리고 알림 부분은 아직 연관관계를 맺지 못했습니다!
전체 게시글 -> 모든 글 부모 (포함)
전체 게시글을 이미지 테이블이 참조하는 것
댓글 테이블?
알람 테이블 - 댓글 테이블 만들어서 연결, 채팅 메시지랑 알람이랑 연결해야 함
1. 댓글 테이블 어떻게 짤지?
2. 대댓글과 멘션 어떻게 짤지?
3. 알림이랑 댓글 테이블 어떻게 연결?
알림 테이블 구조 : 알림이 있게 한 글의 PK, 알림 종류 필요
http://www.ciboard.co.kr/manual/tables/notification
4. 알림이랑 채팅 테이블 어떻게 연결?
5. 퀴즈?
- 게시판 카테고리 테이블 자기참조로 수정?
- 전체게시글 테이블에 없는 컬럼만 추가해서 쓰기
- 이미지 테이블 : 썸네일 img, 기타 img 들을 자기참조 관계로
- 댓글 테이블 : 대댓글 만들 시 하나의 테이블로 만들어서 자기참조 관계로 or 댓글테이블 & 답글테이블 둬서 댓글번호로 참조
[라라벨] 댓글 대댓글 DB설계하기
댓글, 대댓글 구성을 위한 최소한의 DB설계이니 각자 상황에 맞게 칼럼을 바꾸거나 추가해서 사용하시길 바랍니다. 그 외에 더 좋은 DB구성 방식이 있으시면 댓글로 남겨주세요. 댓글과 대댓글 DB
xerar.tistory.com
데이터베이스 테이블(댓글 테이블) 구조 파악
데이터베이스에 CRUD 작업을 처리하기 위해서는 먼저 테이블의 구조를 파악할 필요가 있다.데이터 베이스에서 테이블의 구조를 표현하는 방법으로는 ERD(Entity-Relationship-Diagram)를 주로사용한다.테
1.ironwoong.com