How to enforce referential integrity on Single Table Inheritance?

Question

I've read some of Bill Karwin's answers about single table inheritance and think this approach would be good for the setup I am considering:

Playlist
--------
id AUTO_INCREMENT
title

TeamPlaylist
------------
id REFERENCES Playlist.id
teamId REFERENCES Team.id

UserPlaylist
------------
id REFERENCES Playlist.id
userId REFERENCES User.id

PlaylistVideo
-------------
id
playlistId REFERENCES Playlist.id
videoId REFERENCES Video.id

All the CASCADE options are set to DELETE which will work correctly for when a Playlist is deleted, however, what happens if a User or Team is deleted?

ie. If a User is deleted, the rows in UserPlaylist will be deleted but the referenced rows in Playlist and PlaylistVideo will remain. I thought about enforcing this as a TRIGGER AFTER DELETE but there is no way of knowing if the delete request came about because the Playlist was deleted or if the User was deleted.

What is the best way to enforce integrity in this situation?

Edit (Provided ERD)

Epigraphy

I don't understand how could UserPlaylist be an inheritance of Playlist. Shouldn't it be a relation table instead? – Azalea

I don't understand your question. UserPlaylist is related to Playlist just that the id comes from Playlist.id. Here are some more questions about Single Table Inheritance - https://mcmap.net/q/261453/-foreign-key-to-one-of-many-tables – Epigraphy

the whole reason you don't want a user being deleted to take out playlist and playlist video rows is because they could also referenced by other userplaylist or teamplaylist records. – Shizukoshizuoka

Thanks for your comments but I get the impression that I am not being understood in what I want to accomplish. A Playlist will reference just ONE of UserPlaylist or TeamPlaylist. Hence why Playlist.id is AUTO_INCREMENTed but UserPlaylist.id and TeamPlaylist.id are not. If you need further clarification, please let me know and I'll edit my question. @Azalea - can you provide an example model setup of what you think it should look like? That would help me in trying to understand what you mean. – Epigraphy

You could present your question with an ERD much simpler and much more accurate. Perhaps using MySQL Workbench. – Judsen

Thanks for the suggestion Mehran. I've provided an ERD. – Epigraphy

Could you describe what is a userplaylist? I really think you're not in an inheritance case. – Azalea

A UserPlaylist is just a Playlist except it belongs to a User while a TeamPlaylist belongs to a team. I wanted to try to avoid the situation of creating an exclusive arc where the Playlist model would have either a) a userId and teamId field but for each record one would be used and the other would be NULL or b) an entityId field and an entityType with the entityType set to 'User' or 'Team' – Epigraphy

What you're describing isn't single table inheritance, it's class table inheritance (i.e. represents a class hierarchy in an RDBMS with one table per class). With single table inheritance all the playlist columns would be contained in one table (in addition to a type/discriminator column that contains "user" or "team"). – Holey

Answer 1

In my view, the problem is that your User and Team tables are the ones that should have a supertype table (such as Party), not the Playlist tables.

As you've pointed out, doing your "table inheritance" on playlists comes with penalties when trying to figure out what to delete. All those problems go away when you move the inheritance up to the user/team level.

You can see this answer for more detail about supertyping/subtyping.

I'm sorry to not supply code as I don't know the MySQL syntax by heart.

The basic concept is that the supertype table allows you to implement a database kind of polymorphism. When the table you're working with needs to link to any one of a group of subtypes, you just make the FK point to the supertype instead, and this automatically gets you the desired "only a one of these at a time" business constraint. The super type has a "one-to-zero-or-one" relationship with each of the subtype tables, and each subtype table uses the same value in its PK as the PK from the supertype table.

In your database, by having just one Playlist table with an FK to Party (PartyID), you have easily enforced your business rule at the database level without triggers.

Scree

This is a good point and a good idea. In this system, though, a Team is much different from a User. eg. A User can login, a Team cannot. This is an existing system in production (while the Playlists are new). If I were building it from scratch, this would probably be the way to go. But I need to think it through if it would be worthwhile to change to this setup (ie. Teams and Users would currently have overlapping IDs). But thanks for the suggestion. – Epigraphy

That's what the subtype tables are for: the ways that they are different. The real problem in changing is updating the IDs for one of the sets, with dropping and recreating constraints. But I think it's worthwhile. :) – Scree

Thanks for selecting my answer! What led you to decide this was the way to go? – Scree

In this database, I also have the tables FollowUser, FollowTeam, FollowSport. I would be able to replace these with a single table with this setup. If I don't change this situation may come up again in the future. Plus, we are significantly altering the site at the moment, so this is probably the best time to make this change. Updating the ID's is a little painful but not actually that bad once I thought about it. – Epigraphy

Let me know how it works! I am building my own super- and sub-classed tables at the moment. – Scree

I sure wish downvoters would comment. I never revenge downvote--I just want to understand what the quibble is! – Scree

Answer 2

What you can do is implement triggers on your Users and Team tables that execute whenever rows get deleted from either:

User table:

DELIMITER $$
CREATE TRIGGER user_playlist_delete 
BEFORE DELETE ON User FOR EACH ROW
BEGIN
    DELETE a FROM Playlist a
    INNER JOIN UserPlaylist b ON a.id = b.id AND b.userId = OLD.id;
END$$
DELIMITER ;

Team table:

DELIMITER $$
CREATE TRIGGER team_playlist_delete 
BEFORE DELETE ON Team FOR EACH ROW
BEGIN
    DELETE a FROM Playlist a
    INNER JOIN TeamPlaylist b ON a.id = b.id AND b.teamId = OLD.id;
END$$
DELIMITER ;

What these triggers will do is each time a record is deleted from one of these tables, a DELETE operation will automatically execute on the Playlists table using the id that's about to be deleted (via an inner join).

I have tested this and it works great.

Gauzy

brilliant solution. have not tested it myslef, but looks solid. +1 – Countryman

Good idea Zane. For some reason I had not thought about putting the trigger on the User or Team tables - only the UserPlaylist or TeamPlaylist tables. – Epigraphy

Answer 3

OK I see what you want here... what you want to do is run a query like

DELETE FROM playlist
WHERE       id 
NOT IN      (
    SELECT  id
    FROM    UserPlayList
    UNION
    SELECT  id
    FROM    TeamPlayList
)

after either a row is deleted from either users or teams

Shizukoshizuoka

Answer 4

The answer by Zane Bien is quite obvious & superb.But I have an idea for doing this without use of trigger because trigger has many problems.

Are you using any programming language ? If yes then,

Use a single transaction and make your database auto commit false

write a delete query for the referenced rows in Playlist and PlaylistVideo . Manually you have to write this query first by using that reference id(with where condition) and run it.

Now prepare another query for your main task i.e. delete the User, and the rows in UserPlaylist will be deleted automatically ( due to CASCADE DELETE option).Now run your second query and commit.

Finally make your transaction auto commit true.

It is working successfully, hope it will helpful.

Goldcrest