Database schema

[niquola]

This question is very sensitive for different databases details.

Option 1:

create table patient (
  id text primary key,
  resource json
)

pros:

• schema can be used by most of the databases which have a json support
• All schemas are the same - no need to change the schema

[niquola]

We should think about strict schemas for google big queries (protobuf) or Apache Spark?

[johngrimes]

One of the challenges with these formats is that you can't have arbitrary nesting - which is problematic for extensions and nested structures like QuestionnaireResponse.item.

The only two options I know of here are to nominate a maximum level of nesting upon import, or create a separate table for these structures. From a performance perspective, you want to have the smallest possible schema that can accommodate your data.

[vadi2]

I think so - one of the big advantages of this standard would be for use in analytics solutions.

[gotdan]

Is there a large performance advantage to externalizing id vs. just indexing inside the json? If not, why pull it out rather than leave the ids in the json until the entire resource is converted into a flat representation?

[vneilley]

We've seen performance enhancements in externalizing IDs and other fields for individual indexing with large analytics use cases, over indexing within the JSON object, at least within columnar databases. See: https://chat.fhir.org/#narrow/stream/179219-analytics-on-FHIR/topic/JSON.20and.20Columnar.20tradeoffs

[niquola]

Is there a large performance advantage to externalizing id vs. just indexing inside the json? If not, why pull it out rather than leave the ids in the json until the entire resource is converted into a flat representation?

Yes, it is! Databases need primary keys :(

[gotdan]

Agreed! I wasn't sure if one could use a json path expression as a primary key, but just looked it up and at least for Postgres it has to be a column.

[johngrimes]

How do I know which version of FHIR it is?

[cfu288]

Following up on this question - Is it possible to query against multiple non-homogenous versions of FHIR? (DSTU2/R3/R4 in the same database)

[johngrimes]

What is the table naming convention for resources that contain more than one word, e.g. MedicationRequest?

[niquola]

resource type in lower case

[johngrimes]

How will resource versioning work?

Will queries need to take into account that there could be multiple IDs for the same resource with different business versions?

[niquola]

Can you elaborate on it?

[lschmierer]

I would suggest storing the history in a separate table as it needs to be accessed far less frequently (in my experience) and would otherwise slow down queries on the hot data (cache locality etc.)

Are versioned resources even in scope of sql-on-fhir? Or are only the latest resources interesting for analytics?

[ir4y]

While playing with this demo http://142.132.196.32:7777/#/console I notices that the DuckDB version uses first-level attributes as columns, while Postgres use only one column resource.

I am experimenting with SQLAlchemy to write portable queries but it is not possible now, since the table definitions are completely different:

# Postgres
class Patient(Base):
    __tablename__ = "patient"
    id = Column(Integer, primary_key=True)
    resource = Column(JSON)
    
# DuckDB
class Patient(Base):
    __tablename__ = "patient"
    
    id = Column(String, primary_key=True) 
    name = Column(ARRAY(DuckJSON))
    birthDate = Column(String) 
    gender = Column(String)    

I think we should use the same schema across all databases.

[lschmierer]

I like this schema, because it leaves space to add generated columns for commonly queried JSON fields/search parameter indexing/etc.