StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTagging system: Toxi solution questions
text
Body
copied!<p>I'm sort of breaking my head over the Toxi solution for tag database schemas. I'm working on a system to which users can submit items, and those items can have tags associated with them. After reading up on tagschemas, I found the Toxi solution to suit my needs most. However, I'm not entirely sure if I'm planning this right, so I'd like your opinions on this please.</p> <p>I'll have three databases. <br/><code>items</code> containing <code>item_id</code> and others <br/><code>tagmap</code> using <code>item_id</code> and <code>tag_id</code> as foreign keys <br/><code>tags</code> containing <code>tag_id</code> and <code>tag_text</code></p> <p>When adding a new item, am I right to assume the process to add the tags to the database is as follows?</p> <ol> <li>sort submitted tags into array</li> <li>for every tag in the array: <ol> <li>get tag_id from tags where tag_text matches the current tag</li> <li>if that returns 0 rows: <ol> <li>add tag to tags table</li> <li>get the tag_id</li> </ol></li> <li>add item_id and tag_id to tagmap</li> </ol></li> <li>finish (give user the a-okay, etc)</li> </ol> <p>This means we'll end up with an entry in the tagmap for every tag for every item. It seems correct, but I can't help but think there's a better way to do that than ending up with a huge amount of entries there...</p> <p>As for editing the tags, I've thought up the following process, though I think there's a better way which I haven't found yet.</p> <ol> <li>get tags using item_id and insert into editable field</li> <li>user makes changes. on submit:</li> <li>delete rows from tagmap where item_id matches the one being edited</li> <li>same process as the one listed above </li> </ol> <p>I'm kind of iffy about point 3 there. Is there a way for me to check if any tags have been removed, so I can selectively delete tags instead of just deleting and re-adding them? And just to be sure: when deleting tagmap rows, the related items won't be deleted with it because it points to a foreign key instead of acting as one, right?</p> <p>Also, I may want to keep track of the amount of times a tag is used, but I don't want to run a query to count those every time it needs to be displayed. I'm thinking of having a cron job count the number of instances for every tag_id in tagmap once every hour, or bihourly, and then update the tag_use value in the tags table. Is that a correct way to do it, or is there a better way?</p> <p>Looking back, that's quite the hefty amount of text. Welp, rather too detailed than missing information, and rather asking too many questions and learning a lot of new things than asking too few. Good chance I've just spent too much time looking into this today, and it'll all make more sense tomorrow.</p> <p>Thanks in advance!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload