本文介绍了什么是可以由两个不同资源拥有的数据库表的最佳设计，因此需要两个不同的外键？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我的应用程式有属于群组的使用者的通知设定。组管理员可以定义整个组的设置，以便在任何用户执行操作时通知管理员。 现在我有一个包含列的数据库： group_id， action1，action2，action3，... 。这些操作是布尔值，用于确定当用户在其组中执行操作时是否通知管理员。 我可以创建一个由用户拥有的单独表模型而不是Group模型，但是将完全相同的数据存储在完全独立的表中感觉效率低下，除了将 group_id 更改为 user_id user_id 添加到我已经拥有的表中，并允许空值（例如：code> group_id 。当确定用户的通知设置时，应用程序将首先基于用户选择设置，并回退到 group_id 不为空的设置。这感觉效率低下，因为在数据库中会有很多空值，但是这对我来说肯定需要更少的工作。 这种情况下是否有更高效的设计解决方案通常，有两种策略来处理这种情况： 1。使用独占FK 基本上，每个可能的父表在子表中都有自己的独立外键，执行完全一个的检查为非NULL。由于FK仅在非NULL字段上实施，因此只会强制执行一个FK。 例如： > >（用户和组之间的关系省略） CHECK（ IS NULL） OR（group_id IS NULL AND user_id IS NOT NULL）） 2。使用继承 从常用超类继承用户和组，然后将设置连接到超类型： img src =https://i.stack.imgur.com/3NKi6.pngalt =enter image description here> 有关继承的更多信息类别，子类，子类型，泛化层次等），请查看 ERwin方法指南。不幸的是，现代DBMS本身不支持继承 - 有关实际实现它的一些想法，请查看此帖。 这是一个重型解决方案，可能不适用于两个表（组和用户），但对于许多表来说可以是相当可扩展的。 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? 解决方案 Generally, there are two strategies to handle a situation like this:1. Use Exclusive FKsEssentially, 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.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 InheritanceInherit 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. 这篇关于什么是可以由两个不同资源拥有的数据库表的最佳设计，因此需要两个不同的外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！
