Stream: implementers
Topic: Database model
William Tran (May 23 2018 at 20:06):
Hi,
I'm being charged with implementing FHIR as the claims and clinical data model for my company. As I understand it, FIHR only specifies the REST API, but not the underlying database model.
I've come across FHIRBase, but it seems to simply take the JSON data object and store it in a "resource" field. Have anyone come across a more "relational" database model that I could use as a starting point? Does something like PCORnet fit the bill here, or do I need something different?
- William
Brian Kaney (May 23 2018 at 20:11):
You might want to take a look at http://standardhealthrecord.org/ as well
Tyler Fricks (May 23 2018 at 20:12):
@William Tran I am just starting FHIR implementation too. My recommendation is MongoDB, although that probably isn't helpful for you as it is far from relational. It's just that Mongo handles JSON files very well, even queries are in JSON format. It keeps things consistent and it's what we are using.
Grahame Grieve (May 23 2018 at 20:17):
there's a mix of approaches. You might find this useful:
Grahame Grieve (May 23 2018 at 20:17):
http://www.healthintersections.com.au/?p=2776
William Tran (May 23 2018 at 20:21):
@Tyler Fricks nice to hear that; I've thought of MongoDB too but am a little worried about data integrity. Did you take advantage of MongoDB's schema validation or did you delegate that to the API layer?
@Grahame Grieve and @Brian Kaney , thanks a lot for the resources :)
Tyler Fricks (May 23 2018 at 20:57):
@William Tran We have not done it yet, but we will use the built in validation. I have used Mongo before for other projects and the validation is top notch.
Of course there is some validation before it gets to Mongo as well...
Kevin Mayfield (May 24 2018 at 12:15):
This is based on a relational model (geared towards UK and not all resources) : https://github.com/nhsconnect/careconnect-reference-implementation
Kevin Mayfield (May 24 2018 at 12:16):
it uses HAPI. Instructions available here: https://nhsconnect.github.io/CareConnectAPI/build_ri_overview.html
Brian Postlethwaite (Jun 20 2018 at 23:07):
May also find this useful in your server design, has a list of the issues data storage styles have:
https://www.slideshare.net/BrianPostlethwaite1/fhir-server-design-review
Chris Moesel (Jun 22 2018 at 19:17):
We built a Go-based FHIR server using MongoDB (see: https://github.com/intervention-engine/fhir). MongoDB worked great for CRUD and simple searches. But... we ran into issues implementing things liked chained search, reverse chained search, has, etc. We had to drop into MongoDB's aggregation pipeline to do these, and that significantly impacted performance once we got into very large numbers of records (and it made paging quite difficult as well). In the end, if we were to do it again today, we probably would not use MongoDB.
Chris Moesel (Jun 22 2018 at 19:20):
More recently, we have begun to experiment using PG's JSON capabilities (see: https://github.com/synthetichealth/candle). This is just a proof-of-concept and doesn't do nearly as much as our MongoDB-based server, but so far has shown promise. @Andy Gregorowicz gave a short talk on this at the recent Dev Days. We can probably point you to the slides once they are posted.
Chris Moesel (Jun 22 2018 at 19:23):
I should also note that MongoDB prevented us from properly supporting atom transactions as well -- although I hear that Mongo 4.0 will support multi-document transactions.
Juan Antonio De los Cobos (Nov 20 2018 at 12:25):
Anybody has any documentation about FHIR database model?. thank you
Ewout Kramer (Nov 20 2018 at 13:51):
Hi Juan, there is no single FHIR database model - many vendors will have an existing model that they map to FHIR, and most of the new FHIR-only servers use a database in which the FHIR data is stored as a binary blob, with the searcheable fields split off in separate columns (or a joined search table).
That said, there could be a "best practice" for mapping resources to RDBMS tables - I think I heard someone talking about creating such a document - but I don't recall who.
Michel Rutten (Nov 20 2018 at 13:57):
Hi @Juan Antonio De los Cobos, FYI there is an active discussion going on about mapping FHIR to SQL within the analytics on FHIR stream, e.g.:
https://chat.fhir.org/#narrow/stream/73-analytics-on.20FHIR/subject/Connectathon.20track
Also see @Ryan Brush's Github repo:
https://github.com/rbrush/sql-on-fhir
Ewout Kramer (Nov 20 2018 at 14:43):
Now I remember :grinning:
Juan Antonio De los Cobos (Nov 20 2018 at 15:21):
oh, thank you @Michel Rutten
Juan Antonio De los Cobos (Nov 20 2018 at 15:24):
@Ewout Kramer you remember?, could you tell me?, thanks
Ewout Kramer (Nov 20 2018 at 16:41):
Well, I forgot the sql-on-fhir stuff from Ryan. Michel was right.
Juan Antonio De los Cobos (Feb 05 2019 at 15:31):
Thank you Michel, @Ryan Brush could you help me. I understood that i can create queries using sql, but the question is. How can i connect to my resources local server to execute these sentences to obtain the results??
nicola (RIO/SS) (Feb 06 2019 at 07:29):
sql-on-fhir moved to fhir organization
Here is some related posts - https://medium.com/fhirbase-dojo
nicola (RIO/SS) (Feb 06 2019 at 07:32):
sql on fhir based on nested structure like json/or struct/ composite type. Our attempt to implement flat relational model failed because of about 2K tables was generated, joins are slow and many other reasons. If you want to save just a couple of resources - maybe you can go this way.
nicola (RIO/SS) (Feb 06 2019 at 07:33):
You are welcome to our dedicated stream - https://chat.fhir.org/#narrow/stream/179219-analytics-on.20FHIR - to discuss your concerns.
nicola (RIO/SS) (Feb 06 2019 at 07:37):
@Ryan Brush is mostly interested in analytic on FHIR, when fhirbase is designed to be a database for your FHIR server :)
Kevin Mayfield (Feb 06 2019 at 08:26):
Re models. This isn't a 100% match to the FHIR (STU3) model, but close. https://www.openhealthhub.org/t/exploring-fhir-model-database/1987
It is a relational database model using HAPI RESTful server.
nicola (RIO/SS) (Feb 06 2019 at 10:10):
@Kevin Mayfield as I see from diagrams, this schema makes a lot of assumptions and narrow FHIR resources, i.e. it's lossy. For example Observation.code.coding is a collection, but in Observation table, you have only one column for that. No extensions support
Kevin Mayfield (Feb 07 2019 at 19:50):
Yes, that was done on purpose. It is designed mimic a traditional PAS/EPR SQL model, so it tends to match constraints they would have.
We have another system which stores the full resources but it's NoSQL/Mongo and it's not easy to show relationships between resources.
Last updated: Apr 12 2022 at 19:14 UTC
