Re: table inheritance and DB design

Berend,

I understand your alternative design. So, you are
suggesting to have a Resource table and a Car table
and a ResCar many-to-many relation. This will work,
but it's not extensible. Suppose, my application needs
to find the resource that is assigned to an
appointment. My program will have to know all
many-to-many relations that map other tables to the
Resource table. It will then have to join Resource
table with each such relation, and once the match is
found join it with the actual resource table, in this
case Car. This approach will require me to run one
query per many-to-many relation in order to find the
final row.

This approach is rather slow. Moreover, if I want to
add another type of resource, say Room, I will have to
modify my program and make it join Resources, ResRoom,
and Room.

Using INHERITed tables simplifies this a lot. My
program only needs to join Appointment with Resource
table and get the oid of the actual INHERITing table,
which contains the matching row. The program can then
search that table to get the full data on the required
record. So, in this scenario only 2 queries are
required to find the desired information for each row.
Moreover, I won't have to modify my code once I add a
Room table.

I don't see any good alternative to this design. Do
you?

Does anyone know when ref. constraints will be
inforced on the INHERITing tables?

Thanks.

Alec

--- Berend Tober <btober(at)computer(dot)org> wrote:

> > 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.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

__________________________________
Do you Yahoo!?
Dress up your holiday email, Hollywood style. Learn more.
http://celebrity.mail.yahoo.com