Re: Separate foreign keys with shared ID space

We have a similar design issue in our DB.

We implemented this kind of super-class/sub-class design using views.

We created views with joins with the parent & child tables and 'instead of' triggers. The users can only see the views though they see them as if they were tables (by using synonyms).

In your example you could create three views: business_customer_vw, home_customer_vw, university_customer_vw. They share the common attributes from
the customer table and the respective specific attributes from the business_customer, home_customer, university_customer tables. Optionally, you can create synonyms business_customer, home_customer, university_customer for the views.

The underlying tables data are managed by the instead of triggers.

I can only say It works for us...

Hope this helps.

Carlos.

lennart_at_kommunicera.umea.se (Lennart Jonsson) wrote in message news:<6dae7e65.0407280534.75936feb_at_posting.google.com>...
> robertbrown1971_at_yahoo.com (Robert Brown) wrote in message news:<240a4d09.0407271513.43dfecc7_at_posting.google.com>...
> > Let's say I have a type hierarchy: (just an example)
> >
> > the general entity customer:
> >
> > CREATE TABLE customer(customer_id int, customer_name varchar(250),
> > customer_type int)
> >
> > three specific customer subtypes: 1 - business, 2 - home, 3 -
> > university
> >
> > represented by the following three tables (being subtypes, they share
> > ID space for customer_id)
> >
> > CREATE TABLE business_customer(customer_id int, business_sector int,
> > ... )
> > CREATE TABLE home_customer(customer_id int, household_income_bracket
> > int, ...)
> > CREATE TABLE university_customer(customer_id int, number_students int,
> > ...)
> >
> > Is it possible to make a foreing key constraint that says:
> >
> > the customer_id in busineness_customer table must reference only those
> > customer_id in the customer table where customer_type == 1?
> >
> > the customer_id in home_customer table must reference only those
> > customer_id in the customer table where customer_type == 2?
> >
> >
> > Thanks!
> >
> > - Robert
>
> Something like:
>
> CREATE TABLE customer (
> customer_id int not null primary key,
> customer_name varchar(250) not null,
> customer_type int not null check (customer_type in (1,2,3)),
> unique (customer_id, customer_type )
> );
>
> CREATE TABLE business_customer (
> customer_id int not null primary key,
> customer_type int not null default 1 check (customer_type = 1),
> ...
> foreign key (customer_id, customer_type)
> references customer (customer_id, customer_type)
> );
>
> etc.
>
> HTH
> /Lennart
Received on Thu Jul 29 2004 - 03:53:52 CDT