title: Implementing User Defined Fields
body: <p>I am creating a laboratory database which analyzes a variety of samples from a variety of locations.  Some locations want their own reference number (or other attributes) kept with the sample.</p>
  
  <p>How should I represent the columns which only apply to a subset of my samples?</p>
  
  <p><strong>Option 1:</strong>
  Create a separate table for each unique set of attributes?<br/>
  <code>SAMPLE_BOILER: sample_id (FK), tank_number, boiler_temp, lot_number</code><br/>
  <code>SAMPLE_ACID:   sample_id (FK), vial_number</code><br/>
  This option seems too tedious, especially as the system grows.<br/></p>
  
  <blockquote>
    <p><strong>Option 1a:</strong> Class table inheritance (<a href="http://martinfowler.com/eaaCatalog/classTableInheritance.html" rel="nofollow noreferrer">link</a>): Tree with common fields in internal node/table<br/>
    <strong>Option 1b:</strong> Concrete table inheritance (<a href="http://martinfowler.com/eaaCatalog/concreteTableInheritance.html" rel="nofollow noreferrer">link</a>): Tree with common fields in leaf node/table</p>
  </blockquote>
  
  <p><strong>Option 2:</strong> Put every attribute which applies to any sample into the <code>SAMPLE</code> table.<br/>
  Most columns of each entry would most likely be NULL, however all of the fields are stored together.</p>
  
  <p><strong>Option 3:</strong> Create <code>_VALUE_</code> tables for each Oracle data type used. <br/>
  This option is far more complex.  Getting all of the attributes for a sample requires accessing all of the tables below.  However, the system can expand dynamically without separate tables for each new sample type.</p>
  
  <pre><code>SAMPLE:
   sample_id*
   sample_template_id (FK)
  
  SAMPLE_TEMPLATE:
   sample_template_id*
   version *
   status
   date_created
   name
  
  SAMPLE_ATTR_OF     
   sample_template_id* (FK)
   sample_attribute_id* (FK)
  
  SAMPLE_ATTRIBUTE:
   sample_attribute_id*
   name
   description
  
  SAMPLE_NUMBER:
   sample_id* (FK)
   sample_attribute_id (FK)
   value
  
  SAMPLE_DATE:
   sample_id* (FK)
   sample_attribute_id (FK)
   value
  </code></pre>
  
  <p><strong>Option 4:</strong> (Add your own option)</p>