Re: Question regarding keyword checkboxes in database

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

> Not real sure of your schema, but you might also consider a single keyword
> table with a "keyword_type" column. Not only would this keep your joins
> simple, but it would allow you to add an new keyword type in the future.

Hmmm, yes, that might be better. Haven't started populating it yet (it'll
take me a while to export and clean up all the data from FileMaker Pro), so
I can still make changes to the structure pretty easily.

But I've just run into a new wrinkle -- up until now I was mainly
concentrating on how to organize the keywords themselves, and now I'm
realizing I overlooked something when adding in the items (artist,
exhibitions, etc.) that the keywords pertain to. I had used two columns to
indicate that in the join table, one for item_id (the id number from the
artists, images or exhibitions table) and one for item_type, a single
character that indicated which of the three it was. But when I was checking
to make sure I'd added all the right constraints, I realized that I couldn't
add a foreign key constraint to item_id if it might be referring to an id
number in any of *three* other tables rather than one specific one. And
without that it isn't going to be able to check referential integrity.

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?

> Either way, another victory for normalization! Wahoo!

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

>> WRT "if you want to do real programming" - that made me smile, because the
>> fundamental problem behind this and nearly all my questions on this list and
>> pgsql-general is that I'm *not* a real programmer! I'm a designer who has
>> had to expand into more technical areas because the current state of the web
>> industry makes it a necessity -
>
> <grin> At least you're facing up to reality ... I have several friends who
> keep whining "but why can't I just do Flash the way I did in 1999?"

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.

> It's not just Web work, btw. I get *lots* more work than most of my
> programmer friends because I know networks, accounting and litigiation
> support as well as code; it's just not enough to have just one skill anymore.

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

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