Stream: implementers
Topic: SQL Implementation
Max Milto (Oct 11 2017 at 13:13):
Has anyone experience with implementation of FHIR data structure on MS SQL?
Michel Rutten (Oct 11 2017 at 13:14):
My colleague @Christiaan Knaap certainly has.
Max Milto (Oct 11 2017 at 13:19):
Maybe you know...
Is his project open source?
Michel Rutten (Oct 11 2017 at 13:36):
Hi @Max Milto, our current FHIR Server (called "Vonk") is a commercial project and not open source. Vonk provides flexible support for different storage layers via facade interfaces and includes out-of-the-box support for MS SQL Server.
Earlier, we have published an open source reference implementation of a FHIR Server called "Spark":
https://github.com/furore-fhir/spark
However we no longer actively maintain the Spark repository.
Christiaan Knaap (Oct 11 2017 at 17:39):
But the give-away is: If you need to support many different or even all resourcetypes, don't try to put the resource in a schema, but only the search parameters. If you have a fixed profile for only a couple of resources you could map the resources to a table schema. But you'll end up with a lot of joining quickly due to the hierarchical nature of resources.
Vadim Potapenko (Oct 12 2017 at 05:10):
But the give-away is: If you need to support many different or even all resourcetypes, don't try to put the resource in a schema, but only the search parameters.
Why? Which issues we will have if decide to put resource in a schema? How you propose to store resources?
Grahame Grieve (Oct 12 2017 at 10:46):
if you are purely serving a FHIR Api, you just store the resources as a binary, and pull the values needed to support the search parameters out into one or more separate tables.
Grahame Grieve (Oct 12 2017 at 10:47):
if you're writing process code and don't want to be tied to the FHIR API - typically, this means you want to do analysis/aggregation - you will want to extract more/all information out of the resources.
nicola (RIO/SS) (Oct 12 2017 at 10:55):
Or you could use native JSON support in MSSQL - https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server. That's how we do it with PostgreSQL
Vadim Potapenko (Oct 12 2017 at 11:50):
We working on mobile application which have own db schema. We should simplify interoperability to support FHIR or it elements. Please, advise how this can be accomplished with minimal efforts?
Is it good idea to implement on db schema primitive and complex data types http://hl7.org/fhir/datatypes.html ? so later then FHIR support will be needed it will be easier to create resource data and exchange with third-party system.
Christiaan Knaap (Oct 12 2017 at 12:59):
Depending on the phase of development and the needs of your mobile app you can choose:
1. Use FHIR resources as 'native' storage, and use one of the generic FHIR servers available to do so, and in your app use the resources directly. Interoperablity will then be 'for free'.
2. Keep your existing db schema, but for interoperability you will have to make the mapping to (and maybe also from) FHIR resources somewhere. This can be:
2a. in a separate 'syncing app' that maps your data and puts a copy of it in the form of FHIR resources in a generic FHIR server, or
2b. by having a FHIR layer (a 'facade') on top of your database (or on top of a web API if you have that) that does the mapping on-the-fly.
Christiaan Knaap (Oct 12 2017 at 13:00):
BTW: JSON support in SQL Server is nowhere near as powerful as that in PostgreSQL.
Kevin Mayfield (Oct 12 2017 at 16:01):
HAPI JPA Server works fine on SQL Server but you need to mess around a bit with the JDBC drivers.
Kevin Mayfield (Oct 12 2017 at 16:02):
The structure behind this project (also HAPI based) follows the structure of FHIR model more closely https://github.com/nhsconnect/careconnect-reference-implementation We have changed a few items here and there for practical reason, CodeSystem is CodeSystem but Concept is separated out into another class/table.
Kevin Mayfield (Oct 12 2017 at 16:04):
Note: We are only implementing the most common elements and features - so it's not at the cutting edge of FHIR. The inital version of the server is here: http://194.189.27.193/careconnect/
Kevin Mayfield (Oct 12 2017 at 16:06):
We’ve created the tables using hibernate but you can use Hibernate to map an existing SQL schema. So what we showing is how a SQL database can be made available using HAPI FHIR. The database schema we have created is very similar to several EPR/PAS systems I’ve worked with.
Brian Postlethwaite (Oct 16 2017 at 01:29):
The Telstra Health sqlonfhir server also uses SQLServer underneath, and we store the FHIR resources as an XML column (instead of JSON) and we extract the search data into dedicated indexing tables/columns
Thomas Lukasik (Oct 20 2017 at 21:44):
@basil stolyarov This conversation may also provide some helpful insight WRT your question in the 'SQL DDL for FHIR' stream..
Last updated: Apr 12 2022 at 19:14 UTC
