title: Use item specific prefixes and autonumber for primary keys?
body: <p>We had a meeting this morning about how would should store our ID for some assets that we have in our database that we are making, the descusion generated a bit of heat so I decided to consult the experts of SO.</p>
  
  <p>The table structure that I belive that we should have(short version) is like the following:</p>
  
  <p><strong>Example 1)</strong></p>
  
  <ul>
  <li>AssetId - int(32) - Primary Key</li>
  <li>Type - string</li>
  </ul>
  
  <p>so some example data is like this:</p>
  
  <pre><code>==AssetId======Type===
    12345        "Manhole"
    155415       "Pit"
  </code></pre>
  
  <p>etc.</p>
  
  <p>Another member of the team suggested something like this:</p>
  
  <p><strong>Example 2)</strong></p>
  
  <ul>
  <li>AssetId - string - Primary Key</li>
  <li>Type - string</li>
  </ul>
  
  <p>so some example data is like this:</p>
  
  <pre><code>==AssetId======Type===
    "MH12345"    "Manhole"
    "P155415"    "Pit"
  </code></pre>
  
  <p>where we make a short version of the type and append it to the front of the ID and store it in the database.  I have seen a few asset databases that do this and have never really this approach. </p>
  
  <p>I have never really liked the idea of using strings as ID for sorting reasons.   I also feel like it is storing useless information just for the sake of it when you already have the type of asset store anyway.</p>
  
  <p>What approach would you take?  And why?  Are there any benefits to using approach 1 over 2?</p>
  
  <p>EDIT: Yes I will be using AUTO_INCREMENT for approach 1.</p>