Constraint checks on multiple association tables vs. unique association table that centralizes these constraint checks

Question

I have the following database schema (MySQL 5.7):

I have assistants that may be linked to either:

• one customer (might evolve to multiple customers in the future, hence the association table)
• one or many terminals

I want to implement the constraint: an assistant cannot simultaneously be linked to a customer and a terminal. How to implement this constraint without complex machinery?

Instead of thinking about custom functions and triggers, I'm considering the following schema:

The database model looks strange to me, but the association table is now unique and I can implement constraint checks more easily because they relate to a unique table.

Other possibility:

Now my assistants are totally separated into two types in the database, with the same columns.

What would be the way to go?

Answer 1

This is a classic case of polymorphic association.

Your existing design is one option, although it gets messy when there are a lot of different types, and it doesn't feel as clean in terms of normalization.

Instead, you need separate tables for CustomerAssistant and TerminalAssistant with 1:1 correspondence to Assistant. This is separate to the join tables.

It would also be good to add a discriminator column, which can be a computed column in the derived tables. In other DBMSs you can put a foreign key on a computed column in the derived tables, in MySQL you would need a check constraint instead.

CREATE TABLE Assistant (
  id INT NOT NULL PRIMARY KEY,
  type TINYINT NOT NULL CHECK (type IN (1, 2)),
  firstname TEXT NOT NULL,
  UNIQUE (id, type)
);

CREATE TABLE TerminalAssistant (
  id INT NOT NULL PRIMARY KEY,
  type TINYINT NOT NULL CHECK (type = 1),
  FOREIGN KEY (id, type) REFERENCES Assistant (id, type)
);

Now your join tables can reference those tables.

CREATE TABLE Terminal_TerminalAssistant (
  terminal_id INT NOT NULL,
  assistant_id INT NOT NULL,
  PRIMARY KEY (terminal_id, assistant_id),
  FOREIGN KEY (terminal_id) REFERENCES Terminal (id),
  FOREIGN KEY (assistant_id) REFERENCES TerminalAssistant (id)
);

In the case of Customer, since you can only ever have one then you can use the derived table as the actual join table also.

CREATE TABLE CustomerAssistant (
  assistant_id INT NOT NULL PRIMARY KEY,
  assistant_type TINYINT NOT NULL CHECK (type = 2),
  customer_id INT NOT NULL,
  FOREIGN KEY (id, type) REFERENCES Assistant (assistant_id, assistant_type),
  FOREIGN KEY (customer_id) REFERENCES Customer (id),
);

Alternatively you could do the same design as above, except add a UNIQUE constraint to assistant_id to ensure that each assistant is only present once. If you change that requirement you can just remove the constraint without any further changes.

CREATE TABLE CustomerAssistant (
  id INT NOT NULL PRIMARY KEY,
  type TINYINT NOT NULL CHECK (type = 2),
  FOREIGN KEY (id, type) REFERENCES Assistant (id, type)
);

CREATE TABLE Customer_CustomerAssistant (
  customer_id INT NOT NULL,
  assistant_id INT NOT NULL,
  PRIMARY KEY (customer_id, assistant_id),
  UNIQUE (assistant_id),
  FOREIGN KEY (customer_id) REFERENCES Customer (id),
  FOREIGN KEY (assistant_id) REFERENCES CustomerAssistant (id)
);

---

Now an Assistant can only ever be one type or another, not both at the same time. If you want to change the type, you first must delete all associated rows otherwise the foreign key constraints will fail.

db<>fiddle