Conception de base de données Mysql - identification des modèles requis
J'essaie de déterminer quel modèle (le cas échéant) pourrait décrire le problème auquel je suis confronté lors de la conception de la base de données pour un système de gestion des effectifs de club. J'ai besoin d'aide pour identifier le modèle (s'il en existe un) ou de quelques indications sur les considérations que je dois faire pour progresser.
Les membres sont une entité principale, avec certaines informations telles que les coordonnées sur eux. Les membres peuvent avoir des fonctions centrales au club, c'est-à-dire être le trésorier ou le commodore, etc.
Afin de modéliser la hiérarchie de niveau supérieur du club, j'aurais l'ID des membres, une table d'énumération "BoardMemberTypes" et une table "BoardMembersType" reliant les deux.
Cependant, le Club a également des sous-groupes, chacun avec sa propre structure hiérarchique.
Chaque membre fait partie d'au moins un des sous-groupes, avec le rôle de sous-groupe «membre» et le type d'appartenance tel que «actif» ou «passif».
Chacune de ces structures a quelques rôles identiques et quelques rôles spécifiques. Par exemple:
- Le sous-groupe A a un président, un vice-président, un trésorier de l'ensemble identique et un "président de technologie du sous-groupe A" à partir d'une liste applicable uniquement au sous-groupe A
- Le sous-groupe B a un président, un trésorier et un rôle de «sous-groupe spécifique B 1» et de «sous-groupe spécifique B 2»
- Le sous-groupe C pourrait n'avoir qu'un président et un rôle de «sous-groupe spécifique C».
Les sous-groupes sont le peu compliqué:
Enumeration Table "Subgroup" (ID, Name)
Enumeration Table "MembershipType" (ID, Name)
Enumeration Table "MembershipFunction" (ID, Name)
Premier obstacle: lors de la présentation de l'interface d'administration, je devrais contraindre MembershipFunction en fonction du sous-groupe. Je suppose que cela pourrait être réalisé grâce à une table de liens:
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
En théorie, je pourrais maintenant créer une table "MembersSubgroupMembershipFunctions" avec MembersID, SubgroupMembershipFunctionsId
À ce stade, je n'ai toujours pas la capacité de stocker le MembershipType (actif, passif) qui ne s'applique vraiment qu'à l'entrée "membre". Le résultat final dont j'ai besoin serait peut-être quelque chose comme ça
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(?)
Toute aide, pointeur ou idée pour améliorer cette conception est grandement appréciée.
Réponses
Je ne sais pas s'il existe un modèle de conception au-delà de "normaliser correctement", mais je ne pense pas que vous soyez trop loin d'une solution viable.
Compte tenu de votre description, je ne vois pas la nécessité d'une Subgroup
entité - nous pouvons simplement en avoir Groups
et désigner l'une de ces entités comme "Le Conseil".
Pour des raisons spécifiques GroupFunctions
, nous considérerons cela comme un cas particulier GroupMembers
, c'est-à-dire qu'une fois qu'une personne est affectée à un groupe, elle peut être nommée Président / Vice-président / Assistant spécial du Vice-président junior / etc. Chacun GroupFunction
peut être attribué à au plus un GroupMember
.
Comme pour actif / passif / etc., Il est tout à fait possible d'avoir un niveau pour les Member
et différents niveaux pour chacun Group
, mais vous pourriez avoir besoin d'une logique de transaction si un membre "passif" ne peut pas être un membre "actif" d'un groupe. Vous pouvez également dériver le MembershipType
pour chacun en Member
fonction des groupes dont ils sont membres.
Je vous laisse les types de données et le DDL complet (y compris les tables d'audit), mais cela devrait fonctionner:
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)
);
Les images sont généralement plus faciles à comprendre (si vous n'êtes pas familier avec IDEF1X, lisez ceci ):