How would inheritance be used in MySQL?

Question

So I'm reading a book on database design principles and came on the chapter about inheritance, but I'm confused on how I could "connect" sub classes with their super class in MySQL ?

The table structure would, for example, look like this

So how would I relate these two sub classes with their super class so that I could easily do a query similar to " hey, get me a correct contract type for a Person he is assigned to ".

A person could have one of each, or even both of them, if, for example, a Person is a student doing a part time job as a lecturer as well to finance it's schoolarship, or a Lecturer on some kind of extra course ( so he is a student as well as a lecturer ).

Answer 1

There are various approaches to solve this problem; OR-Mappers like Hibernate implement them all. But you can do this manually, too:

1. The most simple one is called "Single Table Inheritance". This includes using a discriminator column which allows you to store the type name or another type identifier in order to be able to seperate types. This is the easiest way to work with, but be aware, there are these tradeoffs:

   • you cannot use NOT NULL constraints in columns only present in one of the leaf types
   • broad tables with many columns become even broader with new types, this will decrease the overall performance

2. The second one is called "Join Inheritance" and it reflects your UML model. You will have a table "Contract", a table "student_contract" and a table "lecturer_contract", every table only keeps the data related to the type, not of it's supertype. You will use a SQL JOIN to select the data of one specific type. This approach enables you to have small tables (fast) but growing complexity with more inheritance (more JOINs). The core tradedoff is :

   • good distribution on data
   • horrible sql on really complex inheritance structure (which are bad style anyway!)

3. The last approach is "Table per Entity" and this means you will create a table for each leaf type - in your case "Student_Contract" and "Lecturer_Contract". This is a nice approach, but beware, there also is a tradeoff ;-) You have to generate you key values safe along all tables (e.g. by using a SEQUENCE). This construct is not supported by every database (e.g. mysql does not support SEQUENCES as a construct reusable along multiple tables).

For academic purposes, I'd suggest you to try 1) for the sake of simplicity

For real projects, choose wisely - most projects I made based on 2)

EDIT:

You example would end up- depending on the relationship between Person and Contract - in something like this:

PERSON (1) ----- (N) PERSON_CONTRACT (N) ---- (1) CONTRACT 

(M to N relationship, if the person can be multiply assigned to a contract)

or

PERSON (1) --------- (N) CONTRACT

(1:N relationship between person an contract. A contract can only have one person, a person can have multiple contracts)