Re: Inheritance mapping in a RDBMS

Maarten.van.der.Peet_at_liposuctie4all.nl.invalid (Maarten van der Peet) wrote:
>Hello there,
>
>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?
>

There is no theoretical reason to support the second solution. There is only performance/ease of use reasons not to use the first one. You are swapping database enforced integrity constraints for application enforced integrity constraints (through triggers). The performance justifications usually lose their value when compared to the maintenance expense of adding a new soundcarrier subtype.

>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:59:01 CET