title: One To Many To Itself
body: <p>How would one structure a table for an entity that can have a one to many relationship to itself?  Specifically, I'm working on an app to track animal breeding.  Each animal has an ID; it's also got a sire ID and a dame ID.  So it's possible to have a one to many from the sire or dame to its offspring.  I would be inclined to something like this:</p>
  
  <pre><code>ID INT NOT NULL PRIMARY KEY
  SIRE_ID INT 
  DAME_ID INT
  </code></pre>
  
  <p>and record a null value for those animals which were purchased and added to the breeding stock and an ID in the table for the rest.  </p>
  
  <p>So:</p>
  
  <ol>
  <li>Can someone point me to an
  article/web page that discusses
  modeling this sort of relationship?</li>
  <li>Should the ID be an INT or some sort
  of String?  A NULL in the INT would
  indicate that the animal has no
  parents in the database but a String
  with special flag values could be
  used to indicate the same thing.</li>
  <li><p>Would this possibly be best modeled
  via two tables?  I mean one table
  for the animals   and a separate
  table solely indicating kinship e. g.:</p>
  
  <p>Animal</p>
  
  <p>ID INT NOT NULL PRIMARY KEY</p>
  
  <p>Kinship</p>
  
  <p>ID INT NOT NULL PRIMARY KEY FOREIGN KEY</p>
  
  <p>SIRE_ID INT PRIMARY KEY FOREIGN KEY</p>
  
  <p>DAME_ID INT PRIMARY KEY FOREIGN KEY</p></li>
  </ol>
  
  <p>I apologize for the above:  my SQL is rusty.  I hope it sort of conveys what I'm thinking about.    </p>