Re: Question regarding keyword checkboxes in database

Lynna,

> 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.

(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).

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.

> About the only circumstance under which I'd consider being normal a good
> thing. :-)

<chuckle>

> Oh, I don't know, maybe because -- no one's hiring Flash developers any
> more? *rolls eyes* At least half the web developers I used to know aren't
> even the field any more.

Yeah.

> 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.

--
-Josh Berkus
Aglio Database Solutions
San Francisco