CODEWITHSUNDEEP
mysqlsqldatabasedatabase-designerd
curzedpirate
curzedpirate

Reputation: 147

Database design - table design for modeling a hierarchy

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?

Here's some of the data in my LIS design...

    HEMATOLOGY  <-------- Lab group
    **************************************************************
     CBC        <-------- Sub group 1
       RBC      <-------- 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       <-------- Lab Group
    **************************************************************
     Routine urinalysis       <-------- Sub group 1
       Visual Examination     <-------- Sub group 2
         Color                <-------- 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

...This hierarchy of data also gets repeated in other lab groupings in my design (e.g. Blood chemistry, Serology, etc)...

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?

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.

Upvotes: 1

Views: 846

Answers (2)

theon
theon

Reputation: 14380

Here are a couple options:

If it is just the hierarchy above you are modeling, and there is no other data involved, then you can do it in two tables:

two table model

One problem with this is that you do not enforce that, for example, a sub_group must be a child of a lab_group, or that a component must be child of either a sub_group_1 or a sub_group_2, but you could enforce these requirements in your application tier instead.

The plus side of this approach is that the schema is nice and simple. Even if the entities have more data associated with them, it might still be worth modeling the hierarchy like this and have some separate tables for the entities themselves.

If you want to enforce the correct relationships at the data level, then you are going to have to split it out into separate tables. Maybe something like this:

mutiple tables

This assumes that each sub_group_1 is only related to a single lab_group. If this is not the case then add a link table between lab_group and sub_group_1. Likewise for the sub_group_1 -> sub_group_2 relationship.

There is a single link table between component and sub_group_1 and sub_group_2. This allows a single component to be related to several sub_group_1 and sub_group_2 entities. The fact it is a single table means that a lot of the sub_group_1_id and sub_group_2_id records will be null (like you mentioned in your question). You could prevent the nulls be having two separate link tables:

  • sub_group_1_component with a foreign key to sub_group_1 and a foreign key to component
  • sub_group_2_component with a foreign key to sub_group_2 and a foreign key to component

The reason I didn't put this in the diagram is that for me, having to query two tables rather than one to get all the component -> sub_group relationships is too much of a pain. For the sake of a little denormalisation (allowing a few nulls) it is much easier to query a single table. If you find yourself allowing a lot of nulls (like a single link table for the relationships between all the entities here) then that is probably denormalising too much.

Upvotes: 2

Jared Drake
Jared Drake

Reputation: 1002

Personally, I would create 3 tables using relationships for the values. It gives you the ability to create limitless arrays of values. Just try to make sure you give great column names, or your head will spin for days. :)

Also, null values aren't a problem look into all the different type of joins

Upvotes: 1

Related Questions