MySQL：两个n：1关系，但不能同时两个 [英] MySQL: Two n:1 relations, but not both at once

问题描述

对于标题很难解释。

我需要一个类似于这样的数据模型：

I need a data model similar to this:

如你所见，一套可以属于用户或学校。我的问题：应该只允许属于用户或学校。但是从来没有两个同时。

As you can see, a set can belong to both a user or a school. My problem: It should only be allowed to belong either to a user OR a school. But never both at the same time.

如何解决这个问题？

推荐答案

您当前的设计称为独占弧，其中集合表具有两个外键，并且只需要其中一个为非-空值。这是实现多态关联的一种方法，因为给定的外键只能引用一个目标表。

Your current design is called exclusive arcs where the sets table has two foreign keys, and needs exactly one of them to be non-null. This is one way to implement polymorphic associations, since a given foreign key can reference only one target table.

另一个解决方案是使用户和学校之间的共同supertable / code>引用，然后使用它作为集合的父级。

Another solution is to make a common "supertable" that both users and schools references, and then use that as the parent of sets.

create table set_owner

create table users 
  PK is also FK --> set_owner

create table schools
  PK is also FK --> set_owner

create table sets 
  FK --> set_owner

您可以将其视为类似于OO建模中的界面 ：

You can think of this as analogous to an interface in OO modeling:

interface SetOwner { ... }

class User implements SetOwner { ... }

class School implements SetOwner { ... }

class Set {
  SetOwner owner;
}

---

重新发表评论： / p>

---

Re your comments:

所以SetOwner表包含UserID和SchoolIDs，是否正确？这意味着我不会被允许为用户和学校拥有相同的ID。如何执行此操作？

So the SetOwner table contains both UserIDs and SchoolIDs, correct? That would mean that I wouldn't be allowed to have the same ID for an user and a school. How can I enforce this?

让SetOwners表生成id值。您必须插入SetOwners才能插入到用户或学校。所以使用户和学校中的id不是自动递增;只需使用SetOwners生成的值：

Let the SetOwners table generate id values. You have to insert into SetOwners before you can insert into either Users or Schools. So make the id's in Users and Schools not auto-incrementing; just use the value that was generated by SetOwners:

INSERT INTO SetOwners DEFAULT VALUES; -- generates an id
INSERT INTO Schools (id, name, location) VALUES (LAST_INSERT_ID(), 'name', 'location');

这样一来，学校和用户都不会使用给定的id值。

This way no given id value will be used for both a school and a user.

如果我想获取一组的所有者类型，我需要在我的SetOwner表中有一个ownerType属性？

If I'd like to get the owner type for a set, do I need an ownerType attribute in my SetOwner table?

你当然可以这样做。实际上，用户和学校可能还有其他专栏，您可以将这些列放在超级的SetOwners中。这将进入Martin Fowler的 Class Table Inheritance 模式。

You can certainly do this. In fact, there may be other columns that are common to both Users and Schools, and you could put these columns in the supertable SetOwners. This gets into Martin Fowler's Class Table Inheritance pattern.

如果我想要获取学校或用户的名称（无论哪种类型），我可以使用单个查询来做到这一点，还是需要两个查询（首先得到类型，第二个获取名称）？

And if I want to get the name of the school or the user (whichever type it is), can I do this with a single query, or do I need two queries (first to get the type and second to get the name)?

你需要加入。如果您从给定的集合查询，并且您知道它属于用户（不是学校），则可以跳过加入SetOwners并直接加入用户。连接不一定要用外键。

You need to do a join. If you're querying from a given Set and you know it belongs to a user (not a school) you can skip joining to SetOwners and join directly to Users. Joins don't necessarily have to go by foreign keys.

SELECT u.name FROM Sets s JOIN Users u ON s.SetOwner_id = u.id WHERE ...

如果您不知道给定的集合是否属于用户或学校，您必须对两者进行外部连接：

If you don't know whether a given set belongs to a User or a School, you'd have to do an outer join to both:

SELECT COALESCE(u.name, sc.name) AS name 
FROM Sets s 
LEFT OUTER JOIN Users u ON s.SetOwner_id = u.id
LEFT OUTER JOIN Schools sc ON s.SetOwner_id = sc.id 
WHERE ...

你知道SetOwner_id必须匹配一个或另一个表，用户或学校，但是不是两者。

You know that the SetOwner_id must match one or the other table, Users or Schools, but not both.

这篇关于MySQL：两个n：1关系，但不能同时两个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！