StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>First of all "toxi" is not a standard term. Always define your terms! Or at least provide relevant links.</p> <p>And now to the question itself...</p> <blockquote> <p>I'll have three databases. </p> </blockquote> <p>No, you'll have 3 tables.</p> <blockquote> <p>When adding a new item...</p> </blockquote> <p>You are pretty much on the right track, with the exception that you can use the set-based nature of SQL to "merge" many of these steps. For example, tagging an item 1 with tags: 'tag1', 'tag2' and 'tag3' can be done like this...</p> <pre><code>INSERT IGNORE INTO tagmap (item_id, tag_id) SELECT 1, tag_id FROM tags WHERE tag_text IN ('tag1', 'tag2', 'tag3'); </code></pre> <p>The <code>IGNORE</code> allows this to succeed even if item is already connected to some of these tags.</p> <p>This assumes all required tags are already in <code>tags</code>. Assuming <code>tag.tag_id</code> is auto-increment, you can do something like this to ensure they are:</p> <pre><code>INSERT IGNORE INTO tags (tag_text) VALUES ('tag1'), ('tag2'), ('tag3'); </code></pre> <blockquote> <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 then ending up with a huge amount of entries there...</p> </blockquote> <p>There is no magic. If "item is connected to a particular tag" is piece of knowledge you want to record, then it will <em>have</em> to have some sort of physical representation in the database.</p> <blockquote> <p>As for editing the tags...</p> </blockquote> <p>You mean re-tagging items (not modifying tags themselves)?</p> <p>To remove all tags that are not in the list, do something like this:</p> <pre><code>DELETE FROM tagmap WHERE item_id = 1 AND tag_id NOT IN ( SELECT tag_id FROM tags WHERE tag_text IN ('tag1', 'tag3') ); </code></pre> <p>This will disconnect the item from all tags except 'tag1' and 'tag3'. Execute the INSERT above and this DELETE one after another to "cover" both adding and removing tags.</p> <p>You can play with all this in the <a href="http://sqlfiddle.com/#!2/49981/3" rel="nofollow noreferrer">SQL Fiddle</a>.</p> <blockquote> <p>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> </blockquote> <p>Correct. A child endpoint of a FK will not trigger a referential action (such as ON DELETE CASCADE), only parent will.</p> <p>BTW, you are using this schema because you want additional fields in <code>tags</code> (beside <code>tag_text</code>), right? If you do, not loosing this additional data just because all connections are gone is desired behavior.</p> <p>But if you just wanted the <code>tag_text</code>, you'd use a simpler schema where deleting all connections would be the same as deleting the tag itself:</p> <p><img src="https://i.stack.imgur.com/CcCLo.png" alt="enter image description here"></p> <p>This would not just simplify the SQL, it would also provide better <a href="http://use-the-index-luke.com/sql/clustering" rel="nofollow noreferrer">clustering</a>.</p> <p>At first glance, "toxi" might look like it's saving space, but this might actually not be the case in practice, since it requires additional tables and indexes (and tags tend to be short).</p> <blockquote> <p>Also, I may want to keep track of the amount of times a tag ... cron job ...</p> </blockquote> <p>Measure before you decide to do something like this. My SQL Fiddle mentioned above uses a very deliberate order of fields in the <code>tagmap</code> PK, so data is clustered in a way very friendly to this kind of counting (remember: <a href="http://www.ovaistariq.net/521/understanding-innodb-clustered-indexes/" rel="nofollow noreferrer">InnoDB tables are clustered</a>). You'd have to have a truly huge amount of items (or require unusually high performance) before this becomes a problem.</p> <p>In any case, <strong>measure</strong> on realistic amounts of data!</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