참고
v1 대회 부문, 신청 테이블 설계 돌아보기…
division을 json 통으로 넣었는데 왜 그랬는지 돌아보자..
"division": [
{
"pricingPolicy": {
"normal": 40000,
"earlyBird": 12.5
},
"constantFactor": {
"birth": [ 2010, 2015 ],
"gender": "female",
"uniform": "gi",
"divisionName": "초등부"
},
"variableFactor": {
"belt": [ "white" ],
"weight": [ -30, -35, -40, -45, -50, -55, 55 ]
}
},
},
{
"pricingPolicy": {
"normal": 40000,
"earlyBird": 12.5
},
"constantFactor": {
"birth": [ 2010, 2015 ],
"gender": "male",
"uniform": "gi",
"divisionName": "초등부"
},
"variableFactor": {
"belt": [ "white" ],
"weight": [ -30, -35, -40, -45, -50, -55, 55 ]
}
},
}, ...
]왜 json 으로 저장함?
- 대회사가 어떤 데이터가 필요한지 몰랐기 때문에, 수정이 자유로운 json 으로 저장하자..!
정규화 안함? divisionId, genderId, beltId, weightId 없음?
- json 통으로 넣는데 정규화가 될리가 없지..
- 각 속성에 id 를 추가했으면 괜찮았을거같은데, 그 당시에는 확장성과 데이터 정합성을 생각하는 능력이 부족했음
아니 그럼 대회 신청 테이블에 division 정보 어케 저장함?
- id 가 없으니 신청 내역에 저장할 때, 각 divison 속성을 복사해서 저장함… (uniform, gender, belt, weight 등…)
{
"CompetitionApplicationInfos": [
{
"id": 1,
"playercategory": "허민_테스트",
"playerBirth": "980101",
"phoneNumber": "01012345678",
"uniform": "gi",
"gender": "male",
"divisionName": "일반부",
"belt": "blue",
"weight": "-76",
"team": "김포 골드라이온",
"earlyBirdDeadline": "2022-05-01 21:31:00",
"pricingPolicy": { "normal": 40000, "earlyBird": 12.5 },
"status": "ACTIVE"
},
{
"id": 2,
"playercategory": "허민_테스트",
"playerBirth": "980101",
"phoneNumber": "01012345678",
"uniform": "gi",
"gender": "male",
"divisionName": "일반부",
"belt": "blue",
"weight": "앱솔",
"team": "김포 골드라이온",
"earlyBirdDeadline": "2022-05-01 21:31:00",
"pricingPolicy": { "normal": 40000, "earlyBird": 12.5 },
"status": "ACTIVE"
}
]
}v1 에서의 문제점
- 부문을 수정했을 때, 그 부문에 신청한 유저의 신청 정보가 같이 수정되어야하는데 수정이 안됨
- 사실
divisionName + uniform + gender + belt + weight를composite primary key로 생각하고, 모든 컬럼을 for문 돌면서 구현하려면 구현 할 수 있긴 하다.
v2 요구사항
- 유저 신청 내역을 수정 할 수 있어야 함 - v1 에서도 됨
- division을 수정 할 수 있어야 함 - v1 에서도 됨
- division 수정 시 해당 division에 신청한 신청 정보도 수정 되어야 함 - v1 에서 안됨
- division을 수정하면 해당 division에 신청한 데이터를 하나하나 수동으로 수정 해줬어야 함
용어 정리
Relation
- 릴레이션은 행과 열로 구성된 테이블을 말한다.
- relation == table
keys
- 키(key): 특정 튜플을 식별하는 고유한 속성 또는 속성 집합.
- 슈퍼키(super key): 튜플을 유일하게 식별할 수 있는 모든 속성의 집합.
- (주민번호), (고객번호)
- (주민번호, 이름), (고객번호, 이름, 주소)
- 후보키(cendiditekey): 튜플을 유일하게 식별할 수 있는 최소 속성 집합.
- 같은 도서를 다시 구입할 수는 없다고 가정할 때, 후보키는 (고객번호, 도서번호)
- 복합키(composite primary key): 두 개 이상의 속성으로 구성된 후보키.
- (고객번호, 도서번호) 키조합을 후보키로 사용
- 기본키 (Primary Key): 여러 후보키 중 하나를 대표로 선택한 유일하고 변경 불가능한 키.
- 대체키 (Alternate Key): 기본키로 선택되지 않은 나머지 후보키들.
- 외래키 (Foreign Key): 다른 릴레이션의 기본키를 참조하는 속성, 릴레이션 간 관계를 표현.
종속, 결정자
- 속성 A의 값이 결정되면, 다른 속성 B의 값도 유일하게 정해지는 상황을 “속성 B는 속성 A에 종속된다(depend on)“
- 또는 “속성 A는 속성 B를 결정한다(determine)“라고 합니다.
- 이 관계를 ‘A → B’로 나타내며, 이때 A를 B의 결정자라고 부릅니다.
함수 종속성
- 수학에서, 변수 x와 y 사이에서 x의 값이 정해지면 y의 값도 결정되는 관계를 “y는 x의 함수”라고 합니다.
- 비슷한 방식으로, ‘학생 번호 → 주소’처럼 왼쪽 속성의 모든 값에 대하여 오른쪽 속성의 값이 유일하게 결정될 때,
- 이를 “함수적으로 종속된다(functionally dependent)“라고 합니다.
- 릴레이션의 속성 간에 이러한 함수적으로 종속하는 성질을 “함수 종속성(FD, Functional Dependency)“이라고 부릅니다.
함수 종속성 규칙
규칙 이름 기호 설명 부분집합 규칙
(Subset Rule)Y ⊆ X ⇒ X → Y 만약 Y가 X의 부분집합이라면, X는 Y를 함수적으로 결정한다. 증가 규칙
(Augmentation Rule)X → Y ⇒ XZ → YZ X가 Y를 함수적으로 결정하면, 어떤 속성 집합 Z를 X와 Y에 각각 추가해도 동일한 결정 관계가 유지된다. 이행 규칙
(Transitivity Rule)X → Y, Y → Z ⇒ X → Z X가 Y를 함수적으로 결정하고 Y가 Z를 함수적으로 결정한다면, X도 Z를 함수적으로 결정한다. 결합 규칙
(Union Rule)X → Y, X → Z ⇒ X → YZ X가 Y와 Z를 각각 함수적으로 결정한다면, X는 Y와 Z의 결합도 함수적으로 결정한다. 분해 규칙
(Decomposition Rule)X → YZ ⇒ X → Y, X → Z X가 Y와 Z의 결합을 함수적으로 결정한다면, X는 Y와 Z를 각각 함수적으로 결정한다. 유사 이행 규칙
(Pseudo-Transitivity Rule)X → Y, WY → Z ⇒ WX → Z X가 Y를 함수적으로 결정하고, W와 Y의 결합이 Z를 함수적으로 결정한다면, W와 X의 결합도 Z를 함수적으로 결정한다.
이상현상
이상현상은 잘못 설계된 데이터베이스에서 발생하는 문제로, 데이터 조작(삽입, 삭제, 수정) 시 예상치 못한 부작용이 발생하는 현상입니다. 각 이상현상을 한줄로 요약하면:
- 삭제 이상 (Deletion Anomaly): 특정 정보 삭제 시 연관된 정보까지 함께 삭제되는 문제.
- 삽입 이상 (Insertion Anomaly): 새로운 정보 삽입 시 필요하지 않은 정보까지 입력해야 하는 문제 또는 NULL 값 문제.
- 수정 이상 (Update Anomaly): 정보 수정 시 데이터의 중복으로 인해 일관성이 깨지는 문제.
정규화
- 중복, 이상현상을 줄이기 위함
- 이 문제를 해결하기 위해 릴레이션을 분해하는 과정이 필요하며, 이를 정규화(normalization)라고 합니다.
- 정규화는 릴레이션을 분해하여 이상 현상을 제거하는 과정으로, 필요할 때까지 릴레이션을 계속 분해하여 이상 현상이 없어질 때까지 진행됩니다.
Note
- 2NF부터 BCNF까지는 모두 함수 종속성에 관한 내용이다.
- BCNF는 최대한으로 함수 종속성을 배제한 상태를 말한다.
함수 종속성을 배제한다?
- 데이터베이스의 릴레이션에서 모든 속성이 완전히 기본 키에만 종속되도록 설계하는 것을 의미합니다.
- 즉, 릴레이션 내의 모든 속성이 기본 키에만 의존한다.
- 기본 키가 아닌 다른 속성에 의존하는 경우(부분 함수 종속성이나 이행적 함수 종속성)를 제거하는 것입니다.
1NF
1 정규형의 조건
- 각 컬럼이 하나의 속성만을 가져야 한다.
- 하나의 컬럼은 같은 종류나 타입(type)의 값을 가져야 한다.
- 각 컬럼이 유일한(unique) 이름을 가져야 한다.
- 칼럼의 순서가 상관없어야 한다.
정규화가 안된 데이터 (Divison 등록 데이터)
| category | uniform | gender | belt | weight | price |
|---|---|---|---|---|---|
| 초등부 | gi | female&male | white | -30, -40 | 40000 |
| 중등부 | gi | female, male | white | -30, -40 | 40000 |
| 어덜트 | gi | female | white, blue | -40, -50 | 50000 |
| 어덜트 | gi | male | white, blue | -50, -60 | 50000 |
| 어덜트 | no-gi | male | white, blue | -50, -60 | 40000 |
제 1 정규형을 만족하는 Division Table
| category | uniform | gender | belt | weight | price |
|---|---|---|---|---|---|
| 초등부 | gi | female&male | white | -30 | 40000 |
| 초등부 | gi | female&male | white | -40 | 40000 |
| 중등부 | gi | male | white | -30 | 40000 |
| 중등부 | gi | male | white | -40 | 40000 |
| 중등부 | gi | female | white | -30 | 40000 |
| 중등부 | gi | female | white | -40 | 40000 |
| 어덜트 | gi | female | white | -40 | 50000 |
| 어덜트 | gi | female | white | -50 | 50000 |
| 어덜트 | gi | female | blue | -40 | 50000 |
| 어덜트 | gi | female | blue | -50 | 50000 |
| 어덜트 | gi | male | white | -50 | 50000 |
| 어덜트 | gi | male | white | -60 | 50000 |
| 어덜트 | gi | male | blue | -50 | 50000 |
| 어덜트 | gi | male | blue | -60 | 50000 |
| 어덜트 | no-gi | male | white | -50 | 50000 |
| 어덜트 | no-gi | male | white | -60 | 50000 |
| 어덜트 | no-gi | male | blue | -50 | 50000 |
| 어덜트 | no-gi | male | blue | -60 | 50000 |
2NF
2정규형의 조건
- 1 정규형을 만족해야 한다.
- 모든 컬럼이 부분적 종속(Partial Dependency)이 없어야 한다. == 모든 칼럼이 완전 함수 종속을 만족해야 한다.
Faq
Partial Dependency?
- composite primary key 조합 중 특정 컬럼에만 종속되는것
Composite Primary key?
- 유니크한 데이터가 없을때도 컬럼을 조합해서 유니크한 데이터를 만든 key
- 위에 division table에서는
composite primary key={category, uniform, gender, belt, weight}Primary key?
- 행을 구분하기 위해 만든컬럼.
- 유니크한 데이터를 담고있는 컬럼.
제 1정규형을 만족하는 Divsion Table 에서 composite primary key = {category, uniform, gender, belt, weight} 이고,
price 는 composite primary key 에 종속적이다. 따라서 제 2 정규형을 만족한다.
2정규형 불만족 예시 (partial dependency가 존재)
- 만약 price 가 category에 의해서만 가격이 달라진다고 가정해보자 (price가 category에 종속적이다.)
- 초등부 이면 40000원, 어덜트 이면 50000원 이라고 할 때,
- price 는 composite primary key 중 category에 종속되기 때문에 partial dependency가 존재한다.
- 즉 2정규형을 만족하지 못한다.
- 위 가정에서 2정규형을 만족시켜야한다고 할때 아래처럼 분리할수 있다.
| category | uniform | gender | belt | weight |
|---|---|---|---|---|
| 초등부 | gi | male | white | -30 |
| 어덜트 | gi | male | white | -40 |
| category | price |
|---|---|
| 초등부 | 40000 |
| 어덜트 | 50000 |
- 나주하에서는 대회사 마다 price가 여러 키 조합에 의해 결정되고, 대회사 마다 기준이 다르다.
- 때문에, price를 따로 분리하지 않고, 2정규형을 만족한다.
3NF
3 정규형 조건
- 2 정규형을 만족해야 한다.
- 기본키를 제외한 속성들 간의 이행 종속성 (Transitive Dependency)이 없어야 한다.
이행 종속성이란 A→B, B→C 일 때 A→C 가 성립하면 이행 종속이라고 한다.
이미 Division Table 은 3정규형도 만족하기 때문에 3정규형을 만족하지 않은 예시를 만들어보자
3 정규형 불만족 예시
- Application Table + Division Table
| applicationId | plyerName | category | uniform | gender | belt | weight | price |
|---|---|---|---|---|---|---|---|
| 1 | a | 어덜트 | gi | male | white | -50 | 50000 |
| 2 | a | 어덜트 | gi | male | white | absol | 50000 |
| 3 | b | 어덜트 | gi | male | white | -50 | 50000 |
| … |
CPK = {category, uniform, gender, belt, weight} 라고 할때
applicationId → CPK CPK → price applicationId → price
를 만족하고 이행 종속성이 생기기 때문에 3정규형을 만족하지 못한다.
아래와같이 3정규형을 만족하도록 분리 할 수 있다.
3 정규형 만족 예시 (Application Table, Divison Table 분리)
- Division Table
- CPK 대신 divisionId를 둔다.
| divisonId | category | uniform | gender | belt | weight | price |
|---|---|---|---|---|---|---|
| 1 | 어덜트 | gi | male | white | -50 | 50000 |
| 2 | 어덜트 | gi | male | white | absol | 50000 |
| … |
- Application Table
| applicationId | plyerName | divisonId |
|---|---|---|
| 1 | a | 1 |
| 2 | a | 2 |
| 3 | b | 1 |
| … |
BCNF (Boyce-Codd 정규형)
- 모든 결정자가 후보 키인 상태입니다.
- 이는 3NF를 더 엄격하게 적용한 것으로, 릴레이션에서 함수 종속성에 의한 이상 현상을 최대한 배제한다.
나주하에서 사용할 Division Table, Application Table 예시
- BCNF 까지 만족한다.
Dvision Table
| id | category | uniform | gender | belt | weight | price |
|---|---|---|---|---|---|---|
| 1 | 초등부 | gi | male | white | -30 | 40000 |
| 2 | 초등부 | gi | female | white | -30 | 40000 |
| 3 | 중등부 | gi | male | white | -30 | 40000 |
| 4 | 중등부 | gi | male | white | -40 | 40000 |
| 5 | 중등부 | gi | female | white | -30 | 40000 |
| 6 | 중등부 | gi | female | white | -40 | 40000 |
| 7 | 어덜트 | gi | female | white | -40 | 50000 |
| 8 | 어덜트 | gi | female | white | -50 | 50000 |
| 9 | 어덜트 | gi | female | blue | -40 | 50000 |
| 10 | 어덜트 | gi | female | blue | -50 | 50000 |
| 11 | 어덜트 | gi | male | white | -50 | 50000 |
| 12 | 어덜트 | gi | male | white | -60 | 50000 |
| 13 | 어덜트 | gi | male | blue | -50 | 50000 |
| 14 | 어덜트 | gi | male | blue | -60 | 50000 |
| 15 | 어덜트 | no-gi | male | white | -50 | 50000 |
| 16 | 어덜트 | no-gi | male | white | -60 | 50000 |
| 17 | 어덜트 | no-gi | male | blue | -50 | 50000 |
| 18 | 어덜트 | no-gi | male | blue | -60 | 50000 |
| … |
Application Table
| id | playerName | playerPhoneNumber | divisionId | applicationPakageId |
|---|---|---|---|---|
| 1 | A | 01011111111 | 11 | 1 |
| 2 | A | 01011111111 | 15 | 1 |
| 3 | C | 01033333333 | 2 | 2 |
| 4 | D | 01044444444 | 2 | 3 |
| … |
Application Pakage Table
| id | userId |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
| … |
Create Division Table
Division Pack
| category | uniform | gender | belt | weight | price |
|---|---|---|---|---|---|
| 초등부 123 | gi | mixed | white | -30,-40, … | 40000 |
| 초등부 456 | gi | male | white | -40,-50 | 50000 |
| 초등부 456 | gi | female | white | -30, -40, | 50000 |
| 어덜트 | gi | female | white | -30, -40, | 50000 |
export interface IDivisionyPack {
categorys: DivisionEntity['category'][];
uniforms: DivisionEntity['uniform'][];
genders: DivisionEntity['gender'][];
belts: DivisionEntity['belt'][];
weights: DivisionEntity['weight'][];
birthYearRangeStart: DivisionEntity['birthYearRangeStart'];
birthYearRangeEnd: DivisionEntity['birthYearRangeEnd'];
price: PriceSnapshotEntity['price'];
}{
"divisionPacks": [
{
"categorys": [
"초등부"
],
"uniforms": [
"GI"
],
"genders": [
"MALE",
"FEMALE"
],
"belts": [
"화이트",
"유색"
],
"weights": [
"-30",
"-40"
],
"birthYearRangeStart": "2007",
"birthYearRangeEnd": "2008",
"price": 40000
},
]
}{
"result": [
{
"competitionId": 1,
"category": "초등부",
"uniform": "GI",
"gender": "MALE",
"belt": "화이트",
"weight": "-30",
"birthYearRangeStart": "2007",
"birthYearRangeEnd": "2008",
"status": "ACTIVE",
"priceSnapshots": [
{
"price": 40000,
"divisionId": 1,
"id": 1,
"createdAt": "2024-03-23T22:52:47.788Z"
}
],
"id": 1,
"createdAt": "2024-03-23T22:52:47.788Z",
"updatedAt": "2024-03-23T22:52:47.788Z"
},
{
"competitionId": 1,
"category": "초등부",
"uniform": "GI",
"gender": "MALE",
"belt": "화이트",
"weight": "-40",
"birthYearRangeStart": "2007",
"birthYearRangeEnd": "2008",
"status": "ACTIVE",
"priceSnapshots": [
{
"price": 40000,
"divisionId": 2,
"id": 2,
"createdAt": "2024-03-23T22:52:47.788Z"
}
],
"id": 2,
"createdAt": "2024-03-23T22:52:47.788Z",
"updatedAt": "2024-03-23T22:52:47.788Z"
},
{
"competitionId": 1,
"category": "초등부",
"uniform": "GI",
"gender": "MALE",
"belt": "유색",
"weight": "-30",
"birthYearRangeStart": "2007",
"birthYearRangeEnd": "2008",
"status": "ACTIVE",
"priceSnapshots": [
{
"price": 40000,
"divisionId": 3,
"id": 3,
"createdAt": "2024-03-23T22:52:47.788Z"
}
],
"id": 3,
"createdAt": "2024-03-23T22:52:47.788Z",
"updatedAt": "2024-03-23T22:52:47.788Z"
},
{
"competitionId": 1,
"category": "초등부",
"uniform": "GI",
"gender": "MALE",
"belt": "유색",
"weight": "-40",
"birthYearRangeStart": "2007",
"birthYearRangeEnd": "2008",
"status": "ACTIVE",
"priceSnapshots": [
{
"price": 40000,
"divisionId": 4,
"id": 4,
"createdAt": "2024-03-23T22:52:47.788Z"
}
],
"id": 4,
"createdAt": "2024-03-23T22:52:47.788Z",
"updatedAt": "2024-03-23T22:52:47.788Z"
},
{
"competitionId": 1,
"category": "초등부",
"uniform": "GI",
"gender": "FEMALE",
"belt": "화이트",
"weight": "-30",
"birthYearRangeStart": "2007",
"birthYearRangeEnd": "2008",
"status": "ACTIVE",
"priceSnapshots": [
{
"price": 40000,
"divisionId": 5,
"id": 5,
"createdAt": "2024-03-23T22:52:47.788Z"
}
],
"id": 5,
"createdAt": "2024-03-23T22:52:47.788Z",
"updatedAt": "2024-03-23T22:52:47.788Z"
},
{
"competitionId": 1,
"category": "초등부",
"uniform": "GI",
"gender": "FEMALE",
"belt": "화이트",
"weight": "-40",
"birthYearRangeStart": "2007",
"birthYearRangeEnd": "2008",
"status": "ACTIVE",
"priceSnapshots": [
{
"price": 40000,
"divisionId": 6,
"id": 6,
"createdAt": "2024-03-23T22:52:47.788Z"
}
],
"id": 6,
"createdAt": "2024-03-23T22:52:47.788Z",
"updatedAt": "2024-03-23T22:52:47.788Z"
},
{
"competitionId": 1,
"category": "초등부",
"uniform": "GI",
"gender": "FEMALE",
"belt": "유색",
"weight": "-30",
"birthYearRangeStart": "2007",
"birthYearRangeEnd": "2008",
"status": "ACTIVE",
"priceSnapshots": [
{
"price": 40000,
"divisionId": 7,
"id": 7,
"createdAt": "2024-03-23T22:52:47.788Z"
}
],
"id": 7,
"createdAt": "2024-03-23T22:52:47.788Z",
"updatedAt": "2024-03-23T22:52:47.788Z"
},
{
"competitionId": 1,
"category": "초등부",
"uniform": "GI",
"gender": "FEMALE",
"belt": "유색",
"weight": "-40",
"birthYearRangeStart": "2007",
"birthYearRangeEnd": "2008",
"status": "ACTIVE",
"priceSnapshots": [
{
"price": 40000,
"divisionId": 8,
"id": 8,
"createdAt": "2024-03-23T22:52:47.788Z"
}
],
"id": 8,
"createdAt": "2024-03-23T22:52:47.788Z",
"updatedAt": "2024-03-23T22:52:47.788Z"
}
],
"isSuccess": true,
"code": 200
}