How do you model disjoint relationships between two relations in MySQL Workbench?

Question

You have the option to uncheck Mandatory in the foreign key tab of the relationship window, but that doesn't fully capture the meaning of a disjoint relationships, which is an EITHER-OR relationship between multiple relations.

Answer 1

Your referring to the mandatory property of the foreign key makes me believe you are either misunderstanding the meaning of a disjoint relationship, or implementing it with a relation in the wrong "direction".

Let's say we want to implement the following schema:

• class: Staff Member
• class: Permanent (specialises Staff Member)
• class: Temporary (specialises Staff Member)
• a Staff Member is either a Permanent employee or a Temporary contractor

A corresponding EER schema would be (MySQL syntax):

CREATE TABLE staff_member (
  id INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL
);

CREATE TABLE permanent (
  id INT PRIMARY KEY,
  next_appraisal DATETIME NOT NULL,
  FOREIGN KEY (id) REFERENCES staff_member(id)
);

CREATE TABLE temporary (
  id INT PRIMARY KEY,
  contract_end DATETIME NOT NULL,
  FOREIGN KEY (id) REFERENCES staff_member(id)
);

Notice the foreign key is from the specialised entity to the parent entity (id being the primary key, it is also always mandatory by definition).

This still doesn't answer your question. How to model the disjoint property of this relationship? You cannot do this easily (neither can you model that a specialisation is complete, by the way).

Many RDBMS support the use of CHECK constraints in order to enforce these extra conditions, but MySQL does not (beware, the syntax is accepted by the MySQL parser, but the declaration is ignored). However, simple workarounds exist that result in the same effect.