If you want to model object inheritance on a SQL Database you came along some problems. First of all inheritance is not implemented, so you need a workaround. There exists three common patterns which are called Single Table Inheritance, Class Table Inheritance and Concrete Table Inheritance. If the existence of a parent row makes no sense if no child row reference it (e.g. parent is abstract) you came into some problems with standard SQL settings to guarantee this invariant. Maybe you also want that a parent row can only be specialized by exactly one child, which is only with Foreign Key not possible. In this article I will face this two problems.

Class Model

Lets assume we have this simple inheritance relation:

Concrete Table Inheritance

Let us transform this model to an SQL schema using the Concrete Table Inheritance pattern. In this pattern we do not transform abstract classes to a table, but the rest (In the picture all INT are foreign keys and all TEXT are an example for attributes):

In this case we have no row/instance of the abstract parent table/class, which is good and also reduces the amount of tables if you have a large inheritance tree since you skip abstract classes. Finding the corresponding foo row to a particular childX becomes faster, but not simpler since you have to program a special table foo_childX for every childX. To get the parent_attr from a child you do not have to access another table and therefore another memory location. But in contrast there are some other disadvantages:

1. The attribute relation_attr is splitted into multiple tables. Searching or a value requires iteration over multiple tables. Also the iteration over the foo-parent relation becomes more complicated.
2. The attribute parent_attr is also splitted into multiple tables.
3. Finding a relation to a foo_id becomes slower, since you have multiple indexes to check. You maybe have to check the index of all child1, ..., childN to make sure to get all relations.
4. Assuming you have n foo classes (classes which have an aggregation with parent) and m childX tables, you need n * m relation tables between them, which gets fast horrible.

Class Table Inheritance

So lets try a better solution for this case called Class Table Inheritance:

This model solves all disadvantages mentioned above in the Concrete Table Inheritance section and makes everything more clear. It comes also closer to your class inheritance model. But of course there are also some disadvantages:

1. To access all attributes of a particular childX table you have also to access the parent table and therefore an additional memory location. This can be up to h tables, where h is the height of your inheritance tree.
2. Finding the relations for a particular foo force you to search additionally over all ancestors of your child tables. This can be up to h-1 tables (plus foo_parent and the childX table itself of course)
3. For finding the child of a particular parent_id you maybe have to check the index of all child1, ..., childN tables. This slow down your select, but I will provide a workaround for that.
4. Multiple childX tables could have a foreign key to same parent_id. In most cases this breaks your inheritance since an instance/row of parent could have multiple children. Only sometimes you want that. I also provide an solution for make sure that parent have only one child.
5. Normally if foreign key points to a row r the database will preventing deleting r to not have an inconsistent database. Now lets assume a row in foo_parent have an parent_id that points to an row p in parent. parent is abstract, so p must have an child row c which has the same parent_id as p. The database engine will prevent deletion of p, since two foreign keys point to it, fine! But the engine will allow you to delete c. For the database engine this is OK since the database keep consistent for it. But you now it makes no sense that a p exists without a child c, so in real the database is inconsistent, Bad! I will also provide an idea for preventing this.

discriminator attribute

Lets add a discriminator attribute of datatype ENUM('child1','child2'). This column simply stores the type of the child. The value should also not used to get the table name, only as description. But I think the table name as description is in most cases very appropriate.

We also set this field to NOT NULL to make sure that the type is always correctly set. (I delete the parent_attr since we will not need here):

Of course you can also use a discriminator attribute on a parent table ,if one of them is not abstract, of the Concrete Class Inheritance model, but I will only describe the Class Table Inheritance model.

Fist of all we had to make sure that no one can change parent.discriminator. You always insert the parent with the child, so changing makes no sense.
But we must not allow this to prevent that anyone can update discriminator to value which is false. For example setting discriminator to 'child2' if a child1 row will point to that entry. There is no build in function for that, so we had to create an trigger:

CREATE DEFINER = CURRENT_USER TRIGGER `inheritance-example`.`parent_BEFORE_UPDATE` BEFORE UPDATE ON `parent` FOR EACH ROW
    BEGIN
        IF (old.`discriminator` != new.`discriminator`) THEN
          signal sqlstate '45000'
            set message_text = 'You could not change the type of the subtable!';
        END IF;
    END

MySQL will simply abort the operation and no rows will be affected.

If you try to update this table:

parent table:

UPDATE `inheritance-example`.`parent` SET `discriminator`='child2' WHERE `parent_id`='0';

you get the message:

Executing:
UPDATE `inheritance-example`.`parent` SET `discriminator`='child2' WHERE `parent_id`='0';

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1644: 1644: You could not change the type of the subtable!
SQL Statement:
UPDATE `inheritance-example`.`parent` SET `discriminator`='child2' WHERE `parent_id`='0'

But what are we getting out of the discriminator attribute?

Searching in only one child table

If you actually selecting on parent and want the the child of a particular parent_id you know with the discriminator attribute which table you had to look through. So only one table index has to be considered. This is the workaround for point 3.

Only a single child

Well assume we have already inserted this rows:

parent table:

child1 table:

child2 table:

like you can see 0 have two children, which is OK for MySQL but breaks our inheritance.

Lets add these triggers to child1 and child2:

CREATE DEFINER = CURRENT_USER TRIGGER `inheritance-example`.`child1_BEFORE_INSERT` BEFORE INSERT ON `child1` FOR EACH ROW
    begin
        if (SELECT `parent`.`discriminator`
                FROM `parent`
                WHERE `parent`.`parent_id` = new.`parent_id`
                )!='child1' then
                
          signal sqlstate '45000' -- general user defined error code
            set message_text = 'The parent table has not the type child1.';
            
        end if;
    end

CREATE DEFINER = CURRENT_USER TRIGGER `inheritance-example`.`child2_BEFORE_INSERT` BEFORE INSERT ON `child2` FOR EACH ROW
    begin
        if (SELECT `parent`.`discriminator`
                FROM `parent`
                WHERE `parent`.`parent_id` = new.`parent_id`
                )!='child2' then
                
          signal sqlstate '45000' -- general user defined error code
            set message_text = 'The parent table has not the type child2.';
            
        end if;
    end

Now on inserting on child2 table:

INSERT INTO `inheritance-example`.`child2` (`parent_id`, `child2_attr`) VALUES ('0', 'C2Attr0');

we get this error message back:

Executing:
INSERT INTO `inheritance-example`.`child2` (`parent_id`, `child2_attr`) VALUES ('0', 'C2Attr0');

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1644: 1644: The parent table has not the type child2.
SQL Statement:
INSERT INTO `inheritance-example`.`child2` (`parent_id`, `child2_attr`) VALUES ('0', 'C2Attr0')

MySQL will abort the insert operation and no row will be affected. Like you can see with this we can prevent that a parent can ever have more then one children with the same parent_id. Point 4. is also solved.

CREATE DEFINER = CURRENT_USER TRIGGER `inheritance-example`.`child1_BEFORE_UPDATE` BEFORE UPDATE ON `child1` FOR EACH ROW
    begin
        if (old.`parent_id` != new.`parent_id`) then -- this is for preventing unneccesarry selects. Will
                                                     -- speed up all updates where parent_id has not changed.
            if (SELECT `parent`.`discriminator`
                FROM `parent`
                WHERE `parent`.`parent_id` = new.`parent_id`
                )!='child1' then
                    
              signal sqlstate '45000' -- general user defined error code
                set message_text = 'The parent table has not the type child1.';
                
            end if;
        end if;
    end

CREATE DEFINER = CURRENT_USER TRIGGER `inheritance-example`.`child2_BEFORE_UPDATE` BEFORE UPDATE ON `child2` FOR EACH ROW
    begin
        if (old.`parent_id` != new.`parent_id`) then -- this is for preventing unneccesarry selects. Will
                                                     -- speed up all updates where parent_id has not changed.
            if (SELECT `parent`.`discriminator`
                FROM `parent`
                WHERE `parent`.`parent_id` = new.`parent_id`
                )!='child2' then
                    
              signal sqlstate '45000' -- general user defined error code
                set message_text = 'The parent table has not the type child2.';
                
            end if;
        end if;
    end

Auto create and delete parent and preventing deletion of child if parent is referenced

Auto create

For inserting a childX row you had to normally insert first into parent:

INSERT INTO `inheritance-example`.`parent`
(`discriminator`) -- `parent_id` is an AUTO INCREMENT column, so do not insert it.
VALUES('child1');

INSERT INTO `inheritance-example`.`child1`
(`parent_id`, `child1_attr`)
VALUES(LAST_INSERT_ID(), "C1Attr Text");

parent table:

child1 table:

This is absolutely no problem, but you can do that more comfortable:

EDIT Maybe this is a bit too strict, so I added code so that parent will be only created if no parent_id is provided.

CREATE DEFINER = CURRENT_USER TRIGGER `inheritance-example`.`child1_BEFORE_INSERT` BEFORE INSERT ON `child1` FOR EACH ROW
    BEGIN
        if (new.`parent_id` = 0) then
            -- since we create the parent row, the discriminator type is always correct
            INSERT INTO `parent`
                (`id`, `discriminator`)
            VALUES
                (null, 'child1');

            set new.`parent_id` = last_insert_id();

        else
            if (SELECT `parent`.`discriminator`
                FROM `parent`
                WHERE `parent`.`parent_id` = new.`parent_id`
                )!='child1' then
                    
              signal sqlstate '45000' -- general user defined error code
                set message_text = 'The parent table has not the type child1.';
                
            end if;
        end if;
    END

CREATE DEFINER = CURRENT_USER TRIGGER `inheritance-example`.`child2_BEFORE_INSERT` BEFORE INSERT ON `child2` FOR EACH ROW
    BEGIN
        if (new.`parent_id` = 0) then
            -- since we create the parent row, the discriminator type is always correct
            INSERT INTO `parent`
                (`id`, `discriminator`)
            VALUES
                (null, 'child2');

            set new.`parent_id` = last_insert_id();

        else
            if (SELECT `parent`.`discriminator`
                FROM `parent`
                WHERE `parent`.`parent_id` = new.`parent_id`
                )!='child2' then
                    
              signal sqlstate '45000' -- general user defined error code
                set message_text = 'The parent table has not the type child2.';
                
            end if;
        end if;
    END

Then you have to set the default value of child1.parent_id and child2.parent_id to 0. This is a little trick. Since the parent_id will always be set through set new.parent_id = last_insert_id(); it makes no difference what default value you set. But parent_id is the PRIMARY KEY of the children and therefore always NOT NULL and !AUTO INCREMENT. So on inserting MySQL will not allow you to insert with a NULL parent_id, but with the default value you have a workaround for that.

Now lets insert something:

INSERT INTO `inheritance-example`.`child1`
(`child1_attr`)
VALUES("C1Attr with auto create parent");

and it Works:

parent table:

child1 table:

Well you maybe can not do this automatically if the parent contains attributes that you have to set to a non default value. But you have the opportunity to set it also afterwards with an update if it is a NOT NULL value.

Auto delete parent and preventing deletion of child if parent is referenced

actually we can delete a child without any problem:

DELETE FROM `inheritance-example`.`child1` WHERE `parent_id`='3';

so we get:

foo_parent table

parent table:

child1 table:

This database is semantically inconsistent since parent_id=3 is only data trash and should be deleted. Even worse there exist a reference to it which is now semantically invalid!

So lets add these triggers:

CREATE DEFINER = CURRENT_USER TRIGGER `inheritance-example`.`child1_AFTER_DELETE` AFTER DELETE ON `child1` FOR EACH ROW
    BEGIN
        -- directly delete the parent, since it do not contain any data.
        DELETE FROM `parent` 
            WHERE `parent_id` = old.`parent_id`;
    END

CREATE DEFINER = CURRENT_USER TRIGGER `inheritance-example`.`child2_AFTER_DELETE` AFTER DELETE ON `child2` FOR EACH ROW
    BEGIN
        -- directly delete the parent, since it do not contain any data.
        DELETE FROM `parent` 
            WHERE `parent_id` = old.`parent_id`;
    END

let us delete something again:

DELETE FROM `inheritance-example`.`child1` WHERE `parent_id`='2';

we get:

foo_parent table

parent table:

child1 table:

Fine the corresponding row in parent was automatically removed. So there remains no data trash, which you can forget to delete.

So lets delete the last row:

DELETE FROM `inheritance-example`.`child1` WHERE `parent_id`='0';

which results in an error:

Executing:
DELETE FROM `inheritance-example`.`child1` WHERE `parent_id`='0';

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1451: 1451: Cannot delete or update a parent row: a foreign key constraint fails (`inheritance-example`.`foo_parent`, CONSTRAINT `fk_foo_parent_parent1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`parent_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
SQL Statement:
DELETE FROM `inheritance-example`.`child1` WHERE `parent_id`='0'

MySQl will abort and restore to complete operation. No row will be affected, also the child1 WHERE parent_id='0' row! This makes sure that you cannot delete a child row, where the parent row will be referenced by foreign key. And your database keeps complete consistent! This solves your last point Nr. 5.

final comment

There is another tutorial which explains you the function of a discriminator on a students/teacher/parents example. But instead of a ENUM they use an additional table.

I have also not to experience to say if the methods described here are bad or good practice, but it works.

I have also attached the SQL schema with the triggers if you want to experiment a bit. You need MySQL Workbench to edit the model, but I have also added the scheme create SQL statements.