Re: Question regarding keyword checkboxes in database

Lynna Landstreet wrote:

>Given that the keywords are grouped into sets (Medium, Subject, Theme,
>Processes, etc.), and that each image, artist or exhibition can have more
>than one keyword checked even within each set, I think I've narrowed down my
>choices to two:
>
>1. Separate tables for each keyword set, with the tables being the set name,
>the columns being the individual keywords (as boolean fields) plus a foreign
>key for the ID number of the artist/image/etc. as well as a one-letter
>identifier as to whether that ID belongs to an artist, and image or an
>exhibition, and the rows being the individual artists/images/etc. The
>primary key would be the combination of the ID and type columns, because
>while there might be two items with, say ID number 101, they'd have to be of
>different types - one an artist and one an exhibition, or something like
>that. So there would never be two instances of 101-A or 101-E.
>
>
[snip]

>
>2. A many-to-many relationship, with one big table of keywords, with the
>keywords being the rows rather than the columns, and the columns being the
>keyword name, which set it falls into, and an ID number as a primary key.
>Plus a second join table, with the rows being instances of keyword per item.
>
Actually I dont see how your first solution is easier to code.
Imagine Maintenance - you would have to change your DB structure to add
new keyword sets,
as well as change the front end!
I would keep things as general as possible, so that your structrue
imposes as little as possible
on your data.
In other words: you might need to think a bit more about solution 2,
but when you have it, you got many things at a time:
- less code than solution 1
- stable code (in slution 1 it will potentially grow)
- easy maintenance (a change of data is only a change of data and not a
change of structure)
- the good feeling to have come to a general solution.

I mean, its up to you, but if you want to do real programming, choose
the second solution :-)

BTW: Maybe you have a look in one of these very nice books about
programming:
"Code Complete"

(http://www.amazon.com/exec/obidos/tg/detail/-/1556154844/qid=1055404159/sr=8-2/ref=sr_8_2/002-7605764-8176851?v=glance&s=books&n=507846)
"The Pragmatic Programmer"

http://www.amazon.com/exec/obidos/tg/detail/-/020161622X/qid=1055404193/sr=1-2/ref=sr_1_2/002-7605764-8176851?v=glance&s=books

Cheers,
Dani