tables and relationships for members of a club database (1 Viewer)

[John O'Malvern]

SORRY I didn't mean to post this as a sticky! Admin, please move, as I don't seem to be aable to delete it.
(Embarrased!)
I expect to take over a datavbse of 'club' members, actually a local group of a national association.
This local 'club' has its own committee, with a chairperson, treasurer, and secretary etc. Members come and go from htese 'officials' posts.
Many members can be a treasurer [eg], at different times. And one member can hold many different official-posts at different times.
I think to record this correctly I need, tblMembers many-to-one tblMember-Official one-to-many tblOfficialPost, with fields for startdate and enddate in the tblMemberOfficial.
Am I right?
is it of any practical use if I bother with this???

 

[Pat Hartman]

If you want to retain history of who held what post, when - you have a many-many relationship. That requires three tables
tblMember
MemberID (autonumber PK)
FirstName
......
tblPost
PostID (autonumber PK)
PostName
.....
tblMemberPost
MemberPostID (autonumber PK)
MemberID (FK to tblMember)
PostID (FK to tblPost)
StartDT
EndDT

Usually, even though the EndDT may not be known in advance, it is generally easier to use a default date, far in the future. This makes criteria easier to formulate since you get to use the Between operator.

Most junction tables (the middle table of a m-m relationship) have only the two FK's which in combination define uniqueness. When you are keeping history, that is insufficient. For this junction table, you need an different unique index. In this case it would be on PostID and StartDT rather than on PostID and MemberID. You also need logic to ensure that there is no overlap of the date ranges as records are appended to this table.

The final consideration is - do you want to merge current post information with history. If you do, then the above schema will work. If you want to handle them separately, then you would add MemberID and StartDT to tblPost. When the member leaves the post, a record is created in the junction table to record the four fields as history. This will probably make the date overlap logic simpler. It also does not violate normalization rules since the two tables record different information. tblPost records every post and the member that is currently occupying that position. the member and PostDT may logically be empty if the position is empty. tblMemberPost records who held which post in the past.

 

[John O'Malvern]

If you want to retain history of who held what post, when - you have a many-many relationship. That requires three tables
tblMember
MemberID (autonumber PK)
FirstName
......
tblPost
PostID (autonumber PK)
PostName
.....
tblMemberPost
MemberPostID (autonumber PK)
MemberID (FK to tblMember)
PostID (FK to tblPost)
StartDT
EndDT

Usually, even though the EndDT may not be known in advance, it is generally easier to use a default date, far in the future. This makes criteria easier to formulate since you get to use the Between operator.

Most junction tables (the middle table of a m-m relationship) have only the two FK's which in combination define uniqueness. When you are keeping history, that is insufficient. For this junction table, you need an different unique index. In this case it would be on PostID and StartDT rather than on PostID and MemberID. You also need logic to ensure that there is no overlap of the date ranges as records are appended to this table.

The final consideration is - do you want to merge current post information with history. If you do, then the above schema will work. If you want to handle them separately, then you would add MemberID and StartDT to tblPost. When the member leaves the post, a record is created in the junction table to record the four fields as history. This will probably make the date overlap logic simpler. It also does not violate normalization rules since the two tables record different information. tblPost records every post and the member that is currently occupying that position. the member and PostDT may logically be empty if the position is empty. tblMemberPost records who held which post in the past.

Many thanks. I appreciate the extra points.

 

[GaP42]

If your database is to support multiple clubs simultaneously then you would also introduce a ClubId for members and Posts, and a club table.
Practical uses: that is dependent upon your club needs. Can be extended to manage attendance records at monthly management meetings (a schedule), that can be also used in reporting to the AGM. I have also associated the documents to the meetings: agenda, minutes, tabled papers for reference (and posting prior to meeting). And if needed you could use it to support subcommittees (requires a setting up a table for these, including the Club Management Committee, with a "reportsTo" self referential foreign key. Committees have CommitteeMembers (occupying post/roles).

 

[gemma-the-husky]

@John O'Malvern
Hi John. Only you will know if this is worthwhile, practically, but it doesn't actually need much development to include.

It depends if for instance you want to email only committee members. In that case you can very easily get the database to select just current officials to be included in the email distribution, rather than you having to do this manually. You could use the offices table to include a hierarchy of officials for that purpose to further restrict selections, so you could contact just selected officials rather than all officials.

If you keep a history of past positions you can include members who have previously been in office as well as current officials.

None of this is hard once you get the correct structure. It's just a matter of getting the tables organised properly to start with.

 

[John O'Malvern]

If your database is to support multiple clubs simultaneously then you would also introduce a ClubId for members and Posts, and a club table.
Practical uses: that is dependent upon your club needs. Can be extended to manage attendance records at monthly management meetings (a schedule), that can be also used in reporting to the AGM. I have also associated the documents to the meetings: agenda, minutes, tabled papers for reference (and posting prior to meeting). And if needed you could use it to support subcommittees (requires a setting up a table for these, including the Club Management Committee, with a "reportsTo" self referential foreign key. Committees have CommitteeMembers (occupying post/roles).

Interesting points but unneccessary in this case, thanks.

 

[John O'Malvern]

@John O'Malvern
Hi John. Only you will know if this is worthwhile, practically, but it doesn't actually need much development to include.

It depends if for instance you want to email only committee members. In that case you can very easily get the database to select just current officials to be included in the email distribution, rather than you having to do this manually. You could use the offices table to include a hierarchy of officials for that purpose to further restrict selections, so you could contact just selected officials rather than all officials.

If you keep a history of past positions you can include members who have previously been in office as well as current officials.

None of this is hard once you get the correct structure. It's just a matter of getting the tables organised properly to start with.

Thanks, I wanted confirmation that my proposed relationships were good, which they are.
Now knowing that, The implelmentation will be quite easy.

 

[GregDataReno]

A long shot - I suggest David Hay's Data Model Patterns for the 'Party Role Relationships' model. Search the web for Party model.
This is advanced data modeling that relies on time periods to define when roles start and finish. It also relies on extended entity relational modeling using super/sub types. Navigating records requires queries; for example to find the current chairman of a committee requires find the Chairman role that started before today, and has not yet ended - time becomes part of the primary key.

 

[jdraw]

Richard Rost has a multi-part youtube series on an Association database suited to Groups, Clubs, Churches...etc.
It may offer some insight on structure and requirements. The first video is here.