> I'd do something like this:
>
> CREATE TABLE animal_type (
> animal_name TEXT PRIMARY KEY,
> CHECK(animal_name = trim(animal_name))
> );
>
> /* Only one of {Wolf,wolf} can be in the table. */
>
> CREATE UNIQUE INDEX just_one_animal_name
> ON animal_type(LOWER(animal_name));
>
> CREATE TABLE predator (
> animal_name TEXT NOT NULL
> REFERENCES animal_type(animal_name)
> ON DELETE CASCADE,
> PRIMARY KEY(animal_name)
> );
>
> CREATE TABLE prey (
> animal_name TEXT NOT NULL
> REFERENCES animal_type(animal_name)
> ON DELETE CASCADE,
> PRIMARY KEY(animal_name)
> );
>
> CREATE TABLE mauling (
> id SERIAL PRIMARY KEY,
> attacker_id INTEGER NOT NULL REFERENCES predator (animal_type_id),
> victim_id INTEGER NOT NULL REFERENCES prey (animal_type_id),
> attack_time TIMESTAMP WITH TIME ZONE NOT NULL
> );
Just to add to David's idea, I would create two update-able views that joined animal to predator
and another for animal to prey. This way, you only have to insert/update/delete from 1
update-able view rather than two tables.
Of course, I am still waiting for the future version of postgresql that will handle this
functionality seamlessly using table inheritance. :-)
Regards,
Richard Broersma Jr.