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
Is this the right folder?
2 Likes
yap, thatâs correct
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.
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
Another bot? I did not dare to follow the link. Maybe @marcusbaw can remove.
Hi Tom,
Hereâs the schema/EER diagram. I used pgadmin4 tool to generate it. Itâs a fully fledged RDMS management tool and if you want to play with it I can create a user account for you. Please DM me.
I have a working EHRbase instance on AWS and pgadmin4 connected.
I wrote a comprehensive guide to install and configure EHRbase - HERE
Enjoy!
@thomas.beale
4 Likes
Thank you for this diagram @Koray_Atalag 
I noticed that the table names are missing (only 
is shown). Is there a setting in pgadmin4 to show the names?
1 Like
Thanks Koray - any chance you can do what @borut.jures said?
The table name is there right below the schema name âehrâ
2 Likes
Thank you @birger.haarbrandt. The shaded header row looks so âunusedâ. It is there that the name is usually displayed. I should have looked better.
Sorry @Koray_Atalag for missing the table names in your diagram.
1 Like
Non worries @borut.jures I had the same impression when I first generated it.
1 Like