MySQL: Два n:1 отношения, но не оба сразу

Ваш текущий проект называется исключающими дугами , где устанавливает таблицу, имеющую два внешних ключа, и требуется, чтобы ровно один из них был ненулевым. Это один из способов реализации полиморфных ассоциаций, поскольку данный внешний ключ может ссылаться только на одну целевую таблицу.

Другое решение - создать общую «супертаблицу», на которую ссылаются как пользователи , так и школы , а затем использовать ее как родительский для наборов .

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

Вы можете думать об этом как об аналоге интерфейса в объектно-ориентированном моделировании:

interface SetOwner { ... }

class User implements SetOwner { ... }

class School implements SetOwner { ... }

class Set {
  SetOwner owner;
}

По вашим комментариям:

Итак, таблица SetOwner содержит как UserID, так и SchoolID, верно? Это означало бы, что мне не разрешили бы иметь один и тот же идентификатор для пользователя и школы. Как я могу добиться этого?

Разрешить таблице SetOwners генерировать значения идентификаторов. Вы должны вставить в SetOwners, прежде чем сможете вставить в Users или Schools. Так что сделайте идентификаторы в Users and Schools без автоматического увеличения ; просто используйте значение, сгенерированное SetOwners:

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

Таким образом, ни одно значение идентификатора не будет использоваться ни для школы, ни для пользователя.

Если я хочу получить тип владельца для набора, нужен ли мне атрибут ownerType в моей таблице SetOwner?

Вы, конечно, можете это сделать. Фактически, могут быть и другие столбцы, общие как для пользователей, так и для школ, и вы можете поместить эти столбцы в надтаблицу SetOwners. Это входит в паттерн Мартина Фаулера Наследование таблицы классов .

И если я хочу получить название школы или пользователя (в зависимости от того, какой это тип), могу ли я сделать это с помощью одного запроса или мне нужно два запроса (первый для получения типа, а второй для получения name)?

Вам нужно выполнить соединение. Если вы запрашиваете из заданного набора и знаете, что он принадлежит пользователю (а не школе), вы можете пропустить присоединение к SetOwners и присоединиться непосредственно к пользователям. Соединения не обязательно должны происходить по внешним ключам.

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

Если вы не знаете, принадлежит ли данный набор пользователю или школе, вам придется выполнить внешнее соединение с обоими:

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 должен соответствовать одной или другой таблице, Users или школы, но не то и другое вместе.