Results 1 to 3 of 3
  1. #1
    TABROCK is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    2

    Question Tricky design question to enforce a relationship

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,169
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    TABROCK is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    2
    Quote Originally Posted by June7 View Post
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Is it always bad to NOT enforce referential integrity?
    By Access_Novice in forum Database Design
    Replies: 8
    Last Post: 08-18-2014, 09:59 PM
  2. Tricky question
    By Xipooo in forum Access
    Replies: 5
    Last Post: 03-14-2014, 11:13 AM
  3. Relationship / Overall Design Question
    By nunzii in forum Database Design
    Replies: 2
    Last Post: 04-24-2013, 04:08 PM
  4. Replies: 1
    Last Post: 12-23-2012, 08:32 PM
  5. Tricky SQL Question
    By ttocsmi in forum Queries
    Replies: 8
    Last Post: 10-01-2012, 10:04 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums