What's the difference between concept_set and concept_set_derived tables?

Humans (you and me) should only edit set relationships in the concept_set table. These relationships are then programmatically burst into the concept_set_derived table for "real world" use.

• Humans use concept_set
• Computers use concept_set_derived

Understanding by example

Here's an example (using made-up concepts). Suppose we want to relate the following three concepts using concept sets:

1. metoprolol
2. Beta Blockers
3. Anti-Hypertensive Medications

We (humans) use the dictionary editing tools to define two relationships:

The above relationships, state that metoprolol is a Beta-Blocker and that all Beta-Blockers are Anti-Hypertensive Medications. The idea that metoprolol is an Anti-Hypertensive Medication is implied.

We then run a script (e.g., AdministratorService.updateConceptSetDerived(Concept concept)) to programmatically burst all of the implicit relationships and put these into the concept_set_derived table. Each time the script is run, the concept_set_derived table is cleared, and all entries in the concept_set table are copied into concept_set_derived, along with any implied relationships.

The third item in the list above is derived from the manually defined relationships.

Why all this monkey business with concept_set_derived?

The concept_set table is displayed in the Old Nasty Data Model Page. Concept_set_derived is considered part of the business stuff that's needed to make OpenMRS work. The bottom line is that bursting out implicit relationships into a concept_set_derived table prevents the application from having to calculate all of the implicit relationships in real time. Likewise, we don't want to burden humans with having to explicitly define all of these implicit relationships (why create three relationships when two says it all — in the example above).