title: Foreign Key relationship between two columns in one table
body: <p>I'm building a database table representing a 'note' that has a parent-child relationship in two of it's columns, like so:</p>
  
  <pre><code>ID           INT(10) PK NOT NULL UNSIGNED ZEROFILL AUTOINCREMENT  -- Primary key
  parent_ID    INT(10) UNSIGNED ZEROFILL                 -- References Primary key
  username     VARCHAR(30) NOT NULL
  information  VARCHAR(256) NOT NULL
  comment      VARCHAR(256) NOT NULL
  </code></pre>
  
  <p>where <code>username</code> is a foreign key to a <code>user</code> table and is the username of the person who wrote the note, <code>information</code> is the information contained in the note and <code>comment</code> is what <code>username</code> added. <code>comment</code> will always have something in it, and is not necessarily the same as the previous user.</p>
  
  <p>The concept is that someone can "copy" a note and have their own comments, and say "I got this note from xxx", hence the parent-child relationship. A bit like sharing in facebook, perhaps.</p>
  
  <p>How do I correctly form the parent_ID column? Should I use an identifying relationship; which sides are mandatory?
  I figure this has to be a 1-to-Many relationship since five people can copy the same note.</p>
  
  <p>I expect there to be more copying than creating new notes, so there will be a relatively small number of <code>NULL</code>'s in the records, but to get rid of nulls entirely should I apply a <code>NOT NULL</code> constraint and simply make the default parent <code>0</code> with an essentially meaningless record at <code>ID 0</code> and take note of this in the software?</p>
  
  <p>Is this even the correct way? Should I employ a two-table system with:</p>
  
  <pre><code>ID          INT(10)      PK
  information VARCHAR(256)
  orig_user   VARHCAR(30)  FK  -- Potentially
  </code></pre>
  
  <p>and</p>
  
  <pre><code>ID          INT(10)      PK FK
  username    VARCHAR(30)  PK FK
  comment     VARCHAR(256)
  </code></pre>
  
  <p>Which eliminates any possible <code>NULL</code>'s by definiton?</p>
  
  <p>Thanks</p>