title: MySQL schema and CRUD queries for tags system
body: <p>Lets imagine that I have videos and each video can have few tags (maximal is 10 tags per video).</p>
  
  <p>I planned my SQL schema and it looks like this:</p>
  
  <ul>
  <li><code>videos</code>: <code>id</code>, <code>title</code>, <code>path</code>,</li>
  <li><code>tag_rels</code>: <code>id</code>, <code>tag_id</code>, <code>item_id</code> (it will point to <code>videos</code>.<code>id</code>),</li>
  <li><code>tags</code>: <code>id</code>, <code>tag</code>;</li>
  </ul>
  
  <p>Okay, seems fine to me.</p>
  
  <p>Then I wrote <code>SELECT</code> that should get video, plus, tags for it.</p>
  
  <pre><code>SELECT `videos`.`id`, `videos`.`title`, `videos`.`path`, `tags`.`tag`
  FROM `videos`
  JOIN `tag_rels`
      ON `tag_rels`.`item_id` = `videos`.`id`
  JOIN `tags`
      ON `tags`.`id` = `tag_rels`.`tag_id`
  </code></pre>
  
  <p>It's not tested or so because it's all in mind-level.</p>
  
  <p>And the <strong>huge problem</strong> is <code>INSERT</code> query (queries, I guess).</p>
  
  <p>As much as I understand:</p>
  
  <ul>
  <li>Query #1: insert video in <code>videos</code>. This will return primary key (<code>videos</code>.<code>id</code>), right?</li>
  <li>Query #2: select tag #1 from database and get it's primary key,</li>
  <li>Query #3: if there aren't such record (based on tag name (<code>tags</code>.<code>tag</code>), do an insert query and insert it. The goal is to get primary key of that tag,</li>
  <li>Query #4: insert entry in <code>tag_rels</code> with video's <em>pk</em> and tag's <em>pk</em>;</li>
  </ul>
  
  <p>So, it's one query per video as it is, plus` 2 or 3 queries for <strong>each</strong> tag.</p>
  
  <p>This means that if video has 10 tags and (worst situation) any of those tags aren't saved in database, it will cost me 1 + 10 * 3... em.... 31 query?!</p>
  
  <p>There got to be a better way! Thanks a lot!</p>
  
  <p>P.S. I don't want duplicate entries in the database and I would love to have column <code>used_in</code> to <code>tags</code> with video count that use the tag. In future...</p>