本文介绍了对于可以由两个不同资源拥有并因此需要两个不同外键的数据库表的最佳设计是什么?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我的应用程序具有可属于组的用户的通知设置.组管理员可以为整个组定义设置,以便在任何用户执行操作时通知管理员.管理员还可以为单个用户定义设置,这将覆盖组设置.

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.

现在我有一个包含以下列的数据库:group_id, action1, action2, action3, ....这些操作是布尔值,用于确定管理员在其组中的用户执行该操作时是否收到通知.

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.

我可以创建一个由 User 模型而不是 Group 模型拥有的单独表,但是将完全相同的数据存储在一个完全单独的表中感觉效率低下,除非将 group_id 更改为 user_id.

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.

另一种选择是将 user_id 添加到我已有的表中,并允许 group_id 为空值.在确定用户的通知设置时,应用程序将首先根据用户选择设置,然后回退到 group_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?

推荐答案

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

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

本质上,每个可能的父表在子表中都有自己的单独外键,并且有一个 CHECK 强制其中一个是非-空值.由于 FK 仅对非 NULL 字段强制执行,因此只会强制执行其中一个 FK.

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.

例如:

(省略用户和组之间的关系)

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

2.使用继承

从通用超类型继承用户和组,然后将设置连接到超类型:

2. Use Inheritance

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

有关继承(又名类别、子类、子类型、泛化层次结构等)的更多信息,请查看 ERwin 方法指南.不幸的是,现代 DBMS 本身并不支持继承 - 有关物理实现它的一些想法,请查看这篇文章.

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.

这篇关于对于可以由两个不同资源拥有并因此需要两个不同外键的数据库表的最佳设计是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-03 22:30
查看更多