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

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