"Neo2000" Says
The novice/intermediate is user specific and not club specific. There exists just one large happy club with the users defining their proficiency level for that club. I'm thinking we can do away with clubstatusmaster, move clubstatusid into clubid?
No no think I didnt name it properly...lets rename it to ClubMemberStatusId.
ClubMemberStatusMaster : Description : This contains the various statuses of the members belonging to a club. Basically the domain table for "Intermediate" ,"Novice" etc. T
he assumption here this is common across all members and is applicable for all clubs. If a particular club does not support these statuses of a member one can have a generic status "Generic" or "NA" .
ClubMemberStatusID --> PK for this table
ClubMemberStatusDesc --> Description
ClubMembershipDet :
Description : This table contains the member ship details for all your members.
MemberId :
ClubID :
ClubMemberStatusID :
Make a composite primary key of the above three fields. And make them as foreign key individually to the master tables.
The advantage of above is it will be easier for you to query from the table
ClubMembershipDet on any level.
Eg) With appropriate query you can get the following details :
1) Members belonging to a particular club
2)All the clubs of a particular member
3) All the clubs of a member with a particular status
4)All the members who have a particular status in a particular club
Etc etc..
Other advantage is since we have separated the master tables from the transaction table you can add more clubs ,change description,status descriptions etc and it wont affect the existing data since they are linked by "Ids" which of course should not be changed. So this ensures scalability to use a self evident jargon.