Inheritance mapping in a RDBMS

What I am trying to do is, map an inheritance hierarchy in a relational database system.

Example:
Let's say you want to describe in a relational database, a collection of soundcarriers that consists of records, cassetes, dat-tapes en cds. (extremely simplified example, possible domain values between brackets)

Should I use a couple of tables:

soundcarrier (id, content [music, speech], label_description) record (id, material [aluminium, glass, vinyl], speed[33,45,78]) cassete (id, length [45, 90, 120])
dat (id, brand[sony, maxwell], sample_freq[44,48]) cd (id, brand [sony, maxwell])

So an:
select * from soundcarrier, record where soundcarrier.id = record.id; gives me all the record information

Or should I use one big table and an extra attribute type. (maybe that one isn't even necessary because you can "see" what columns are filled?)

soundcarrier (id, type[record, cassette, dat, cd], content,material, speed, size, length, datspeed, samplefreq, cdbrand)

and then
select id, content, label_description, speed from soundcarrier gives me all the record information.

A disadvantage seems to me that there are a lot of fields that are not applicable for one soundcarrier. The first one seems cleaner to me but what would be the theoretical reason for not using the second solution?

So my questions are:
what's the best way to map an inheritance hierarchy to a relational database? What are the advantages or disadvantages of the different solutions? Maybe there are some other solutions?

Thanks for your time.

Maarten Received on Mon Jan 19 2004 - 15:06:58 CET