Hi all, sorry if the subject line was not specific enough, but I’m having a hard time even getting my issue into concise words.
I need to create a database that will track Coaches and their “Groups”.
Coaches can belong to more than one group.
Coaches must belong to at least one group.
Groups can have 1 or more coaches.
Each Group has one coach who is the “leader” for that Group. (this is the part I am finding trickiest).
So I am thinking two primary tables with a many-many relationship table in between.
tblCoaches contains:
· emailID (PK)
· FirstName
· LastName
tblGroups contains:
· GroupName (PK)
· MembershipLevel
relTblGroupsCoaches contains:
· GroupName (PK)
· emailID (PK)
Do I have the basic structure right? Where should I add a row to hold the “leader” coach for the group and ensure that each group has a leader?
Also, how will I enforce that each coach must belong to a Group?
Can these things enforced through effective design, or will it have to be enforced through the User Interface / programmatically?
Thanks for reading!
Todd