Mysql Database Design - identificazione dei modelli richiesti

Aug 17 2020

Sto cercando di capire quale modello (se presente) potrebbe descrivere il problema che sto affrontando durante la progettazione del database per un sistema di gestione dell'effettivo di club. Ho bisogno di aiuto per identificare il pattern (se esiste) o alcuni indicatori di quali considerazioni devo fare per fare qualche progresso.

I membri sono un'entità principale, con alcune informazioni come i dettagli di contatto su di loro. I membri possono avere funzioni centrali nel club, ad esempio essere il tesoriere o il commodoro, ecc.

Per modellare la gerarchia di livello superiore del club, avrei l'ID dei membri, una tabella di enumerazione "BoardMemberTypes" e una tabella "BoardMembersType" che collegasse i due.

Tuttavia, il Club dispone anche di sottogruppi, ciascuno con la propria struttura gerarchica.

Ogni membro fa parte di almeno uno dei sottogruppi, con il ruolo del sottogruppo "membro" e il tipo di appartenenza come "attivo" o "passivo".

Ciascuna di queste strutture ha alcuni ruoli identici e alcuni specifici. Come esempio:

  • Il sottogruppo A ha presidente, vicepresidente, tesoriere dello stesso gruppo e "presidente tecnologico del sottogruppo A" da un elenco applicabile solo al sottogruppo A
  • Il sottogruppo B ha un presidente, un tesoriere e un ruolo di "sottogruppo specifico B 1" e "sottogruppo specifico B 2"
  • Il sottogruppo C potrebbe avere solo un presidente e un ruolo di "sottogruppo specifico C".

I sottogruppi sono la parte complicata:

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

Primo ostacolo: quando si presenta l'interfaccia di amministrazione, è necessario limitare MembershipFunction a seconda del sottogruppo. Immagino che questo possa essere ottenuto tramite una tabella di collegamento:

 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

In teoria, ora potrei creare una tabella "MembersSubgroupMembershipFunctions" con MembersID, SubgroupMembershipFunctionsId

A questo punto, mi manca ancora la possibilità di memorizzare il MembershipType (attivo, passivo) che si applica realmente solo alla voce "membro". Il risultato finale di cui ho bisogno sarebbe forse qualcosa del genere

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(?)

Qualsiasi aiuto, suggerimento o idea per migliorare questo design è molto apprezzato.

Risposte

1 bbaird Aug 17 2020 at 22:54

Non so se esista uno schema di progettazione oltre a "normalizzare correttamente", ma non credo che tu sia troppo lontano da una soluzione praticabile.

Data la tua descrizione non vedo la necessità di Subgroupun'entità - possiamo semplicemente avere Groupse denotare una di queste come "The Board".

Per lo specifico GroupFunctions, li considereremo un caso speciale per GroupMembers, cioè una volta che qualcuno viene assegnato a un gruppo, può essere assegnato come presidente / vicepresidente / assistente speciale al vicepresidente junior / ecc. Ciascuno GroupFunctionpuò essere assegnato al massimo a uno GroupMember.

Per quanto riguarda attivo / passivo / ecc., È del tutto possibile avere un livello per Membere diversi livelli per ciascuno Group, ma potrebbe essere necessaria una logica di transazione se un membro "passivo" non può essere un membro "attivo" di un gruppo. In alternativa, puoi derivare il MembershipTypeper ciascuno in Memberbase ai gruppi di cui sono membri.

Lascio a te i tipi di dati e il DDL completo (comprese le tabelle di controllo), ma dovrebbe funzionare:

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)
);

Le immagini sono generalmente più facili da capire (se non hai familiarità con IDEF1X, leggi questo ):