List:General Discussion« Previous MessageNext Message »
From:Rhino Date:November 25 2005 6:16pm
Subject:Re: Seeking Opinions
View as plain text  
----- Original Message ----- 
From: "Robb Kerr" <rkerr.news@stripped>
To: <mysql@stripped>
Sent: Friday, November 25, 2005 11:59 AM
Subject: Seeking Opinions


> I'm building a new clipart site. I need to have keyword searching. I'm
> seeking opinions about table design. Here are my proposed options. If
> anyone has any other suggestions, please make them.
>
> 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.
>
> Option Two
> Single table. Table one (clipart pieces) contains ClipartID, ClipartName
> and Keywords fields. The Keywords field would be a long text field that
> would be searched with a full-text search. Searching and maintenance would
> be easier but would searching be slowed down significantly?
>
> Please provide any input you have and make any alternate suggestions.
>
I'm not sure if you'll gain or lose by putting the keywords in a separate 
table. Your description of the data is too vague. Could you possibly type an 
example of a few rows of each scenario so that we can see what will actually 
be in the Keywords columns in each scenario? It would also be VERY useful to 
know what the primary and foreign keys of each table are going to be.

There is one major performance issue that you don't appear to have 
considered yet: how will the clipart images themselves be stored? Are you 
going to store each one as a blob in the data row itself? Or are you going 
to store a URL or other URL-like description of where the clipart image is 
found? The latter approach keeps the MySQL tables very small and may give 
you performance advantages but also make your job a bit more complicated: 
you have to maintain some kind of directory structure for your clipart files 
and keep them consistent with the URL that you store in the database.

I've barely touched blobs in MySQL so I don't feel qualified to recommend 
either approach to you from my own experience but I *think* the consensus 
among people with more blob experience is that the second approach I 
mentioned is better. However, it would be very wise of you to check the 
archives for this mailing list - search on 'blob' - to be sure I am getting 
that right.

Rhino 



-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.7/182 - Release Date: 24/11/2005

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