Hi Robb,
Your table setup is correct. In the third table the two ID fields together
form the Primary Key. This makes sure that you cannot add the same keyword
twice to the same image. If you use MySQL Administrator to create your
tables then you just add both columns to the primary key index.
Programmatically it would look something like (to create the tabloe and
indexes):
CREATE TABLE "ClipartKeyword" (
"ClipartID" INT(11) NOT NULL,
"KeywordID" INT(11) NOT NULL,
PRIMARY KEY ("ClipartID","KeywordID"),
INDEX ("KeywordID")
) TYPE=InnoDB;
This database uses ANSI, hence the quotes around the field names. The index
on KeywordID makes it easy to find all images that have a certain keyword
attached to it.
Good luck,
Johan
On 11/25/05, Robb Kerr <[EMAIL PROTECTED]> wrote:
>
> 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
