> Option One
> Related tables. Table one (clipart pieces) contains ClipartID and
> ClipartName fields. Table two (keywords) contains KeywordID, ClipartID and
> Keyword fields. This option will create an incredibly large related table
> (keywords) with each piece of clipart having tens of related fields in the
> keyword table. But, searching ought to be fast.
Use this option but use a third table that contains just ClipartID and
KeywordID to create the m:n relationship. Like this:
Clipart: ClipartID (primary key) & Clipartname
Keywords: KeywordID (primary key) & Keyword (just one so must be unique)
Linktable: ClipartID & KeywordID (ClipartID + KeywordID = primary key)
I have a database like this with over 250,000 images, 50,000+ keywords and
more than 2 million image - keyword links. All my keyword searches are very
fast (under 0.05 seconds per query).
This is on very mediocre hardware (AMD Sempron 2600+ with only 512Mb of
memory) so performance on a faster computer with more memory should be