Re: Question regarding keyword checkboxes in database

on 6/12/03 7:16 PM, Josh Berkus at josh(at)agliodbs(dot)com wrote:

>> Is there some way to indicate that a foreign key can apply to any of more
>> than one table? The book I'm mostly working from doesn't say anything about
>> that, but it does say that foreign key constraints are an advanced topic and
>> they're only covering the basics of them. Or should I be using three join
>> tables, one each for artists, exhibitions and images?
>
> Congratulations! You've just run into one of the failures of the SQL
> Standard. What you want is called a "distributed key", and it is a concept
> well-supported in Relational Calculus, but for some reason omitted from the
> SQL standard.

Damn. Calculus was a long, long time ago for me, but I thought this sounded
like something that *should* be possible. However, "should" does not mean
"is". :-/

> (And, BTW, your book is WRONG. Foriegn Keys are *not* and "advanced topic";
> they are fundamental and nobody should design a database without
> understanding them).

They're fundamental to using the referential integrity feature of
PostgreSQL, right? Or so it would appear to me, anyway... FWIW, the book
("Beginning Databases in PostgreSQL" by Richard Stones & Neil Matthew) does
state that they're important, but they only go over the basic aspects of
them (adding the REFERENCES constraint to tables or columns and a couple of
options you can include with it) and say that their *full* potential is
advanced, so I did at least get to learn a little bit about them. But more
would have been better.

> I've had to handle this before. The approach is to do a "do it yourself"
> key, consisting of:
>
> ---optional, but a good idea-------------------------------------
> 1) create a sequence called, for example "aie_sq"
> 2) Alter the Artists, Exhibitions, and Images tables so they all draw on this
> same sequence for their unique ids (NEXTVAL('aie_sq')). This will ensure
> that all IDs are unique between the 3 tables.
> -----------------------------------------------------------------------
> 3) Create a unique 3-column index in the join table.
> 4) Create BEFORE INSERT OR UPDATE triggers on the join table which checks
> that the id exists in one of the 3 tables.
> 5) Create BEFORE or AFTER UPDATE OR DELETE triggers on each of the 3 tables
> which check dependant records in the join table and take approprate action.
>
> If this is too much for you, then just create 3 seperate join tables. In
> your situation, I'm not sure there's that much advantage in doing it the more
> sophisticated way.

It's not necessarily too much for me -- I generally like doing things the
harder but more elegant way rather than the easier but sloppier way. But in
this particular case, the decision is complicated by the fact that there are
hundreds of existing records needing to be imported into the database once I
get the structure finalized, all of which have existing ID numbers and
existing relationships between them. So if I let PostgreSQL renumber all the
records according to a new sequence, I'd have to manually recreate all the
links between them, which given there are something like 1300 records in
total, would seriously suck. So I think I'm stuck with separate join tables.

However, this does sound like an excellent way of dealing with this sort of
problem if one is creating a new database from scratch, and I will
definitely save this message for future reference because I could run into
this situation again on future projects.

>> Yes, the latest revision of my resume isn't as web-focussed as it used to
>> be, and highlights non-tech skills like writing, editing and research as
>> well as the technical stuff. And I suppose when this contract ends, I'll be
>> able to not only add what I've learned about PostgreSQL and PHP, but also
>> arranging veggies and crackers artistically for gallery openings. :-)
>
> Well, contact me when you're done; I sometimes get non-profit referrals that I
> don't have time for.

Cool - thanks!

Lynna
--
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org