การออกแบบฐานข้อมูล Mysql - การระบุรูปแบบที่ต้องการ

Question

ฉันกำลังพยายามคิดว่ารูปแบบใด (ถ้ามี) อาจอธิบายถึงปัญหาที่ฉันเผชิญเมื่อออกแบบฐานข้อมูลสำหรับระบบการจัดการสมาชิกของสโมสร ฉันต้องการความช่วยเหลือในการระบุรูปแบบ (ถ้ามี) หรือคำแนะนำบางประการเกี่ยวกับสิ่งที่ฉันต้องพิจารณาเพื่อให้เกิดความก้าวหน้า

สมาชิกเป็นหน่วยงานหลักโดยมีข้อมูลบางอย่างเช่นรายละเอียดการติดต่อ สมาชิกสามารถมีหน้าที่ส่วนกลางของสโมสรเช่นเป็นเหรัญญิกหรือผู้บังคับการเรือเป็นต้น

เพื่อที่จะจำลองลำดับชั้นระดับสูงสุดของสโมสรฉันจะมี ID จากสมาชิกตารางการแจงนับ "BoardMemberTypes" และตาราง "BoardMembersType" ที่เชื่อมโยงทั้งสอง

อย่างไรก็ตามคลับยังมีกลุ่มย่อยซึ่งแต่ละกลุ่มมีโครงสร้างลำดับชั้นของตนเอง

สมาชิกทุกคนเป็นส่วนหนึ่งของกลุ่มย่อยอย่างน้อยหนึ่งกลุ่มโดยมีกลุ่มย่อย Role "member" และประเภทการเป็นสมาชิกเช่น "active" หรือ "passive"

แต่ละโครงสร้างเหล่านี้มีบทบาทที่เหมือนกันและมีบทบาทเฉพาะบางอย่าง ตัวอย่างเช่น:

• กลุ่มย่อย A มีเก้าอี้รองประธานเหรัญญิกจากชุดที่เหมือนกันและ "กลุ่มย่อย A Technology Chair" จากรายการที่ใช้ได้กับกลุ่มย่อย 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

ตามทฤษฎีแล้วตอนนี้ฉันสามารถสร้างตาราง "MembersSubgroupMembershipFunctions" ด้วย MembersID, 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กล่าวคือเมื่อมีการมอบหมายให้ใครบางคนเข้าร่วมกลุ่มแล้วจะสามารถมอบหมายเก้าอี้ / รองประธาน / ผู้ช่วยพิเศษให้กับรองประธานจูเนียร์ / ฯลฯ แต่ละคนสามารถกำหนดให้ที่หนึ่งมากที่สุดGroupFunctionGroupMember

สำหรับแอคทีฟ / พาสซีฟ / ฯลฯ เป็นไปได้ทั้งหมดที่จะมีระดับหนึ่งสำหรับระดับ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 โปรดอ่านสิ่งนี้ ):