On Fri, 25 Nov 2005 10:44:44 -0800, Johan wrote:
>> 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
> excellent.
>
> HTH,
>
> Johan
Please explain further your 3 table scenario. Is the following example
correct...
Table One - Clipart
ClipartID (primary key) | ClipartName
1 | artone.jpg
2 | arttwo.jpg
3 | artthree.jpg
Table Two - Keywords
KeywordID (primary key) | Keyword
1 | black and white
2 | color
3 | christmas
4 | thanksgiving
Table Three - LinkTable
ClipartID | KeywordID
1 | 1
1 | 3
2 | 2
2 | 3
I don't understand what would be the primary key for the third table or
what you mean by "(ClipartID + KeywordID = primary key)". Please elaborate.
Thanx,
Robb
