Design decision 2007-10-03 - By Chris
Back
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/mysql?unsub=mysql@(protected)
|
|
