I'm trying to figure out which pattern (if any) might describe the problem I'm facing when designing the database for a club membership management system. I require help in identifying the pattern (if one exists) or some pointers of what considerations I need to make in order to make some headway.
Members are a main entity, with some information such as contact details on them. Members can have central functions at the club, ie be the treasurer or commodore etc.
In order to model the club's top level hierarchy, I would have the ID from Members, an enumeration table "BoardMemberTypes" and a "BoardMembersType" table linking the two.
However, the Club also has sub-groups, each with their own hierarchy structure.
Every Member is part of at least one of the Subgroups, with the subgroup Role "member", and the membership type such as "active" or "passive".
Each of these structures have a few identical and a few specific roles. As an example:
- Subgroup A has chair, vice chair, treasurer from the identical set, and "Subgroup A Technology Chair" from a list only applicable to Subgroup A
- Subgroup B has a chair, a treasurer, and a "Specific Subgroup B 1" and "Specific Subgroup B 2" role
- Subgoup C might have only a chair and a "Specific Subgroup C" role.
The Subgroups are the complicated bit:
Enumeration Table "Subgroup" (ID, Name) Enumeration Table "MembershipType" (ID, Name) Enumeration Table "MembershipFunction" (ID, Name)
First hurdle: When presenting the admin interface, I'd need to constrain MembershipFunction depending on the subgroup. I guess this could be achieved through a link table:
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 theory, I could now create a table "MembersSubgroupMembershipFunctions" with MembersID, SubgroupMembershipFunctionsId
At this point, I'm still lacking the abilty to store the MembershipType (active, passive) which only really applies to the "member" entry. The end result I need would be something like this maybe
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(?)
Any help, pointer or idea towards improving this design is greatly appreciated.