Diseño de base de datos Mysql: identificación de patrones requeridos
Estoy tratando de averiguar qué patrón (si lo hay) podría describir el problema al que me enfrento al diseñar la base de datos para un sistema de gestión de socios de clubes. Necesito ayuda para identificar el patrón (si existe) o algunos indicadores de las consideraciones que debo hacer para avanzar.
Los miembros son una entidad principal, con cierta información, como los datos de contacto, sobre ellos. Los miembros pueden tener funciones centrales en el club, es decir, ser el tesorero o comodoro, etc.
Para modelar la jerarquía de nivel superior del club, tendría el ID de Members, una tabla de enumeración "BoardMemberTypes" y una tabla "BoardMembersType" que vincule los dos.
Sin embargo, el Club también tiene subgrupos, cada uno con su propia estructura jerárquica.
Cada miembro es parte de al menos uno de los subgrupos, con el rol del subgrupo "miembro" y el tipo de membresía como "activo" o "pasivo".
Cada una de estas estructuras tiene algunos roles idénticos y algunos específicos. Como ejemplo:
- El subgrupo A tiene un presidente, un vicepresidente, un tesorero del mismo grupo y un "presidente de tecnología del subgrupo A" de una lista que solo se aplica al subgrupo A
- El subgrupo B tiene un presidente, un tesorero y una función de "Subgrupo específico B 1" y "Subgrupo específico B 2"
- El subgrupo C podría tener solo un presidente y una función de "Subgrupo C específico".
Los subgrupos son la parte complicada:
Enumeration Table "Subgroup" (ID, Name)
Enumeration Table "MembershipType" (ID, Name)
Enumeration Table "MembershipFunction" (ID, Name)
Primer obstáculo: al presentar la interfaz de administración, necesitaría restringir MembershipFunction según el subgrupo. Supongo que esto se podría lograr a través de una tabla de enlaces:
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 teoría, ahora podría crear una tabla "MembersSubgroupMembershipFunctions" con MembersID, SubgroupMembershipFunctionsId
En este punto, todavía me falta la capacidad de almacenar el MembershipType (activo, pasivo) que solo se aplica realmente a la entrada "miembro". El resultado final que necesito sería algo como esto, tal vez
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(?)
Cualquier ayuda, consejo o idea para mejorar este diseño es muy apreciada.
Respuestas
No sé si hay un patrón de diseño más allá de "normalizar correctamente", pero no creo que esté demasiado lejos de una solución viable.
Dada su descripción, no veo la necesidad de una Subgroupentidad; solo podemos tener Groupsy denotar una de esas como "La Junta".
Específicamente GroupFunctions, los consideraremos como un caso especial GroupMembers, es decir, una vez que alguien es asignado a un grupo, se le puede asignar un presidente / vicepresidente / asistente especial del vicepresidente junior, etc. Cada uno GroupFunctionpuede asignarse como máximo a uno GroupMember.
En cuanto a activo / pasivo / etc., es completamente posible tener un nivel para el Membery diferentes niveles para cada uno Group, pero es posible que necesite alguna lógica de transacción si un miembro "pasivo" no puede ser un miembro "activo" de un grupo. Alternativamente, puede derivar el MembershipTypepara cada uno en Memberfunción de los grupos de los que son miembros.
Le dejaré los tipos de datos y el DDL completo (incluidas las tablas de auditoría), pero esto debería 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)
);
Las imágenes suelen ser más fáciles de entender (si no está familiarizado con IDEF1X, lea esto ):
