title: mysql manual increment ids?
body: <p>I have a table with items in it (id, name, etc) and I want some kind of database scheme to be able to have multiple copies of the item while still being able to increment the ids. There will be a field called startdate or date_from_which_this_entry_should_be_used.</p>
  
  <p>I've thought of two ways of implementing this:</p>
  
  <ol>
  <li><p>Have a table with only ids (primary key, auto-increment) and do joins to a table that has all the item information.</p>
  
  <p>Advantages:</p>
  
  <ul>
  <li>easy to understand</li>
  <li>hard for someone that comes after me to get confused</li>
  </ul>
  
  <p>Disadvantages:</p>
  
  <ul>
  <li>requires more debugging and coding since this system is already in use</li>
  <li>seems weird to have a table with a single field</li>
  </ul></li>
  <li><p>Have a single table using a sub-select to get the MAX value (+1) to apply to new items.</p>
  
  <p>Advantages:</p>
  
  <ul>
  <li>single table</li>
  <li>only minor code adjustments (but not all that different, maybe)</li>
  </ul>
  
  <p>Disadvantages:</p>
  
  <ul>
  <li>prone to errors (manual increment, can't allow deletions or the MAX value might be off)</li>
  </ul></li>
  </ol>
  
  <p>Thanks in advance!</p>