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.

On Thu, Dec 02, 2004 at 10:53:37PM -0500, Berend Tober wrote:

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 ...

Who would that be? Because I've always thought that most people
(everyone?) think of it as a bug that nobody has bothered to fix. Not
that the fix is easy, mind you ...

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Porque francamente, si para saber manejarse a uno mismo hubiera que
rendir examen... �Qui�n es el machito que tendr�a carnet?" (Mafalda)

On Thu, Dec 02, 2004 at 10:53:37PM -0500, Berend Tober wrote:

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 ...

Who would that be? Because I've always thought that most people
(everyone?) think of it as a bug that nobody has bothered to fix. Not
that the fix is easy, mind you ...

Sorry, I can't name names from recollection. One of the posts I read from
a few years ago was prefaced with some comment like "I'm not sure its
really a bug...but" and some talk about "the SQL Standard". My personal
take-away was that the behavior was not going to be fixed in the near term
and that I could not count on the behavior I expected, so I abandoned the
use of inheritance and implemented as described. And don't say "fix it
yourself" because I might be inclined to try, if I had the appropriate
skills.

On Fri, Dec 03, 2004 at 08:24:38AM -0500, Berend Tober wrote:

On Thu, Dec 02, 2004 at 10:53:37PM -0500, Berend Tober wrote:

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 ...

Who would that be? Because I've always thought that most people
(everyone?) think of it as a bug that nobody has bothered to fix. Not
that the fix is easy, mind you ...

Sorry, I can't name names from recollection.

Sorry, that comment was not actually meant to make you name someone, it
was a manner of speaking.

My personal take-away was that the behavior was not going to be fixed
in the near term

The problem is that no developer is interested in fixing it (it's "low
on everyone's to-do list"). But I think you would have a hard time
finding someone to defend the current behavior as desirable.

And don't say "fix it yourself" because I might be inclined to try, if
I had the appropriate skills.

I don't see that as a problem ... in fact, that's how this project
works. If you are annoyed with the current situation, you are welcome
to change it. Postgres _is_ open source after all, unlike some other
database system which claims the name but disdains the methodology ...

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Saca el libro que tu religi�n considere como el indicado para encontrar la
oraci�n que traiga paz a tu alma. Luego rebootea el computador
y ve si funciona" (Carlos Ducl�s)

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

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

...have a Resource table and a Car table
and a ResCar many-to-many relation.

I don't think you need the ResCar table. The Car table defines a
many-to-many relation with Appointment. As does the Resource table. The
Car table contains a subset of rows from the Resource table.

...it's not extensible. ...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.

Resource will give you all of the resources associated with an
Appointment, ...

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

...but you do have to join with the specialized resource entities in any
case, i.e., Car, Driver, and Room, to get the details pertaining to those
resources. You'll probably use a UNION to get everything, and it will
probably have NULL in some columns, since the attributes of a Car and a
Driver are different.

...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.

Indeed, but you'll have to modify the data base and program anyway to
handle the attributes of Room, say, as they differ from the Car and Driver
resources.

Using INHERITed tables simplifies this a lot.

Indeed, if inheritance worked right, but you don't want to wait for that,
do you?

program only needs to join Appointment with Resource
table and get the oid of the actual INHERITing table,
which contains the matching row.

I think you can still do that, since you don't need the ResCar and ResRoom
relations.

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

The context in which I have done something like this, where suppliers and
customers are both specialized types of organizations looks like:

CREATE TABLE organization
(
organization_pk serial,
organization_name varchar(128) NOT NULL,
internet_domain varchar(64),
tax_id_no varchar(12),
CONSTRAINT organization_pkey PRIMARY KEY (organization_pk)
) WITHOUT OIDS;

CREATE TABLE customer
(
customer_pk int4 NOT NULL,
customer_identifier varchar(18),
referral_customer bpchar(1) DEFAULT 'N',
customer_approval_status_pk int4 DEFAULT 0,
CONSTRAINT customer_pkey PRIMARY KEY (customer_pk),
CONSTRAINT organization_fkey FOREIGN KEY (customer_pk) REFERENCES
organization (organization_pk),
CONSTRAINT customer_approval_status_fkey FOREIGN KEY
(customer_approval_status_pk) REFERENCES customer_approval_status
(customer_approval_status_pk)
) WITHOUT OIDS;

CREATE TABLE supplier
(
supplier_pk int4 NOT NULL,
supplier_identifier varchar(18),
supplier_approval_status_pk int4 NOT NULL DEFAULT 0,
supplier_rating_pk int4 DEFAULT 0,
CONSTRAINT supplier_pkey PRIMARY KEY (supplier_pk),
CONSTRAINT organization_fkey FOREIGN KEY (supplier_pk) REFERENCES
organization (organization_pk),
CONSTRAINT supplier_approval_status_fkey FOREIGN KEY
(supplier_approval_status_pk) REFERENCES paid.supplier_approval_status
(supplier_approval_status_pk)
) WITHOUT OIDS;

Organization defines the common attributes to both suppliers and
customers, but customer and suppliers each have different additional
attributes. Note also that an organization can be both a customer and a
supplier, and it will have the same primary key in all tables:

Now this is different from your situation in that I don't have the
equivalent of your Appointment table, but I think you should be able to
make this work. My organization table would be your Resource, and my
customer and supplier would be your car and driver. All three tables would
have foreign key reference to Appointment.

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:

On Fri, Dec 03, 2004 at 08:24:38AM -0500, Berend Tober wrote:

My personal take-away was that the behavior was not going to be fixed
in the near term

The problem is that no developer is interested in fixing it (it's "low
on everyone's to-do list").

Not even that so much as that fixing it looks very hard, and there is a
great deal of other stuff to do that yields more bang for less work.

regards, tom lane

I understand your bang theory perfectly :) I just
wanted to know if there were plans to fix this.

I just looked at the TODO list at
http://developer.postgresql.org/todo.php and found the
section with inherited ref. constraints in the Indexes
section.

On the TODO page it says: "A hyphen, "-", marks
changes that will appear in the upcoming 8.1
release.", but none of the items are marked with a
hyphen. I guess this TODO page needs to be updated.

Thanks.

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:

On Fri, Dec 03, 2004 at 08:24:38AM -0500, Berend

Tober wrote:

My personal take-away was that the behavior was

not going to be fixed

in the near term

The problem is that no developer is interested in

fixing it (it's "low

on everyone's to-do list").

Not even that so much as that fixing it looks very
hard, and there is a
great deal of other stuff to do that yields more
bang for less work.

regards, tom lane

__________________________________
Do you Yahoo!?
The all-new My Yahoo! - What will yours do?
http://my.yahoo.com

Alec Swan <aukcioner@yahoo.com> writes:

On the TODO page it says: "A hyphen, "-", marks
changes that will appear in the upcoming 8.1
release.", but none of the items are marked with a
hyphen. I guess this TODO page needs to be updated.

It's perfectly up to date ... nothing's been done for 8.1 yet ;-)

regards, tom lane

On Fri, Dec 03, 2004 at 10:02:07AM -0800, Alec Swan wrote:

On the TODO page it says: "A hyphen, "-", marks
changes that will appear in the upcoming 8.1
release.", but none of the items are marked with a
hyphen. I guess this TODO page needs to be updated.

Development for 8.1 hasn't started yet ... items which had hyphens were
just removed because they are going to appear in 8.0.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
Jason Tesser: You might not have understood me or I am not understanding you.
Paul Thomas: It feels like we're 2 people divided by a common language...

Berend,

thanks for posting a part of your schema. In OO terms
you used incapsulation by providing a reference from
customer table to organization table instead of
inheritance. This makes sense.

But again, in order to find a supplier represented by
an organizaion record, your business layer would
probably join Organization with Customer and get an
empty result set. Then Organization will be joined
with Supplier and the desired record will be found.
What if you add 20 different types of organizations?
You will need to change your business layer to work
with the new 20 tables.

Now, suppose Customer and Supplier were inheriting
from Organization. Then your business code would find
an Organization record and the table name of the
actual table (Supplier in this example) where the
record is stored. Then a simple query would be run on
the Supplier table to find the actual supplier record.
The same process would apply if you add 20 more types
of organizations.

I normally use an OR-mapping tool, say Hibernate, in
my code. Mapped classes get generated automatically
based on the database content. So, if I add 20 tables,
I get extra 20 classes auto-generated. And then I map
them via xml files to the actual database tables.

Note that in the business layer you can just have a
simple method findOrganization(organization_pk), which
will have Organization return type (which is mapped to
the Organization table), but can actually return
objects of types Customers or Suppliers, or any of
those 20 classes that were auto-generated by the tool.

So, the only changes to the code required to handle
the new 20 tables would be on the data layer side and
will be auto-generated by the OR-mapping tool.

Sorry for taking this discussion in a different
direction, but I thought I'd share the practices that
I use in my code.

We can take this discussion out of this mailing list
if needed.

Thanks.

--- Berend Tober <btober@computer.org> wrote:

...have a Resource table and a Car table
and a ResCar many-to-many relation.

I don't think you need the ResCar table. The Car
table defines a
many-to-many relation with Appointment. As does the
Resource table. The
Car table contains a subset of rows from the
Resource table.

...it's not extensible. ...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.

Resource will give you all of the resources
associated with an
Appointment, ...

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

...but you do have to join with the specialized
resource entities in any
case, i.e., Car, Driver, and Room, to get the
details pertaining to those
resources. You'll probably use a UNION to get
everything, and it will
probably have NULL in some columns, since the
attributes of a Car and a
Driver are different.

...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.

Indeed, but you'll have to modify the data base and
program anyway to
handle the attributes of Room, say, as they differ
from the Car and Driver
resources.

Using INHERITed tables simplifies this a lot.

Indeed, if inheritance worked right, but you don't
want to wait for that,
do you?

program only needs to join Appointment with

Resource

table and get the oid of the actual INHERITing

table,

which contains the matching row.

I think you can still do that, since you don't need
the ResCar and ResRoom
relations.

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

Do

you?

The context in which I have done something like
this, where suppliers and
customers are both specialized types of
organizations looks like:

CREATE TABLE organization
(
organization_pk serial,
organization_name varchar(128) NOT NULL,
internet_domain varchar(64),
tax_id_no varchar(12),
CONSTRAINT organization_pkey PRIMARY KEY
(organization_pk)
) WITHOUT OIDS;

CREATE TABLE customer
(
customer_pk int4 NOT NULL,
customer_identifier varchar(18),
referral_customer bpchar(1) DEFAULT 'N',
customer_approval_status_pk int4 DEFAULT 0,
CONSTRAINT customer_pkey PRIMARY KEY
(customer_pk),
CONSTRAINT organization_fkey FOREIGN KEY
(customer_pk) REFERENCES
organization (organization_pk),
CONSTRAINT customer_approval_status_fkey FOREIGN
KEY
(customer_approval_status_pk) REFERENCES
customer_approval_status
(customer_approval_status_pk)
) WITHOUT OIDS;

CREATE TABLE supplier
(
supplier_pk int4 NOT NULL,
supplier_identifier varchar(18),
supplier_approval_status_pk int4 NOT NULL DEFAULT
0,
supplier_rating_pk int4 DEFAULT 0,
CONSTRAINT supplier_pkey PRIMARY KEY
(supplier_pk),
CONSTRAINT organization_fkey FOREIGN KEY
(supplier_pk) REFERENCES
organization (organization_pk),
CONSTRAINT supplier_approval_status_fkey FOREIGN
KEY
(supplier_approval_status_pk) REFERENCES
paid.supplier_approval_status
(supplier_approval_status_pk)
) WITHOUT OIDS;

Organization defines the common attributes to both
suppliers and
customers, but customer and suppliers each have
different additional
attributes. Note also that an organization can be
both a customer and a
supplier, and it will have the same primary key in
all tables:

Now this is different from your situation in that I
don't have the
equivalent of your Appointment table, but I think
you should be able to
make this work. My organization table would be your
Resource, and my
customer and supplier would be your car and driver.
All three tables would
have foreign key reference to Appointment.

__________________________________
Do you Yahoo!?
Send a seasonal email greeting and help others. Do good.
http://celebrity.mail.yahoo.com

We are working on a PostgresSQL KB. Soon we are going to move the FAQ and
Information as such to the KB

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alec Swan
Sent: Friday, December 03, 2004 7:02 PM
To: Tom Lane
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] table inheritance and DB design

I understand your bang theory perfectly :) I just wanted to know if there
were plans to fix this.

I just looked at the TODO list at
http://developer.postgresql.org/todo.php and found the section with
inherited ref. constraints in the Indexes section.

On the TODO page it says: "A hyphen, "-", marks changes that will appear in
the upcoming 8.1 release.", but none of the items are marked with a hyphen.
I guess this TODO page needs to be updated.

Thanks.

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:

On Fri, Dec 03, 2004 at 08:24:38AM -0500, Berend

Tober wrote:

My personal take-away was that the behavior was

not going to be fixed

in the near term

The problem is that no developer is interested in

fixing it (it's "low

on everyone's to-do list").

Not even that so much as that fixing it looks very hard, and there is
a great deal of other stuff to do that yields more bang for less work.

regards, tom lane

__________________________________
Do you Yahoo!?
The all-new My Yahoo! - What will yours do?
http://my.yahoo.com

---------------------------(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