title: Database design - table design for modeling a hierarchy
body: <p>I am designing a laboratory information system (LIS) and am confused on how to design the tables for the different laboratory tests. How should I deal with a table that has an attribute with multiple values and each of the multiple values of that attribute can also have multiple values as well?</p>
  
  <p>Here's some of the data in my LIS design...</p>
  
  <pre><code>    HEMATOLOGY  &lt;-------- Lab group
      **************************************************************
       CBC        &lt;-------- Sub group 1
         RBC      &lt;-------- Component
         WBC
         Hemoglobin
         Hematocrit
         MCV
         MCH
         MCHC
         Platelet count
       Hemoglobin
       Hematocrit
       WBC differential
         Neutrophils
         Lymphocytes
         Monocytes
         Eosinophils
         Basophils
       Platelet count
       Reticulocyte count
       ESR
       Bleeding time
       Clotting time
       Pro-time
       Peripheral smear
       Malarial smear
       ABO
       RH typing
  
      CLINICAL MICROSCOPY       &lt;-------- Lab Group
      **************************************************************
       Routine urinalysis       &lt;-------- Sub group 1
         Visual Examination     &lt;-------- Sub group 2
           Color                &lt;-------- Component
           Turbidity
           Specific Gravity       
         Chemical Examination
           pH
           protein
           glucose
           ketones
           RBC
           Hbg
           bilirubin
           specific gravitiy
           nitrite for bacteria
           urobilinogen
           leukocyte esterase 
         Microscopic Examination
           Red Blood Cells (RBCs)
           White Blood Cells (WBCs)
           Epithelial Cells 
           Microorganisms (bacteria, trichomonads, yeast) 
           Trichomonads 
           Casts 
           Crystals
       Occult Blood
       Pregnancy Test 
  </code></pre>
  
  <p>...This hierarchy of data also gets repeated in other lab groupings in my design (e.g. Blood chemistry, Serology, etc)...</p>
  
  <p>Another question is, how am I gonna deal with a component (for example, RBC) which can be a member of one or more lab groups?</p>
  
  <p>I already implemented a solution to my problem by making a separate tables, 1 for lab group,  1 for sub group 1, 1 for sub group 2 and 1 for component. And then created another table to consolidate all of them by placing a foreign key of each in this table...the only trade off is that some of the rows in this table may have null values. Im not satisfied with my design, so I'm hoping someone could give me advise on how to make it right; any help would be greatly appreciated.</p>