EhrBase DB schema definition

Implementation Platform
1

I was trying to find the DB schema definition for Postgres in EhrBase in the EhrBase code base . So far I have not found it - can anyone provide a pointer? Ping @birger.haarbrandt

2

Is this the right folder?

2 Likes
3

yap, that’s correct

4

Is there a DB schema / ER diagram or similar available? The reason I am looking at this is to consider how a ‘standard RDBMS’ schema could be derived from an RM. My supposition is that this is via a transform of the RM to a simplified model that is optimised for RDBMS persistence, e.g certain fields stringified, maybe some inheritance simpification, and so on.

5

Generally speaking, all those techniques are used if the DBMS is relational. For instance, denormalization is applied to avoid the cost of too many joins, resulting in sparse tables, or multiple table inheritance vs. single table inheritance, depending on the case (it’s also about joins vs. sparse tables). Also small structures might get serialized as a single string column. I also simplify the OBJECT_REFs, most of the times as FKs, and other tricks. I did that analysis back in 2011-2013 while designing EHRServer, even testing XML and JSON databases. After that designed the “Design and Implementation of Clinical Databases with openEHR” course, that basically shows all those techniques and where can be applied.

About EHRBASE specifically, the only thing I know is everything is normalized but the content, which is saved as native JSON columns in Postgres, then AQL is translated to SQL which contains parts that query inside the JSON structure. Then there are some history columns for the locatables that are versioned, to store the older versions. In EHRServer and Atomik we don’t have those history tables, everything is stored in the same tables and there is an is_last flag for quick access to the last version.

That’s another thing: on RDBMS we also add stuff that is not part of the RM that we use to speed up queries or simplify some use cases, like storing different forms of paths to query and reconstruct data when retrieving.

Hope that helps.

2 Likes