What is the best design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]

Question

My application has notification settings for users that can belong to groups. A group administrator can define settings for the entire group, so that when any user performs an action, the administrator is notified. The administrator can also define settings for an individual user, which will override the group setting.

Right now I have a database with columns: group_id, action1, action2, action3, .... The actions are booleans that determine if the administrator is notified when that action is performed by a user in his or her group.

I could make a separate table owned by the User model instead of the Group model, but it feels inefficient to store the exact same data in an entirely separate table save changing the group_id to user_id.

Another option is to add user_id to the table I already have, and allow null values for group_id. When determining notification settings for a User, the application would first choose the setting based on User, and fallback to the setting where group_id is not null. This feels inefficient because there will be many null values in the database, but it definitely requires less work on my part.

Is there a design for this situation that is more efficient than the two I've described?

Answer 1

Generally, there are two strategies to handle a situation like this:

1. Use Exclusive FKs

---

Essentially, each of the possible parent tables will have its own, separate foreign key in the child table, and there is a CHECK enforcing exactly one of them is non-NULL. Since FKs are only enforced on non-NULL fields (meaning, when a FK is set to NULL there is no database-level validation), only one of the FKs will be enforced.

For example:

(relationship between user and group omitted)

CHECK (
    (group_id IS NOT NULL AND user_id IS NULL)
    OR (group_id IS NULL AND user_id IS NOT NULL)
)

2. Use Inheritance

---

Inherit user and group from a common supertype and then connect the setting to the supertype:

For more information on inheritance (aka. category, subclassing, subtype, generalization hierarchy etc.), take a look at "Subtype Relationships" chapter of ERwin Methods Guide. Unfortunately, modern DBMSes don't natively support inheritance - for some ideas about physically implementing it, take a look at this post.

This is a heavy-duty solution probably not justified for just two tables (groups and users), but can be quite "scalable" for many tables.

Answer 2

how about an Actions table instead?

It could have the columns:

Table Actions:
ActionId - Identity columns
Action   - Store your action here; type would depend on your system
RefId    - The Id for either the user or the group
RefTable - either User or Group

then when accessing the table you know your ID already, and you know if it's a group or user and can then get the appropriate action.

This make sense?

Update:

If its possible that you could have the same action for both user/group and want one to take priority (as you mentioned in your Q) you could also add a priority column and set it as a tinyInt - lower number = higher priority. Then when you select the actions you can order them by this priority. Then perform the first action, or each action in order.