Mysql数据库设计-识别所需的模式

Question

我试图找出哪种模式(如果有的话)可以描述我在为俱乐部成员管理系统设计数据库时所面临的问题。我需要帮助识别模式(如果存在的话)，或者指示我需要考虑什么才能取得一些进展。

成员是一个主要实体，有一些信息，例如联系方式。会员可在俱乐部担任中心职务(司库或准将等)。

为了模拟俱乐部的高层结构，我需要成员的ID、一个枚举表"BoardMemberTypes“和一个"BoardMembersType”表将两者连接起来。

然而，俱乐部也有分组，每个小组都有自己的等级结构.

每个成员都是至少一个子组的一部分，其子组角色为“成员”，成员类型为“主动”或“被动”。

这些结构中的每一个都有几个相同的和几个特定的角色。例如：

• 分组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创建一个表“SubgroupMembershipFunctionsId”

在这一点上，我仍然缺乏存储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(?)

任何帮助，指针或想法，以改进这个设计是非常感谢的。

Answer 1

我不知道是否有一个设计模式超出了“规范正确”，但我不认为你离一个可行的解决方案太远。

根据您的描述，我不认为需要一个Subgroup实体--我们可以只使用Groups，并将其中一个表示为“董事会”。

对于具体的GroupFunctions，我们将把这些看作是GroupMembers的特例，即一旦某人被指派到一个组，他们可以被指定为主席/副主席/ Jr副主席/其他的特别助理。每个GroupFunction最多可以被分配给一个GroupMember。

至于主动/被动/等等，Member完全可能有一个级别，每个Group有不同级别，但是如果“被动”成员不能是组的“主动”成员，则可能需要一些事务逻辑。或者，您可以根据每个Member的成员组来派生它们的D9。

我将把数据类型和完整的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，请阅读这)：