Mysqlデータベース設計-必要なパターンの特定
クラブ会員管理システムのデータベースを設計するときに直面している問題を説明しているパターン（ある場合）を特定しようとしています。パターン（存在する場合）または前進するために行う必要のある考慮事項のいくつかの指針を特定するのに助けが必要です。
メンバーは主要なエンティティであり、連絡先の詳細などの情報が含まれています。メンバーは、クラブで中心的な機能を持つことができます。つまり、会計や提督などです。
クラブの最上位階層をモデル化するために、メンバーからのID、列挙型テーブル「BoardMemberTypes」および2つをリンクする「BoardMembersType」テーブルを取得します。
ただし、クラブにはサブグループもあり、それぞれに独自の階層構造があります。
すべてのメンバーは、少なくとも1つのサブグループの一部であり、サブグループの役割は「メンバー」であり、メンバーシップのタイプは「アクティブ」や「パッシブ」などです。
これらの構造のそれぞれには、いくつかの同一の役割といくつかの特定の役割があります。例として：
- サブグループAには、同じセットの議長、副議長、財務担当者、およびサブグループAにのみ適用されるリストの「サブグループAテクノロジー議長」がいます。
- サブグループBには、議長、会計、および「特定のサブグループB1」と「特定のサブグループB2」の役割があります。
- サブグループ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(?)
この設計を改善するための助け、指針、またはアイデアは大歓迎です。
回答
「適切に正規化する」以外のデザインパターンがあるかどうかはわかりませんが、実行可能なソリューションからそれほど離れているとは思いません。
あなたの説明を考えると、私はSubgroupエンティティの必要性を感じていません-私たちGroupsはそれらの1つを「取締役会」として持つことができます。
具体的にはGroupFunctions、これらは特別な場合と見なされますGroupMembers。つまり、誰かがグループに割り当てられると、議長/副議長/ジュニア副議長の特別アシスタントなどを割り当てることができます。それぞれGroupFunctionを最大で1つに割り当てることができますGroupMember。
アクティブ/パッシブなどに関してはMember、レベルごとGroupに1つのレベルを設定し、レベルごとに異なるレベルを設定することは完全に可能ですが、「パッシブ」メンバーをグループの「アクティブ」メンバーにできない場合は、トランザクションロジックが必要になる場合があります。または、メンバーであるグループに基づいて、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に慣れていない場合は、これを読んでください）：
