Tricky design question to enforce a relationship

[TABROCK]

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

[June7]

Perhaps another field in relTblGroupsCoaches - maybe a YesNo named Leader. Making sure each group has a leader could involve a query (maybe a report output) that lists groups with no leader record. This validation could probably also be done during data entry.

Requiring each coach be assigned to at least one group could be tricky. I don't think Enforce Referential Integrity can assure that. Again, maybe a query and report output to show the deficiency.

[TABROCK]

Perhaps another field in relTblGroupsCoaches - maybe a YesNo named Leader. Making sure each group has a leader could involve a query (maybe a report output) that lists groups with no leader record. This validation could probably also be done during data entry.

Requiring each coach be assigned to at least one group could be tricky. I don't think Enforce Referential Integrity can assure that. Again, maybe a query and report output to show the deficiency.

June7,
thanks for that. I think you are helping me work through this.

I think you are confirming my thought that the underlying tables/relationships won't enforce what I want, and that I will have to enforce it at data entry time, somehow. I think my flow could be (for new groups) 1) input Coach email (the leader), 2) input (new) GroupName, 3) somehow assign that pairing as an entry in the rel table with a YesNo, as you suggested, marked "yes". Subsequently adding additional coaches to a group would be easier (combo for groups) and the YesNo field would never actually be exposed to the user for data entry, just defaulted to "no".
Having the flow go this way would probably also enforce that the coaches be assigned to groups because that would be a required field on the "add more coaches" form.

Still looking for other ideas, but this might work.

Thank you,
Todd