0

Let's say I have two tables - User and TrainingGroup. Relation between these two tables has to be like this:

  • User can be in only one TrainingGroup
  • One TrainingGroup can have multiple users

The problem happens because I need the following relationships as well:

  • User can coach many TrainingGroups (group coach, eg. kids group, adults group etc.)
  • Group can have only one user (coach)

Also, it is not possible for a user to be a coach and member of the group at the same time.

Is it possible to achieve that, and how? Any other design ideas?

Code block below roughly explains what I'm trying to achieve:

    public class TrainingGroup
    {
        public User Coach { get; set; } // Coach of the group
        public int CoachId { get; set; }

        public List<User> Members { get; set; } //Members in the training group
    }

    public class User
    {
        public int TrainingGroupId { get; set; }
        public TrainingGroup TrainingGroup { get; set; } // Group the user (Member) is training in

        public List<TrainingGroup> CoachingGroups { get; set; } // Groups that the user is coaching
    }
ropouser
  • 1
  • 1
  • A similar issues is discussed in "[Are circular references acceptable in database?](https://stackoverflow.com/questions/1006917/are-circular-references-acceptable-in-database)". One issue is bootstrapping the data. If `TrainingGroup.CoachId` and `User.TrainingGroupId` are both non-nullable, there is no way to initially populate the data on an empty database. If one or both of these are null, that issue goes away. Will every Training Group always have a coach? Will every user (particularly coaches) always be in a training group? – T N Feb 04 '23 at 17:49
  • Hey @TN Thank you for your reply. Yes we can assume that there is a default TrainingGroup and every user will be in a group. I can do the same for coaches TrainingGroup doesn't need to have a coach, but if need be I can add default value to that also – ropouser Feb 06 '23 at 07:29
  • I suggest considering allowing null FK values to represent "no coach / no training group assigned" instead of having special placeholder user and training group records. That's been a standard practice in database projects I've worked on. Exceptions are sometimes made for lookup tables (such as for a status or category) where it may be advantageous to have an explicit "unknown" or "unspecified" value. – T N Feb 06 '23 at 21:03

0 Answers0