club membership data structure and relationships

[John O'Malvern]

I'm working on a database I've inherited which has successfully worked for a local group belonging to a national organisation of Advanced Drivers.
It is largely based on one table of member details, not attempting to record any detail of members' qualifications nor who held positions [and when] in the club's committee. Members ['Observers'] train new [unqualified] members to become Advanced Drivers, but ADs can get trained by experienced Observers to become Observers themselves. Any member may or may not be an AD for a car, or a bike, or an Observer for either or both, and may also hold any committee position.
Regarding the possible merging, I don't see a logical difference between a qualification held and a committee position held. Am I right?
In order to record details of who is training who, which further qualifications have been achieved, and who held [and holds] which committee positions I intend developing the structure like this:-
and I would like to hear what I've got right and what I've got wrong, and would you say 'yes' to the Q in the box bottom right?
Thanks in advance!

 

[John O'Malvern]

...and should i post this in:
theory-and-practice-of-database-design instead?

 

[GPGeorge]

...and should i post this in:
theory-and-practice-of-database-design instead?

Show us the actual Relationship diagram, not a contrived illustration, please.

Although you no doubt put a lot of effort into it, it actually makes it harder to understand, unfortunately.

The problem with this picture is that it is not how we are used to looking at the tables in a relational database applications. It makes the task of trying to figure out what you want to do more difficult. You can append your questions to that relationship diagram to call out issues you want more specific attention on specific elements.

But after looking at relationship diagrams for more than 20 years, for us, trying to suss out this picture would require more effort as well.

 

[GaP42]

As a national organisation - will you only ever have one committee (eg a governing committee, with possible subcommittee with oversight of training, say?)
Re the question re merging of the "red" tables - as essentially a combined lookup table of positions and qualification category - provided the Code/Descriptor attribute pair can be distinguished by allocation of a type that dictates the use, and they remain exclusive then not a problem (also suggest a flag of IsActive is needed at least).
Re merging of the "Blue" tables - that involves a level of abstraction that may be more difficult to control/manage. You need to see how well the attributes of each map out and correspond - I suspect this is a route not worth the effort given what appears in the list of items.
I assume you are just beginning to map out what you need to extend the existing inherited db. Further elaboration may be needed around membership - but this is what you have been given.

 

[John O'Malvern]

Show us the actual Relationship diagram, not a contrived illustration, please.

Although you no doubt put a lot of effort into it, it actually makes it harder to understand, unfortunately.

The problem with this picture is that it is not how we are used to looking at the tables in a relational database applications. It makes the task of trying to figure out what you want to do more difficult. You can append your questions to that relationship diagram to call out issues you want more specific attention on specific elements.

But after looking at relationship diagrams for more than 20 years, for us, trying to suss out this picture would require more effort as well.

Thanks for your reply. I'm surprised at your reaction to my diagram, as it is just as I was taught in higher education by people who had come from deploying RDMSs in a national industry. 'Do your data analysis, and figure out your relationships diagramatically before attempting anything more' they would have said.
However, it seems I'm out on a limb here, so hopefully this suits you better:
Going back to my original question, I think there is little benefit to merging tables, and I won't bother.
I think I have all the necessary fields [ignoring tblContacts] but any advice or comments on whether it is set up right will be most welcome.

 

[John O'Malvern]

As a national organisation - will you only ever have one committee (eg a governing committee, with possible subcommittee with oversight of training, say?)
Re the question re merging of the "red" tables - as essentially a combined lookup table of positions and qualification category - provided the Code/Descriptor attribute pair can be distinguished by allocation of a type that dictates the use, and they remain exclusive then not a problem (also suggest a flag of IsActive is needed at least).
Re merging of the "Blue" tables - that involves a level of abstraction that may be more difficult to control/manage. You need to see how well the attributes of each map out and correspond - I suspect this is a route not worth the effort given what appears in the list of items.
I assume you are just beginning to map out what you need to extend the existing inherited db. Further elaboration may be needed around membership - but this is what you have been given.

NB I'm only dealing with a local group affiliated to the national organisation.
Given that each post and each qualification has a start date and an end date, what will the IsActive flag add?
I think the membership side of things is all in order - obviously you are not seeing all the fields, but is there something relating to the tables above that requires 'further elaboration'?

 

[John O'Malvern]

Sorry, George, I forgot to thank you for moving my question to here.
Thanks.

 

[GaP42]

NB I'm only dealing with a local group affiliated to the national organisation.
Given that each post and each qualification has a start date and an end date, what will the IsActive flag add?
I think the membership side of things is all in order - obviously you are not seeing all the fields, but is there something relating to the tables above that requires 'further elaboration'?

Re local group / national affiliation - will other local groups (later) find it useful? Would it be better now to add a simple Committee name entity/attribute to ease any later convergence.
The isActive flag is on the lookup attributes for Qualification Category and Committee Position - not the qualification tenure or committee tenure ... it can then be used to temporarily suspend or "archive" the use of such values when no longer in use.

In the OP you used the members concept as opposed to Contact - are all Contacts members. Can non-members partcipate in obtaining the qualification. If only members do you need them to be, say, current financial members?

 

[John O'Malvern]

Re local group / national affiliation - will other local groups (later) find it useful? Would it be better now to add a simple Committee name entity/attribute to ease any later convergence.
The isActive flag is on the lookup attributes for Qualification Category and Committee Position - not the qualification tenure or committee tenure ... it can then be used to temporarily suspend or "archive" the use of such values when no longer in use.

In the OP you used the members concept as opposed to Contact - are all Contacts members. Can non-members partcipate in obtaining the qualification. If only members do you need them to be, say, current financial members?

Local / national. -- There are many groups already in existance, I have wondered what they do / how they do this. Yes it would make sense, be potentially kind and thoughtful to make it available, but whether they want to change or can is another matter! TBH I really don't want to take that on.

isActive -- ok, i see, might be useful, thanks.

Contacts get classified into one of several ContactTypes:- [a ContactTypes field in the contacts table]
Not all Contacts are Members but all members are paying. Only Members can participate [train or observe], or be Committee members.
Someone just joined and thus under training to be an Advanced Driver, is an Associate until they qualify.
There are Friends, who pay but can't participate in any significant way.
There are also Honorary, Transferred and Withdrawn ContactTypes.
There are also several Observer ContactTypes, and if someone has the main Advanced Driver qualification, but is training to be an Observer, they are Member/Associate [!]
These ContactTypes are held in a separate table, with a One-to-Many relationship, but no enforced referential integrity.

Category refers to the type of vehicle for which members are qualified [car, bike, commercial etc]
The Categories are also held in a separate table, with a One-to-Many relationship, but no enforced referential integrity. This table simply lists all possible combinations of category, from 'Bike' to 'Car+Bike+MiniBus+Comm'.
I haven't spent time thinking about these two tables and their 'correctness', but I can see there is almost certainly work to do.
Time for a fresh cup of coffee!

 

[GaP42]

The Categories are also held in a separate table, with a One-to-Many relationship, but no enforced referential integrity. This table simply lists all possible combinations of category, from 'Bike' to 'Car+Bike+MiniBus+Comm'.

That does look a bit too slack: as your db will manage the qualification of contact/members then you will need to assign that qualification when they do become qualified - which then presumably means their "category" can be determined from a query which will show all the current qualifications they have. That then would appear to obviate the need for Category, if I am understanding correctly how it is used.

 

[John O'Malvern]

Yes! , and you've made me think about it and realise the Qualification_Category is not just for Observer level, it works for all qualifications.
All Members are qualified, and all qualifications are held by Members - or those other ContactTypes who [historically] held qualifications (e.g. Honorary, transferred, deceased, etc).
I'm stuck now wondering how wrong [if at all] the relationship diagram is above [Access one] [once I remove 'observer' from the names of the two Tables.

 

[GaP42]

Yes! , and you've made me think about it and realise the Qualification_Category is not just for Observer level, it works for all qualifications.
All Members are qualified, and all qualifications are held by Members - or those other ContactTypes who [historically] held qualifications (e.g. Honorary, transferred, deceased, etc).
I'm stuck now wondering how wrong [if at all] the relationship diagram is above [Access one] [once I remove 'observer' from the names of the two Tables.

So you need to have attributes like a qualification type and a qualification status (honarary, accredited, probationary ...): category may do it for qualification type - but then you may need a sub-level for some types? eg Advanced Driver / Master?, and Advanced Driver / Novice? just speculating
I would presume "deceased" is not an appropriate qualifier for a qualification. "Transferred" - hmm - what is transferred - the qualification or the person to another part of the national org? If the latter, then similar to deceased.