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

Yes, that should be one table like

Programs:
   id,
   name,
   type_id,
   length,
   etc...

with a reference table for the type of program if there are other bits of data associated with the type:

ProgramType
   type_id,
   type_name,
   etc...

Like that.

Ron

Answer 2

This is a pretty standard problem faced by many people before, and all of the approaches you may consider have probably been done at one point.

A simple Google search comes up with some pretty good explanations of the pros and cons of each.

Answer 3

My first idea was also to use one Program table for movies, shows & drama's. Then add a ProgramType table and use a foreign key to it just like the parent post.

Other columns can be added such as fkDirector, fkMovie. Then add a constraint that when the ProgramType is a movie, fkDirector can not be null or when it's a show, fkHost can not be null.

This allows for easy lookup of all movies/shows/... recorded between start and enddate. Also makes sure all the data is filled in and the references are correct.

Anyone has a better idea?

Answer 4

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).

Answer 5

These are both good articles on the subject:
http://www.ibm.com/developerworks/library/ws-mapping-to-rdb/
http://www.agiledata.org/essays/mappingObjects.html

This what I will end up doing. My program table will simply be:

program
id

and then I will add fkProgram to each of the subclasses (drama, gameshow, and movie). This way the Program table will be an intermediate table between the subclasses. I can use a foreign key to the Program table to refer to an instance any of the sub-classes. This will allow me to have a single Record table and not violate any normal forms.

movie
id
fkProgram
name
fkDirector

record
id
fkProgram
startTime
endTime

Answer 6

Do a google search on "generalization specialization relational modeling".

The "gen-spec" model follows the same pattern as the "is-a" relationship.

For example, a car is a specialized vehicle. A truck is a different kind of specialized vehicle. A motorcycle is a third kind of specialized vehicle.

You should find plenty of articles.

Interestingly, if you just do a google search on "gen-spec" one of the top links is to a description of gen-spec modeling in Smalltalk.