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 <[EMAIL PROTECTED]> 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 - [EMAIL PROTECTED] - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
begin:vcard
fn:Joshua Drake
n:Drake;Joshua
org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE
url:http://www.commandprompt.com
version:2.1
end:vcard
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
