什么是可以由两个不同的资源所拥有的数据库表中的最佳设计，因此需要两个不同的外键？(What is t

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:

一般来说，有两种策略来处理这样的情况：

1.使用专用FKS

从本质上讲，每一个可能的父表将在子表自己的， 独立的外键，并有一个CHECK 正是执行其中的一个不为NULL。 由于FKS仅在非NULL字段执行，只有FKS的一个将被强制执行。

例如：

（用户和组省略之间的关系）

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

2.使用继承

从共同的超类型继承的用户和组，然后将设置连接到父类型：

有关继承的更多信息（亦称类，子类，子类型，概括层次等），看看在“子类型的关系”一章ERwin的方法指南 。 不幸的是，现代的DBMS本身不支持继承-有关物理实现它的一些想法，看看这篇文章 。

这是一个重型的解决方案可能是没有道理的只是两个表（组和用户），但可以为许多表颇为“可扩展性”。

Answer 2:

怎么样动作表呢？

它可以有列：

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

访问表那么当你知道你的ID已经和你知道，如果它是一个组或用户，然后可以得到适当的行动。

这有意义吗？

更新：

如果可能，你可以有两个用户/组相同的动作，想一个优先（如您在您的问与答提到的），你还可以添加一个priority列，并将其设置为tinyInt -低数=更高的优先级。 然后，当你选择的操作，你可以通过这个优先级排序。 然后，执行第一动作，或在顺序中的每个动作。

文章来源: What is the best design for a database table that can be owned by two different resources, and therefore needs two different foreign keys?