MySQL 데이터베이스 디자인-필요한 패턴 식별

Aug 17 2020

클럽 회원 관리 시스템을위한 데이터베이스를 설계 할 때 어떤 패턴 (있는 경우)이 제가 직면 한 문제를 설명 할 수 있는지 알아 보려고합니다. 패턴 (존재하는 경우)을 식별하는 데 도움이 필요하거나 진행을 위해 고려해야 할 사항에 대한 몇 가지 지침이 필요합니다.

구성원은 연락처 세부 정보와 같은 일부 정보가 포함 된 주요 엔티티입니다. 회원들은 클럽에서 중요한 역할을 할 수 있습니다. 즉, 재무 나 상품 등이 될 수 있습니다.

클럽의 최상위 계층 구조를 모델링하기 위해 회원의 ID, "BoardMemberTypes"열거 테이블 및 둘을 연결하는 "BoardMembersType"테이블이 있습니다.

그러나 클럽에는 각각 고유 한 계층 구조가있는 하위 그룹도 있습니다.

모든 구성원은 하위 그룹 역할 "member"와 "active"또는 "passive"와 같은 멤버십 유형을 가진 하나 이상의 하위 그룹에 속합니다.

이러한 각 구조에는 몇 가지 동일한 역할과 몇 가지 특정 역할이 있습니다. 예로서:

  • 하위 그룹 A에는 동일한 세트의 의장, 부의장, 재무 및 하위 그룹 A에만 적용되는 목록의 "하위 그룹 A 기술 의장"이 있습니다.
  • 하위 그룹 B에는 의장, 재무, "특정 하위 그룹 B 1"및 "특정 하위 그룹 B 2"역할이 있습니다.
  • Subgoup C에는 의장과 "특정 하위 그룹 C"역할 만있을 수 있습니다.

하위 그룹은 복잡한 부분입니다.

Enumeration Table "Subgroup" (ID, Name)
Enumeration Table "MembershipType" (ID, Name)
Enumeration Table "MembershipFunction" (ID, Name)

첫 번째 장애물 : 관리자 인터페이스를 표시 할 때 하위 그룹에 따라 MembershipFunction을 제한해야합니다. 나는 이것이 링크 테이블을 통해 달성 될 수 있다고 생각합니다.

 SubgroupMembershipFunctions (Id, SubgroupId, MembershipFunctionId)
 e.g. (Name output after applying joins obviously)

1    Subgroup A    Member
2    Subgroup A    Chairman
3    Subgroup A    Subgroup A Technology Chair
4    Subgroup B    Member
5    Subgroup B    Chairman
6    Subgroup B    Specific Subgroup B 1

이론적으로 이제 MembersID, SubgroupMembershipFunctionsId를 사용하여 "MembersSubgroupMembershipFunctions"테이블을 만들 수 있습니다.

이 시점에서는 "멤버"항목에만 실제로 적용되는 MembershipType (액티브, 패시브)을 저장할 능력이 여전히 부족합니다. 내가 필요한 최종 결과는 아마도 다음과 같을 것입니다.

User          Subgroup    Type        Status
Justin Case   A           member      active
Justin Case   A           chairman    null(?)
Justin Case   B           member      passive
Justin Case   B           B 1         null(?)
Joe Bloggs    A           member      active
Jane Doe      B           member      active
Jane Doe      C           member      passive
Jane Doe      C           vicechair   null(?)

이 디자인을 개선하기위한 모든 도움, 조언 또는 아이디어는 대단히 감사합니다.

답변

1 bbaird Aug 17 2020 at 22:54

"올바르게 정규화"하는 것 이상의 디자인 패턴이 있는지는 모르겠지만 실행 가능한 솔루션에서 너무 멀리 떨어져 있다고 생각하지 않습니다.

귀하의 설명을 감안할 때 Subgroup엔티티 가 필요하다고 생각하지 않습니다. Groups그 중 하나를 "The Board"로 표시 할 수 있습니다 .

구체적으로 GroupFunctions, 우리는 그것들을 특별한 경우로 간주 할 것입니다 GroupMembers. 즉, 누군가가 그룹에 배정되면 그들은 주니어 부의장 등의 의장 / 부회장 / 특별 조수에게 배정 될 수 있습니다. 각각 GroupFunction은 최대 하나에 할당 할 수 있습니다 GroupMember.

액티브 / 패시브 / 등의 경우,에 대해 하나의 수준을 가질 수 Member있고 각각에 대해 다른 수준을 Group가질 수 있지만, "패시브"구성원이 그룹의 "활성"구성원이 될 수없는 경우 일부 트랜잭션 논리가 필요할 수 있습니다. 또는 구성원 인 그룹을 기반으로 MembershipType각각에 대해 Member를 파생 할 수 있습니다 .

데이터 유형과 전체 DDL (감사 테이블 포함)은 그대로 두겠습니다.하지만 다음과 같이 작동합니다.

CREATE TABLE MembershipType
(
  MembershipTypeCd
 ,Name
 ,CONSTRAINT PK_MembershipType PRIMARY KEY (MembershipTypeCd)
 ,CONSTRAINT AK_MembershipType UNIQUE (Name)

);

CREATE TABLE Member
(
  MemberId
 ,MembershipTypeCd
 ,CONSTRAINT FK_Member_Has_MembershipType FOREIGN KEY (MembershipTypeCd) REFERENCES MembershipType (MembershipTypeCd)
 ,CONSTRAINT PK_Member PRIMARY KEY (MemberId)
);

CREATE TABLE MemberFunction
(
  FunctionShortName
 ,Name
 ,CONSTRAINT PK_MemberFunction PRIMARY KEY (FunctionShortName)
 ,CONSTRAINT AK_MemberFunction UNIQUE (Name)
);

CREATE TABLE Group /* A reserved keyword in most DBMS - may make sense to rename */
(
  GroupId
 ,Name
 ,CONSTRAINT PK_Group PRIMARY KEY (GroupId)
 ,CONSTRAINT AK_Group UNIQUE (Name)
);


CREATE TABLE GroupMember
(
  GroupId
 ,MemberId
 ,GroupMembershipTypeCd
 ,CONSTRAINT FK_GroupMember_Member_Of_Group FOREIGN KEY (GroupId) REFERENCES Group (GroupId)
 ,CONSTRAINT FK_GroupMember_Is_Member FOREIGN KEY (MemberId) REFERENCES Member (MemberId)
 ,CONSTRAINT FK_GroupMember_Has_MembershipType FOREIGN KEY (GroupMembershipTypeCd) REFERENCES MembershipType (MembershipTypeCd)
 ,CONSTRAINT PK_GroupMember PRIMARY KEY (GroupId, MemberId)
 ,CONSTRAINT AK_GroupMember UNIQUE (MemberId, GroupId)
)
;

CREATE TABLE GroupFunction
(
  GroupId
 ,FunctionShortName
 ,CONSTRAINT FK_GroupFunction_Available_For_Group FOREIGN KEY (GroupId) REFERENCES Group (GroupId)
 ,CONSTRAINT FK_GroupFunction_Is_MemberFunction FOREIGN KEY (FunctionShortName) REFERENCES MemberFunction (FunctionShortName)
 ,CONSTRAINT PK_GroupFunction PRIMARY KEY (GroupId, FunctionShortName)
);

CREATE TABLE GroupFunctionAssignment
(
  GroupId
 ,FunctionShortName
 ,MemberId
 ,CONSTRAINT FK_GroupFunctionAssignment_Assigned_To_GroupMember FOREIGN KEY (GroupId, MemberId) REFERENCES GroupMember (GroupId, MemberId)
 ,CONSTRAINT FK_GroupFunctionAssignment_Assigment_Of_GroupFunction FOREIGN KEY (GroupId, FunctionShortName) REFERENCES GroupFunction (GroupId, FunctionShortName)
 ,CONSTRAINT PK_GroupFunctionAssignment PRIMARY KEY (GroupId, FunctionShortName)
);

사진 (당신이 IDEF1X에 익숙하지 않은 경우, 읽고 이해하기 일반적으로 쉽게 이 ) :