Re: Inheritance mapping in a RDBMS

Maarten.van.der.Peet_at_liposuctie4all.nl.invalid (Maarten van der Peet) wrote in message news:<1g7tdnt.95hic8i9a3dhN%Maarten.van.der.Peet_at_liposuctie4all.nl.invalid>...
> 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])

If you choose this approach I would suggest an attribute in the "supertype", describing the type, something like:

create table soundcarrier (
  id ...,
  ...
  carrier_type ...
  primary key (id),
  check (carrier_type in ('record', ...)   unique (id, carrier_type)
)

create table record (
  id ...,
  ...
  carrier_type ... default 'record'
  primary key (id)
  foreign key (id, carrier_type) references soundcarrier(id, carrier_type)
  check (carrier_type = 'record')
)

[...]

HTH
/Lennart Received on Tue Jan 20 2004 - 06:30:04 CET