title: Ways to implement tags - pros and cons of each
body: <p><a href="https://stackoverflow.com/questions/1480949/storing-tags-in-database-store-tag-once-or-many-times">Related</a></p>
  <p>Using SO as an example, what is the most sensible way to manage tags if you anticipate they will change often?</p>
  <h1>Way 1:  Seriously denormalized (comma delimited)</h1>
  <pre>
  table <b>posts</b>
  +--------+-----------------+ 
  | postId | tags            |
  +--------+-----------------+
  |   1    | c++,search,code |
  </pre>
  <p>Here tags are comma delimited.</p>
  <p><strong>Pros</strong>:  Tags are retrieved at once with a single <code>select</code> query.  Updating tags is simple.  <b>Easy and cheap to update.</b></p>
  <p><strong>Cons</strong>:  Extra parsing on tag retrieval, difficult to count how many posts use which tags.</p>
  <p>(alternatively, if limited to something like 5 tags)</p>
  <pre>
  table <b>posts</b>
  +--------+-------+-------+-------+-------+-------+
  | postId | tag_1 | tag_2 | tag_3 | tag_4 | tag_5 |
  +--------+-------+-------+-------+-------+-------+
  |   1    | c++   |search | code  |       |       | 
  </pre>
  <h1>Way 2:  &quot;Slightly normalized&quot; (separate table, no intersection)</h1>
  <pre>
  table posts
  +--------+-------------------+
  | postId | title             |
  +--------+-------------------+
  |   1    | How do u tag?     |
  
  table taggings
  +--------+---------+
  | postId | tagName |
  +--------+---------+
  |   1    | C++     |
  |   1    | search  |
  </pre>
  <p><strong>Pros</strong>:  Easy to see tag counts (<code>count(*) from taggings where tagName='C++'</code>).</p>
  <p><strong>Cons</strong>:  tagName will likely be repeated many, many times.</p>
  <h1>Way 3:  The cool kid's (normalized with intersection table)</h1>
  <pre>
  table <b>posts</b>
  +--------+---------------------------------------+
  | postId | title                                 |
  +--------+---------------------------------------+
  |   1    | Why is a raven like a writing desk?   |
  
  table <b>tags</b>
  +--------+---------+
  | tagId  | tagName |
  +--------+---------+
  |   1    | C++     |
  |   2    | search  |
  |   3    | foofle  |
  
  table <b>taggings</b>
  +--------+---------+
  | postId | tagId   |
  +--------+---------+
  |   1    | 1       |
  |   1    | 2       |
  |   1    | 3       |
  </pre>
  <p><strong>Pros</strong>:</p>
  <ul>
  <li>No repeating tag names.</li>
  <li>More girls will like you.</li>
  </ul>
  <p><strong>Cons</strong>:  More expensive to change tags than way #1.</p>