Mysql Database Design - identificando os padrões necessários

Aug 17 2020

Estou tentando descobrir qual padrão (se houver) pode descrever o problema que estou enfrentando ao projetar o banco de dados para um sistema de gerenciamento de sócios de clube. Preciso de ajuda para identificar o padrão (se houver) ou algumas dicas de quais considerações preciso fazer para fazer algum progresso.

Os membros são uma entidade principal, com algumas informações, como detalhes de contato sobre eles. Os membros podem ter funções centrais no clube, ou seja, ser tesoureiro ou comodoro, etc.

Para modelar a hierarquia de nível superior do clube, eu teria o ID dos membros, uma tabela de enumeração "BoardMemberTypes" e uma tabela "BoardMembersType" ligando os dois.

No entanto, o Clube também possui subgrupos, cada um com sua própria estrutura hierárquica.

Cada Membro faz parte de pelo menos um dos Subgrupos, com o subgrupo Função "membro" e o tipo de associação como "ativo" ou "passivo".

Cada uma dessas estruturas tem algumas funções idênticas e algumas funções específicas. Como um exemplo:

  • O subgrupo A tem presidente, vice-presidente, tesoureiro de conjunto idêntico e "Diretor de tecnologia do subgrupo A" de uma lista aplicável apenas ao subgrupo A
  • O subgrupo B tem um presidente, um tesoureiro e uma função de "Subgrupo específico B 1" e "Subgrupo específico B 2"
  • O subgupo C pode ter apenas um presidente e uma função de "Subgrupo C específico".

Os subgrupos são a parte complicada:

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

Primeiro obstáculo: ao apresentar a interface de administração, eu precisaria restringir MembershipFunction dependendo do subgrupo. Acho que isso pode ser feito por meio de uma tabela de links:

 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

Em teoria, agora eu poderia criar uma tabela "MembersSubgroupMembershipFunctions" com MembersID, SubgroupMembershipFunctionsId

Neste ponto, ainda não tenho a capacidade de armazenar o MembershipType (ativo, passivo) que só se aplica realmente à entrada "membro". O resultado final que preciso seria algo assim, talvez

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

Qualquer ajuda, indicação ou ideia para melhorar este design é muito apreciada.

Respostas

1 bbaird Aug 17 2020 at 22:54

Não sei se existe um padrão de design além de "normalizar corretamente", mas não acho que você esteja muito longe de uma solução viável.

Dada a sua descrição, não vejo necessidade de uma Subgroupentidade - podemos simplesmente ter Groupse denotar uma delas como "O Conselho".

Especificamente GroupFunctions, consideraremos esse um caso especial GroupMembers, ou seja, uma vez que alguém é designado para um grupo, ele pode ser designado Presidente / Vice-Presidente / Assistente Especial do Vice-Presidente Júnior / etc. Cada um GroupFunctionpode ser atribuído a no máximo um GroupMember.

Quanto a ativo / passivo / etc., É inteiramente possível ter um nível para o Membere níveis diferentes para cada um Group, mas você pode precisar de alguma lógica de transação se um membro "passivo" não puder ser um membro "ativo" de um grupo. Como alternativa, você pode derivar o MembershipTypefor each com Memberbase nos grupos dos quais eles são membros.

Vou deixar os tipos de dados e DDL completo para você (incluindo tabelas de auditoria), mas isso deve funcionar:

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

As imagens geralmente são mais fáceis de entender (se você não estiver familiarizado com IDEF1X, leia isto ):