MySQL: Two n:1 relations, but not both at once

Question

Sorry for the title, it's difficult to explain.

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.

How can I solve this problem?

Answer 1

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.

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

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;
}

---

Re your comments:

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?

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');

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

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

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)?

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 ...

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

Answer 2

With the caveat: It's not entirely clear what a Set is in your data model.

I'm questioning your data model.

Why do User -> Sets and School -> Sets have to point to the same table. It's not more Normalized if those are really independent sets of data. Just because they share some similarities in the columns that they track doesn't mean they should be stored in the same table. Are they logically the same thing?

Just create separate tables for School Sets and for User Sets. This will make inverse queries easier as well because you won't have to check for null relationships in the Sets table to know if it's really a UserSet or a SchoolSet.

Answer 3

You'll have to use a trigger to enforce this business rule, because MySQL has but don't enforce CHECK constraints (on any engine).