Scott Haneda wrote:
I have an a table of objects, attached to those objects are keywords.
Users submit the keywords to the objects.
Currently, I chose to keep a hit count on the keywords, so if a duplicate
keyword is supplied, a counter is incremented.
I thought this was a good idea, as it keeps the number of rows in the
keywords table to a minimum.
However, this is a user login based system, and with the above, I lose the
ability to track which users sent in which keywords.
Why do you need this info? Is there a reporting need or something else
you need to keep this information for? Ie when are you going to need to
know who inserted a particular keyword?
So I can move to the keywords table storing duplicate keywords, and each
keyword will get a user_id attached to it, but, that table will grow. Fast.
1000 objects, each with 10 keywords only = 10,000, I could hit many millions
very fast.
I could toss in a third table, and relate that to the user_id and keyword.
However, I am inserting in one go, and that could be upwards of 30 or more
inserts, taking too long.
[assuming php]
$keyword_ids = array();
foreach ($keywords_to_insert) {
$query = "insert into keywords_table(keyword) .....";
$keyword_ids[] = mysql_insert_id();
}
$query = "insert into table (userid, keywordid) select userid, keywordid
where keywordid in (" . implode(',', $keyword_ids) . ")";
[/assuming php]
Just one query to insert the relationship(s) :)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
