Database Design Theory?

Gabriel Gunderson gabe at gundy.org
Tue Nov 8 18:58:17 MST 2005


On Tue, 2005-11-08 at 18:25 -0700, Ross Werner wrote:
> Let's say you have a table "foo" that needs to reference the "fruit" 
> table. So you have a "fruit_id" column in table "foo". Now, each
> "fruit" can be either an "orange" or a "banana" but not both. Also,
> the columns required for each table are vastly different--an entirely
> different set for "orange"s than "banana"s. What's the best way to
> model this in the database?
> 
> Basically in object-oriented terms, "orange" and "banana" both extend
> the same abstract class "fruit".
> 
> What I typically do is have a "fruit" table, an "orange" table, and a 
> "banana" table with the primary key of the "orange" table also being a
> foreign key to the "fruit" table, and ditto with "banana".
> Unfortunately this doesn't technically prevent (from the database
> standpoint) a particular fruit instance from being both an orange
> *and* a banana, and it's a headache when trying to figure out the
> types and information of a list of fruits.
> 
> Anyone have a better way?

Ah, I was hoping I could bring up PostgreSQL (8.1 -just out- makes PG
sweeter then ever)!

Now, I'm not saying you should do this, but in many cases it works...

CREATE TABLE fruits
(
    name     VARCHAR(20) NOT NULL,
    is_yummy BOOLEAN NOT NULL
);

CREATE TABLE oranges
(
    is_hard_to_peel BOOLEAN NOT NULL
) INHERITS(fruit);

CREATE TABLE apples
(
    is_crisp BOOLEAN NOT NULL
) INHERITS(fruit);

Now the oranges and apples tables have all the fields that the fruits
table has and their own too.  If you select from "fruits" you get the
"fruit" part of the apples and oranges *and* anything inserted directly
into the fruits table as well.

I'll let the rest of you figure out why you shouldn't do this type of
thing.  As for me, I'll continue to do it where it makes things easy and
clean.

Gabe

P.S.  I'm not worried about getting locked into PG by using this
non-standard SQL.  What could be more fun?




More information about the PLUG mailing list