Re: Question regarding keyword checkboxes in database design

Hi Lynna,

Lynna Landstreet wrote:

>So today's conversion question, relates to the several lists of checkboxes
>in the current FMP database, for keywords under which the images, artists
>and exhibitions (it's for an art gallery) are categorized. In FMP, these are
>handled by using one field per *set* of checkboxes, with a value list
>associated with it that provides the different checkboxes or radio buttons
>in the layouts, but stores the data as one long string delimited by some
>weird character that doesn't display properly when you export the content as
>text.
>
So if I get this right, you want to state something like
"This and that Keyword is associated with this record".
If this is the case, I would propose this design:

We have a table "items", which contains the actual stuff,
then a table "keywords" which contains all the keywords.
Then you need a third table for example called "items_keywords".
This third table has at least 2 fields: item_id and keyword_id
(for simplicity I assume that the item table has a Primary Key
field called item_id).
So every entry in items_keywords describes an assiciation of an item
with a given keyword.
You could use this table  to specify even more information about this
association (you could, for example put a descriptive column there,
which lets you say that a given keyword is NOT there).

I guess this would solve your problems and leave a lot of
flexibility.

Cheers, Dani