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.
现在我有一个数据库列:group_id,action1,action2,action3,....这些操作是布尔值，用于确定管理员在其组中的用户执行该操作时是否收到通知。
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.
我可以使用用户模型而不是Group模型来创建一个单独的表，但是在一个完全独立的表中存储完全相同的数据会让人感觉效率不高，可以将group_id改为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.
另一个选项是将user_id添加到我已经有的表中，并允许group_id的空值。当确定用户的通知设置时，应用程序将首先根据用户选择设置，然后返回到group_id不为空的设置。这让人感觉效率很低，因为数据库中有很多空值，但这肯定需要我少做一些工作。
Is there a design for this situation that is more efficient than the two I've described?
有没有一种比我所描述的更有效率的设计?
2 个解决方案
#1
9
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, only one of the FKs will be enforced.
本质上，每个可能的父表在子表中都有自己的、独立的外键，并且有一个检查强制执行其中一个非空键。由于FKs只在非空字段上执行，因此只有一个FKs将被执行。
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.
有关继承的更多信息(aka)。类别，子类，子类，子类，泛化层次等)，看看“子类型关系”章节的ERwin方法指南。不幸的是，现代的dbms并不支持继承—关于物理实现继承的一些想法，请参阅本文。
This is a heavy-duty solution probably not justified for just two tables (groups and users), but can be quite "scalable" for many tables.
这是一种功能强大的解决方案，可能并不只适用于两个表(组和用户)，但对于许多表来说，它是相当“可伸缩”的。
#2
0
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.
然后在访问表时，您已经知道了您的ID，并且您知道它是否是一个组或用户，然后可以得到适当的操作。
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.
如果可能的话，您可以对用户/组执行相同的操作，并希望其中一个具有优先级(如您在Q中提到的)，您还可以添加一个priority列，并将其设置为tinyInt - less number = higher priority。然后，当您选择操作时，您可以按此优先级对它们进行排序。然后执行第一个动作，或者按顺序执行每个动作。
#1
9
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, only one of the FKs will be enforced.
本质上，每个可能的父表在子表中都有自己的、独立的外键，并且有一个检查强制执行其中一个非空键。由于FKs只在非空字段上执行，因此只有一个FKs将被执行。
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.
有关继承的更多信息(aka)。类别，子类，子类，子类，泛化层次等)，看看“子类型关系”章节的ERwin方法指南。不幸的是，现代的dbms并不支持继承—关于物理实现继承的一些想法，请参阅本文。
This is a heavy-duty solution probably not justified for just two tables (groups and users), but can be quite "scalable" for many tables.
这是一种功能强大的解决方案，可能并不只适用于两个表(组和用户)，但对于许多表来说，它是相当“可伸缩”的。
#2
0
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.
然后在访问表时，您已经知道了您的ID，并且您知道它是否是一个组或用户，然后可以得到适当的操作。
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.
如果可能的话，您可以对用户/组执行相同的操作，并希望其中一个具有优先级(如您在Q中提到的)，您还可以添加一个priority列，并将其设置为tinyInt - less number = higher priority。然后，当您选择操作时，您可以按此优先级对它们进行排序。然后执行第一个动作，或者按顺序执行每个动作。