Re: Question regarding keyword checkboxes in database

Many thanks for the replies to my question - I think a lot of it is more an
interface issue than a database structure issue, and needs to be addressed
with PHP when I start creating the front end. The important thing now is
having a structure that makes it as easy as possible to get to all the
information.

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.

Advantages: Fairly clear and easy to understand. Keeps tables smaller, as no
keyword table could have more entries than the total number of artists,
exhibitions and images combined, and most would have a lot less.

Disadvantages: More tables. Harder to add new keywords if needed (though new
ones won't need to be added very often).

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.

Advantages: Fewer tables. Easier to add new keywords.

Disadvantages: Coding for the way I want to display the keyword info
(grouped by set) probably a bit trickier. Tables much larger, especially the
join table, which could easily be five to ten times the total number of
artists, exhibitions and images combined (and there are already several
hundred items in each of those three files, with more coming, so this would
be many thousands of rows).

I suppose one of the issues I need to think of in deciding between these two
options is performance: I don't know PostgreSQL well enough to know whether
it's quicker to access several small tables, or one or two very large ones.
Anyone know? And can anyone see any other issues or problems I haven't
thought of here?

Thanks,

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