Re: table inheritance and DB design - Mailing list pgsql-general

Alec Swan wrote:

>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.
>
>
If I am reading what you are saying correctly,
it is called 5th normal form as is perhaps
the most extensible you can be.

http://www.bkent.net/Doc/simple5.htm#label4
http://www.datamodel.org/NormalizationRules.html


Sincerely,

Joshua D. Drake


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


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL