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

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