Lynna,
> 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...
Sure. But 130,000 rows in a two-INT-column table is not going to strain even
a workstation converted to server usage. I have an application of similar
size that does not just keyword matching, but relative scoring and weighting
based on data in external tables, and its performance is perfectly acceptable
( < 15 seconds including HTML rendering) on an IMac.
Second, remember that raw query performance is not the only kind of
performance you need to worry about. Any not-normalized database structure
carries with it a maintainence penalty of extra DBA time, down time, and
troubleshooting; often these issues far overshadow (especially in terms of
cost) any extra query time required for the normalized structure -- provided
that there is any extra, which often there's not.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
