On Wednesday, June 11, 2003, at 02:19 PM, Lynna Landstreet wrote:
[shortened]
> Given that the keywords are grouped into sets (Medium, Subject, Theme,
> Processes, etc.)...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...
> Disadvantages: More tables. Harder to add new keywords if needed...
> 2. A many-to-many relationship, with one big table of keywords,
> with the
> keywords being the rows rather than the columns...
> 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...
> 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.
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.
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.
-heath