List:General Discussion« Previous MessageNext Message »
From:Robb Kerr Date:November 25 2005 8:43pm
Subject:Re: Seeking Opinions
View as plain text  
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
Thread
Seeking OpinionsRobb Kerr25 Nov
  • Re: Seeking OpinionsSGreen25 Nov
  • Re: Seeking OpinionsRhino25 Nov
  • Re: Seeking OpinionsJohan25 Nov
    • Re: Seeking OpinionsHal Vaughan25 Nov
  • Re: Seeking OpinionsRobb Kerr25 Nov
    • Re: Seeking OpinionsJohan26 Nov
      • Re: Seeking Opinionssheeri kritzer28 Nov
        • Re: Seeking OpinionsSGreen28 Nov
          • Re: Seeking Opinionssheeri kritzer28 Nov
        • Re: Seeking OpinionsMartijn Tonies28 Nov
          • Re: Seeking Opinionssheeri kritzer28 Nov
        • Re: Seeking OpinionsMartijn Tonies28 Nov
          • Re: Seeking Opinionssheeri kritzer28 Nov
        • Re: Seeking OpinionsMartijn Tonies28 Nov
        • Re: Seeking OpinionsHarald Fuchs29 Nov