Re: table inheritance and DB design

> I am trying to create a database, which allows me to store appointment
> information. ...
>
> Now, I want to have several tables, say Car and Driver, which INHERIT from
> the Resource table. I also want AppRes table can enforce a ref. constraint
> on the Resource table. So, in the future I can add a Room table and be
> able to associate its records with an appointments via AppRes just by
> making the Room table inherit from the Resource table.
>
> I like this idea a lot, but I noticed that the current version of postgres

When I first read in the documentation about inheritance, I was pretty
excited, too,

> So, my first question is when FK constraints will be "fixed" to include
> children tables?

But after testing out some design alternatives, I really didn't like the
way it worked. And in researching for help (as you are now), I learned
that the unusual behavior (or at least the behavior that seems weird to
me) regarding relational integrity and uniquness constraints as been
around for a while, and some people actually think is is SUPPOSED to work
that way ...

> My second question is if there is a design, which will allow me to add
> different types of resources (Cars, Drivers, Rooms, etc) and have FK
> constraints enforced in AppRes table?

I found that I could do what I want using standard normalization
techniques, foreign key relationships, and on insert triggers.

The tables that you propose to inherit from Resources should just be
typical many-to-many relations that associate key values from Resources to
Appointments. Each of these tables will have foreign key references to a
mutually-exlusive subset of the rows in Resource depending on what
resource type the rows represent.

Resource will have a serial type primary key, and each of the
psuedo-"inherited" tables will have a before insert trigger that does an
insert into Resource and then takes the new serial primary key value from
the row added to Resource and uses that value in its own foreign key
reference to the Resource table in one column and assigns a foreign key
reference in its other column to the row in the Appointment table.