Re: table inheritance and DB design - Mailing list pgsql-general
From | Alec Swan |
---|---|
Subject | Re: table inheritance and DB design |
Date | |
Msg-id | 20041203153800.56750.qmail@web53410.mail.yahoo.com Whole thread Raw |
In response to | Re: table inheritance and DB design ("Berend Tober" <btober@computer.org>) |
Responses |
Re: table inheritance and DB design
("Joshua D. Drake" <jd@commandprompt.com>)
Re: table inheritance and DB design ("Berend Tober" <btober@computer.org>) |
List | pgsql-general |
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@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
pgsql-general by date: