----- Original Message -----
From: "Robb Kerr" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
