Database Design Theory?
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
CREATE TABLE apples
is_crisp BOOLEAN NOT NULL
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
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