...
- Define views which join related tables together e.g. Person, Patient, Patient Attributes could be put together in one view; Concept, Obs, Concept_Name could be put together as one view.
- On these de-normalised views (from 1) we could create some domain specific views for diagnosis, chief complaints, disposition, lab result etc.
- On views from 1 and 2 we could create views which introduces dimensions (see the dimension list below).
- Explore the possibility of organising diagnosis in a flatter structure and use reference term mapping.
Dimensions
Hospital level --> Date, Location
Patient level --> Age, Gender, Tribe
...
Visit level --> Visit TypeEncounter level --> Location
Any view at a particular level would be able to provide columns for dimensions their and higher level. For example a program view should not only provide program outcome, program name with every program instance but also age, gender, tribe, date and location. All the views can be defined such that they repeat the applicable dimensions for every row.
References
http://stratosprovatopoulos.com/web-development/mysql/pivot-table-with-dynamic-columns/