Re: Question regarding keyword checkboxes in database

on 6/11/03 2:43 PM, Heath Tanner at heath(at)inligo(dot)com wrote:

> I don't know about PostgreSQL to tell you whether performance would
> be ultimately better with several small tables or two larger
> tables, but I can tell you that the "keywords" table and its
> accompanying join table are not going to be large enough to make
> this an issue at all (assuming your SQL is reasonable and the join
> column are indexed, of course). Solution #2 will definitely provide
> adequate (or better) performance.

So even if the join table ends up with something like 100,000 rows in it,
it's not going to cause much deterioration in performance as long as it's
indexed? Because right now, there are 1357 records in the three core tables
combined, and there are presently 99 keywords they can potentially be
categorized under, grouped into 9 sets. So there's a theoretical maximum of
134,343 entries that could go into the join table right now - in practice,
there won't be nearly that many, because nothing is going to have *all* the
keywords checked, but it's still going to be a pretty impressively large
table. And there's still 15 years worth of past exhibitions to enter, plus
new exhibitions will be entered on an ongoing basis. That's why I was
nervous about this option.

> Maybe there are some people who have used an approach like #1 with
> some success, but I generally try avoid creating database
> structures that must change when new data (new keywords) is
> introduced.

I guess I've been a bit spoiled because in FileMaker Pro, with its value
lists, new keywords are quite easy to add, but they won't necessarily be in
PostgreSQL, or at least not with this structure. I don't anticipate that new
keywords will be added very often - I've already entered ten years worth of
exhibitions and the current keyword list is the result of that, so I don't
think there will be too many themes or formats in the back catalogue that
won't have occurred during that time, but there could well be a few. Plus we
want this to be pretty much infinitely extensible into the future, so new
things could well be added then (given the frequency with which new ideas
crop up in the art world).

So maybe the join table idea is the better one of the two. It's just the
size issue that worries me. I know there's theoretically no limit to the
number of rows in a table, but in practice there's got to be a point when
performance degrades noticeably...

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