List:General Discussion« Previous MessageNext Message »
From:Johan Date:November 25 2005 6:44pm
Subject:Re: Seeking Opinions
View as plain text  
> 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

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