Expressing an is-a relationship in a relational database

Question

I was wondering if there is a clean way to represent an is-a relationship as illustrated by this example:

This DB stores recording times for three types of programs: movies, game shows, drama. In an object oriented sense each of these is-a program. Each of these subclasses have different properties. Here are the tables (fk prefix indicates a foreign key):

movie
id
name
fkDirector

gameShow
id
name
fkHost
fkContestant

drama
id
name

In OO terms the record table would in sense look like this:

record
id
fkProgram
startTime
endTime

The only way I can think of doing this without violating the normal forms is to have three record tables namely recordMovie, recordGameShow, and recordDrama.

Is there a way to consolidate these tables into one without violating the principles of database normalization?

Here are some non-working examples to illustrate the idea:

program
id
fkMovie
fkGameShow
fkDrama

This table violates the first normal form because it will contain nulls. For each row only one of the 3 entries will be non null.

program
id
fkSpecific ← fkMovie OR fkGameShow OR fkDrama
fkType ← would indicate what table to look into

Here I will not be able to enforce referential integrity because the fkSpecific could potentially point to one of three tables.

I'm just trying to save the overhead of having 3 tables here instead of one. Maybe this simply isn't applicable to an RDB.

Answer 1

Why do you want to store all the data on a single table? They are clearly different entities. Your idea of a main Record table, with auxiliary recordMovie, recordGameShow, and RecordDrama.

To enforce the "is-a" relationship between the auxiliary tables and the main one, you need to do declare Record.id to be a foreign key in all these tables, and also add a constraint to it so it's unique - this enforces a one-to-one relationship which would convert these tables in extensions of the main one.

You'd also need to add a new field in the main Record table to indicate what kind of record it is (movie, game show, drama, something else?). This could be either a foreign key reference to yet another table (RecordTypes?) or a string (with a constraint defined over the values it can accept).