CODEWITHSUNDEEP
c#.netdatabaseentity-frameworkdatabase-design
aKzenT
aKzenT

Reputation: 7895

Modelling group membership with a "selected" member in Database

In my data model I have an entity Group and another entity GroupMember. One Group consists of one or more GroupMembers, but one GroupMember can only be in one Group at the same time. So far no problem, in the database GroupMember has a foreign key to the Group's id. However now I want to have one of the members to be the "default" or "selected" member. There should always be exactly one selected member not more and not less.

I tried modelling this in Entity Framework having one 1-* association to model the group membership and one (0..1)-1 relationship to save an instance of the selected GroupMember inside of Group.

However now I obviously have the problem, that when inserting instances of Group and GroupMember I get an error that entity framework cannot determine in which order to insert the items, since Group requires a valid GroupMember as the default member, but the GroupMember cannot be inserted unless without referencing an existing Group entity. A chicken-egg problem so to say...

The easiest way would probably be to make one of the relationships optionally, but this would remove a constraint that I would like to have during normal database operation. Ideally entity framework should insert the data in any order to the database and the database should check constraint violations only at the end of the transaction.

Another way would be to model the selected member as a boolean property "IsSelected" in the GroupMember. However I'm not sure how to ensure that there is only one selected member at the same time using only the entity framework designer (I want to try to avoid working with the database directly).

Can you offer any guidance what would be the preferred way to handle this? Thanks!

Upvotes: 2

Views: 161

Answers (2)

Aaronaught
Aaronaught

Reputation: 122654

The correct way to model this is with an association table:

+-------+              +--------+                 +--------+
| Group |--------------| Member |-----------------| Person |
+-------+ 1          * +--------+ 1             1 +--------+
    | 1                                               | 1
    |                                                 |
    |                                                 |
    | 0..1                                            |
+--------+                                            |
| Leader |--------------------------------------------+
+--------+ 0..1

I'm pretending that "leader" is an accurate description of who is "special" in the group. You should try to use a more descriptive name than "selected".

The schema looks like this:

CREATE TABLE Group
(
    Id int NOT NULL PRIMARY KEY,
    ...
)

CREATE TABLE Person
(
    Id int NOT NULL PRIMARY KEY,
    ...
)

CREATE TABLE Member
(
    PersonId int NOT NULL PRIMARY KEY
        CONSTRAINT FK_Member_Person FOREIGN KEY REFERENCES Person (Id)
            ON UPDATE CASCADE ON DELETE CASCADE,
    GroupId int NOT NULL
        CONSTRAINT FK_Member_Group FOREIGN KEY REFERENCES Group (Id)
            ON UPDATE CASCADE ON DELETE CASCADE
)
CREATE INDEX IX_Member_Group ON Member (GroupId)

CREATE TABLE Leader
(
    PersonId int NOT NULL PRIMARY KEY
        CONSTRAINT FK_Leader_Person FOREIGN KEY REFERENCES Person (Id)
            ON UPDATE CASCADE ON DELETE CASCADE,
    GroupId int NOT NULL
        CONSTRAINT FK_Leader_Group FOREIGN KEY REFERENCES Group (Id)
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT U_Member_Group UNIQUE (GroupId)
)

It expresses the following information about the relationships:

  • A group exists, period. It may or may not have members. If it has no members, then by definition it also has no leader. It still exists, because new members might be added later.

  • A person exists, period. A person would not cease to exist simply because his/her group does.

  • A person may be a member of one and only one group.

  • A person may also be the leader of a group. A group can only have one leader at a time. The leader of a group may or may not be considered a member.

You may think that the constraints imposed by this relational design are significantly looser than the ones asked about in your question. And you'd be right. That's because your question is conflating the data model with the business/domain requirements.

In addition to this model you should also have several business rules, enforced by your application, such as:

  • If a group has no members, it is deleted/deactivated/hidden.

  • If a deactivated/hidden group acquires members, it is reactivated/shown.

  • A person must be a member of some group. This information must be supplied when a new person is added (it does not have to be an existing group, it can be a new group). If a person's membership group is deleted, this should trigger an exception process; alternatively, do not allow a group to be deleted if it still has members.

  • A group which has members must have a leader. If a new person is added to an empty group, that person becomes the leader. If the leader (person) is deleted, then a new leader should be automatically selected based on some criteria, or an exception process should be triggered.

Why is this the "correct" design?

First of all because it accurately portrays the independence of entities and their relationships. Groups and persons do not actually depend on each other; it is simply your business rules dictating that you are not interested in persons without a group membership or groups without any members or leaders.

More importantly because the indexing and constraints are far cleaner:

  • Querying the members of a group is fast.
  • Querying the membership(s) of a person is fast.
  • Querying the leader of a group is fast.
  • Querying the persons who are also leaders is fast.
  • Deleting a group will automatically remove all group memberships/leaders.
  • Deleting a person will automatically remove all group memberships/leaderships.
  • Changing a membership is still a single UPDATE statement.
  • Changing a leadership is still a single UPDATE statement.
  • SQL Server won't complain about multiple cascade paths.
  • Each table has at most 2 indexes, on the columns you'd expect to be indexed.
  • You can easily extend this design, i.e. to accommodate different types of membership.
  • Changes to membership/leadership will never interfere with simple queries (such as finding a person by name).
  • Every ORM can handle this with no trouble at all. Generally you would treat it as a many-to-many but you might be able to implement it as nullable-one-to-one.

All of the other solutions have some serious, fatal flaw:

  • Putting the GroupId on Person and LeaderId on Group results in a cycle that cannot be resolved except by making at least one of the columns nullable. You will also not be able to CASCADE one of the relationships.

  • Putting the GroupId on Person and an additional IsLeader on Person does not allow you to enforce the upper bound (1 leader per group) without a trigger. Actually, you technically can with a filtered index (SQL '08 only), but it's still wrong-headed because the IsLeader bit does not actually designate a relationship, and if you accidentally update the GroupId but forget about IsLeader then you've suddenly just made this person the leader of an entirely different group, and probably violated the at-most-one constraint.

  • Some people will choose to add GroupId to Person but still maintain the Leader association table. That is a better design conceptually, but since you'll likely have a CASCADE from Group to Person, you won't be able to put a two-way CASCADE on Leader as well (you'll get the "multiple cascade paths" error if you try).

Yes, I know it's a little more work and requires you to think a little harder about what your business rules are, but trust me, this is what you want to do. Anything else will only lead to pain.

Upvotes: 2

Thomas Weller
Thomas Weller

Reputation: 11717

The easiest way to do this is as follows:

  • Declare a boolean property IsSelected on the GroupMember entity.
  • Add a partial class declaration to the GroupMember class (all EF entity classes are declared partial, so it's easy to extend them with custom code).
  • Subscribe to the 'BeforeValueChanging' event of the IsSelected property (I can't remember the exact name of the event from the top of my head, but you can be sure that EF provides something like that.).
  • In your event handler, you then can implement the desired logic. There's no need to directly care about the database...

HTH!

Upvotes: 0

Related Questions