Re: Seeking Opinions
In article [EMAIL PROTECTED], sheeri kritzer [EMAIL PROTECTED] writes: On 11/28/05, Martijn Tonies [EMAIL PROTECTED] wrote: Part of me agrees with you, on a Pure SQL level. but then why would anyone ever use ENUM or SET? I wouldn't :-) IMO, they're abominations that are to be avoided. Again, part of me agrees with you. But part of me, particularly the part that says our queries need to be optimized as much as possible, so our customers will be happy, thinks that ENUM and SET are non-standard, MySQL specific ways to create normalized data without having an extra table, and thus an extra join, and thus more computation, and thus a slower query. I'd say SET is a denormalization which might give you some performance (at the price of being completely nonstandard), but ENUM is just syntactical sugar for a TINYINT column. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking Opinions
On this note, I feel as though the best way to do this in MySQL is to use the ENUM or SET types, instead of indexing against a separate table -- I'm guessing SET, so each piece of clipart can be associated with more than one category. Of course, that eliminates relevancy searches (which you could do as in the first example given by the OP, with a text field and fulltext search). I think the biggest problem with that is that SET only allows up to 64 different items, so the clipart site could only have up to 64 categories (and a Christmas floral arrangement might have the keywords color, flowers and Christmas). That being said, the point of this post is to ask -- Is MySQL working on allowing the SET limit to increase? Or is that just too much storage and math? -Sheeri On 11/25/05, Johan [EMAIL PROTECTED] wrote: 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking Opinions
You, yourself, explain why your suggestion would be a bad fit for this project: SET is limited to just 64 discrete values per table. I cannot remember reading that increasing the size of the SET features as a priority on any development list. I might have missed something but I don't think that SET64 will be coming any time soon. Shawn Green Database Administrator Unimin Corporation - Spruce Pine sheeri kritzer [EMAIL PROTECTED] wrote on 11/28/2005 09:53:49 AM: On this note, I feel as though the best way to do this in MySQL is to use the ENUM or SET types, instead of indexing against a separate table -- I'm guessing SET, so each piece of clipart can be associated with more than one category. Of course, that eliminates relevancy searches (which you could do as in the first example given by the OP, with a text field and fulltext search). I think the biggest problem with that is that SET only allows up to 64 different items, so the clipart site could only have up to 64 categories (and a Christmas floral arrangement might have the keywords color, flowers and Christmas). That being said, the point of this post is to ask -- Is MySQL working on allowing the SET limit to increase? Or is that just too much storage and math? -Sheeri On 11/25/05, Johan [EMAIL PROTECTED] wrote: 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking Opinions
I did answer my own question, and that's why I said: That being said, the point of this post is to ask -- Is MySQL working on allowing the SET limit to increase? Or is that just too much storage and math? Using a SET is, I believe, MUCH faster than making a special table simply to normalize the data. I believe in using MySQL's functionality to the fullest, and working smarter, not harder. Would folks use SET to help normalize their data instead of using a separate table? Should I put in a request for the feature I want? Or would very few folks benefit, and it would add too much storage and computational cost? (my original question) -Sheeri On 11/28/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: You, yourself, explain why your suggestion would be a bad fit for this project: SET is limited to just 64 discrete values per table. I cannot remember reading that increasing the size of the SET features as a priority on any development list. I might have missed something but I don't think that SET64 will be coming any time soon. Shawn Green Database Administrator Unimin Corporation - Spruce Pine sheeri kritzer [EMAIL PROTECTED] wrote on 11/28/2005 09:53:49 AM: On this note, I feel as though the best way to do this in MySQL is to use the ENUM or SET types, instead of indexing against a separate table -- I'm guessing SET, so each piece of clipart can be associated with more than one category. Of course, that eliminates relevancy searches (which you could do as in the first example given by the OP, with a text field and fulltext search). I think the biggest problem with that is that SET only allows up to 64 different items, so the clipart site could only have up to 64 categories (and a Christmas floral arrangement might have the keywords color, flowers and Christmas). That being said, the point of this post is to ask -- Is MySQL working on allowing the SET limit to increase? Or is that just too much storage and math? -Sheeri On 11/25/05, Johan [EMAIL PROTECTED] wrote: 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
Re: Seeking Opinions
I did answer my own question, and that's why I said: That being said, the point of this post is to ask -- Is MySQL working on allowing the SET limit to increase? Or is that just too much storage and math? Using a SET is, I believe, MUCH faster than making a special table simply to normalize the data. I believe in using MySQL's functionality to the fullest, and working smarter, not harder. Would folks use SET to help normalize their data instead of using a separate table? Should I put in a request for the feature I want? Or would very few folks benefit, and it would add too much storage and computational cost? (my original question) Create a separate table. MySQL is a database engine. Tables is what database engines do. Your design should be clear and extendable without having to modify metadata (adding a category would need you to do an ALTER TABLE). -- Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking Opinions
Part of me agrees with you, on a Pure SQL level. but then why would anyone ever use ENUM or SET? -Sheeri On 11/28/05, Martijn Tonies [EMAIL PROTECTED] wrote: I did answer my own question, and that's why I said: That being said, the point of this post is to ask -- Is MySQL working on allowing the SET limit to increase? Or is that just too much storage and math? Using a SET is, I believe, MUCH faster than making a special table simply to normalize the data. I believe in using MySQL's functionality to the fullest, and working smarter, not harder. Would folks use SET to help normalize their data instead of using a separate table? Should I put in a request for the feature I want? Or would very few folks benefit, and it would add too much storage and computational cost? (my original question) Create a separate table. MySQL is a database engine. Tables is what database engines do. Your design should be clear and extendable without having to modify metadata (adding a category would need you to do an ALTER TABLE). -- Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking Opinions
Part of me agrees with you, on a Pure SQL level. but then why would anyone ever use ENUM or SET? I wouldn't :-) IMO, they're abominations that are to be avoided. Now, ENUM could be a bit useful. The Pure SQL way to avoid those would simply be a column of the right datatype and a CHECK constraint (which MySQL doesn't support, yet). -- Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking Opinions
On 11/28/05, Martijn Tonies [EMAIL PROTECTED] wrote: Part of me agrees with you, on a Pure SQL level. but then why would anyone ever use ENUM or SET? I wouldn't :-) IMO, they're abominations that are to be avoided. Again, part of me agrees with you. But part of me, particularly the part that says our queries need to be optimized as much as possible, so our customers will be happy, thinks that ENUM and SET are non-standard, MySQL specific ways to create normalized data without having an extra table, and thus an extra join, and thus more computation, and thus a slower query. Tell me, do you also create your own sequences in MySQL instead of using the AUTO_INCREMENT feature? Because that, too, is an abomination in the same way. I use ENUM a lot, successfully, and when I want to add another ENUM value to a table (I never delete one), I have a script that runs a show table, gets the ENUM field in question, gets all the values, and alters the table modifying the column with the correct values (all the current values + the new one). I've never had a problem with it yet (knock wood). -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking Opinions
Part of me agrees with you, on a Pure SQL level. but then why would anyone ever use ENUM or SET? I wouldn't :-) IMO, they're abominations that are to be avoided. Again, part of me agrees with you. But part of me, particularly the part that says our queries need to be optimized as much as possible, so our customers will be happy, thinks that ENUM and SET are non-standard, MySQL specific ways to create normalized data without having an extra table, and thus an extra join, and thus more computation, and thus a slower query. That's wrong, if you use set, the data isn't normalized cause you're storing multiple values in a column. Tell me, do you also create your own sequences in MySQL instead of using the AUTO_INCREMENT feature? Because that, too, is an abomination in the same way. One way to do it is to create an auto-inc table, but this is besides the point for ENUM/SET. I use ENUM a lot, successfully, and when I want to add another ENUM value to a table (I never delete one), I have a script that runs a show table, gets the ENUM field in question, gets all the values, and alters the table modifying the column with the correct values (all the current values + the new one). I've never had a problem with it yet (knock wood). In other words: modifying metadata to adjust the system for data requirements ;-) ... This also means there's no easy way to automate this. -- Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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. Robb Kerr Digital IGUANA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking Opinions
Robb Kerr [EMAIL PROTECTED] wrote on 11/25/2005 11:59:48 AM: 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. Robb Kerr Digital IGUANA If speed and flexibility is your priority, use option one. Remember: FT indexing will skip all stopwords and words smaller than the minimum FT length. By default, that length is set to 4 but there are ways to make it smaller and you can also provide an empty stopword list so you can get around that, too. For option 1 I think you need a third table that maps keywords to clipart. That way you only need to store the string value of a keyword once. It will keep your keywords table smaller and make lookups faster. Technically speaking, doing it this way normalizes your keyword data. Normalized databases most often perform much better than denomalized databases. There are some notable exceptions but as a general rule this is true. Making a separate keyword table makes it easier to search for a list of keywords and locate not just full matches but partial matches, too (matched 6 of 8 search terms). The FT search returns a relevance number but as applied to smaller chunks of text (a list of 20 or 30 keywords or key phrases), that may not be very useful. Search the archives for various techniques of finding lists of values from normalized data. This type of question has appeared frequently. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Seeking Opinions
- 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]
Re: Seeking Opinions
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
Re: Seeking Opinions
On Friday 25 November 2005 01:44 pm, 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) In the Clipart table, are names required to be unique? If so, then you can use Clipartname as the index, and that would eliminate the 3rd table. Or so I think -- I'm still learning this. Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking Opinions
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]
Re: Seeking Opinions
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]
