Physical Model
In SanteDB, the physical model is used to express the concrete tables which are created in a SQL based RDBMS to store data on a physical media (disk). Implementers of new SQL based persistence services should try to re-use these mappings in their expression to the physical model.
SanteDB uses the ORM Lite data mapping technology, and the SanteDB.Persistence.Data.Ado
assembly contains the ORM lite physical model classes.
Data Providers
SanteDB includes an implementation of a persistence layer which includes support for generic ADO based databases. This provider uses the ADO.NET wrapper to invoke necessary functions and select data from tables. In order to leverage the ADO.NET persistence service, the ADO.NET provider must support:
SQL92 syntax
Limit and offset queries in the form of
SELECT FOO FROM BAR OFFSET x LIMIT y
Invoke of Stored Procedures and Functions or equivalent C# logic which implements the functions
Pooled and thread-safe access to the underlying disk structure.
Currently SanteDB iCDR works with the following OrmLite data providers:
Database System
Invariant
PostgreSQL 9.4 – 10.0
npgsql
FirebirdSQL 3.0
FirebirdSQL
SQLite 3.3
Sqlite
Regardless of the RDBMS system feeding the ADO provider, it must follow the schema specified in this section.
Implementing new Data Providers
To implement a new ADO.NET based data provider, implementers will need to:
Write an ORM Lite
IDbProvider
instance (see an example of the FirebirdSQL provider)Write an ORM Lite
IDbConfigurationProvider
instance (see an example of the FirebirdSQL Configuration provider)The DDL and DML classes for the most recent SanteDB physical model (see an example of the FirebirdSQL DDL files)
You should include these as embedded resources in your plugin
You should include all updates , translated to your SQL dialect as well
You should include your own provider's invariant in the header
Implementations of any supported extended functions (such as SOUNDEX, LEVENSHTEIN, etc.) as
IDbFilterFunction
implementations (see an example FirebirdSQL String Matching Functions)
Patching SQL Tables
See: Database Patching article for information on patching SQL structures.
Conventions
The physical schema of the ADO provider is designed such that many different ADO providers can support the data structures. Because of the limitations on some RDBMS systems, the ADO schema uses a short-hand for table names.
Suffixes
The following suffixes are placed on entities in the schema:
Suffix
Used For
Example
_TBL
Tables
ENT_VRSN_TBL
_VW
Views
ENT_CUR_VRSN_VW
_CDTBL
Code Lookup Tables
ENT_REL_CLS_CDTBL
_SYSTBL
Internal System Tables
PATCH_DB_SYSTBL
General Naming Conventions
Most tables in the SanteDB physical model carry common abbreviations of names. Because of the varied contributors some tables break these rules, however the general principal applies:
Names less than four characters should be represented in full (example:
NAME
orTAG
)Names which represent a classified object carry their classification, for example:
OBS_TBL
=> Observation TableSUB_ADM_TBL
=> Substance Administration Table
Association tables which exist only to link together two other tables should have
ASSOC
in their nameLonger table names are usually acronyms:
CD_TBL
=> Concept Definition TableCS_TBL
=> Code System Table
Table and columns should remove vowels where the removal of vowels do no interfere with understanding the meaning of the column or table:
ENT_TBL
=> Entity TableCLS_CD_ID
=> Class Concept Definition ID
Inherited Tables
Tables which represent an inheritance are created as discrete tables. Each class in the hierarchy maps to one table in the schema and contain only the attributes of the class.
As an example:
// Entity
public class Entity
{
}
// Person IS A Entity
public class Person : Entity
{
}
// Patient IS A Person
public class Patient : Person
{
}
In the database schema this inheritance structure is represented as:
CREATE TABLE ENT_TBL
(
ENT_ID UUID NOT NULL,
...
CONSTRAINT PK_ENT_TBL PRIMARY KEY (ENT_ID)
);
-- PERSON IS A ENTITY
CREATE TABLE PSN_TBL
(
ENT_ID UUID NOT NULL,
...
CONSTRAINT PK_PSN_TBL PRIMARY KEY (ENT_ID)
CONSTRAINT FK_PSN_ENT_TBL FOREIGN KEY (ENT_ID) REFERENCES ENT_TBL(ENT_ID)
);
-- PATIENT IS A PERSON
CREATE TABLE PAT_TBL
(
ENT_ID UUID NOT NULL,
...
CONSTRAINT PK_PAT_TBL PRIMARY KEY (ENT_ID)
CONSTRAINT FK_PAT_PSN_TBL FOREIGN KEY (ENT_ID) REFERENCES PSN_TBL(ENT_ID)
);
Versioning
On persistence services which provide versioning (such as those in the iCDR) the tables can be split into two categories:
Versioned Tables -> Acts, Entities and Concepts which track changes to data over time.
Non-Versioned Tables -> Any other class which does not track changes over time.
Because version data is required for interfaces in the iCDR the structures are broken into a primary table and a versioning table, where:
Primary Table: Contains the unchanging attributes of the object
Version Table: Contains the attributes which may be changed on the object. This table contains:
Sequence Identification (used for tracking the order of versions)
Creation time and provenance (who created the version)
Obsolete time and provenance (who rendered this version obsolete)
Replaced version id (the version that this version replaces)
For example, ENT_TBL
and ENT_VRSN_TBL
are related in this manner:
CREATE TABLE ENT_TBL (
ENT_ID UUID NOT NULL,
-- NON-VERSIONED PROPERTIES
CONSTRIANT PK_ENT_TBL PRIMARY KEY (ENT_ID)
);
-- ENTITY VERSION TABLE
CREATE TABLE ENT_VRSN_TBL (
ENT_VRSN_ID UUID NOT NULL,
VRSN_SEQ_ID BIGINT NOT NULL, -- THE VERSION SEQUENCEING IDENTIFIER
ENT_ID UUID NOT NULL, -- THE ENTITY THIS VERSION APPLIES TO
CRT_UTC TIMESTAMP NOT NULL, -- THE TIME THE VERSION WAS CREATED
CRT_PROV_ID UUID NOT NULL, -- THE PROVENANCE THAT CREATED THIS
OBSLT_UTC TIMESTAMP, -- THE TIME THE VERSION IS NO LONGER VALID
OBSLT_PROV_ID UUID, -- THE PROVENANCE OF WHAT OBSOLETED THIS
RPLC_VRSN_ID UUID, -- THE REPLACED VERSION
... -- VERSIONED DATA FIELDS
CONSTRAINT PK_ENT_VRSN_TBL PRIMARY KEY (ENT_VRSN_ID),
CONSTRAINT FK_ENT_VRSN_ENT_TBL FOREIGN KEY (ENT_ID) REFERENCES ENT_TBL(ENT_ID),
CONSTRAINT FK_ENT_VRSN_RPLC FOREIGN KEY (RPLC_VRSN_ID) REFERENCE ENT_VRSN_TBL(ENT_VRSN_ID)
);
CREATE UNIQUE INDEX ENT_VRSN_SEQ_UQ_IDX ON ENT_VRSN_TBL(VRSN_SEQ_ID);
Tables which are associated with a versioned entity should be linked to the primary key of the non-versioned portion of the data, and carry an Effective Version Seq
and an Obsolete Version Seq.
For example, identifiers for an entity:
-- ENTITY IDENTIFIER
CREATE TABLE ENT_ID_TBL (
ENT_ID_ID UUID NOT NULL, -- SURROGATE PRIMARY KEY
ENT_ID UUID NOT NULL, -- THE ENTITY TO WHICH THIS IDENTIFIER IS RELATED
EFFT_VRSN_SEQ_ID BIGINT NOT NULL, -- THE VERSION OF THE ENTITY WHERE THIS ID WAS VALID
OBSLT_VRSN_SEQ_ID BIGINT, -- THE VERSION OF THE ENTITY WHERE THIS ID IS NO LONGER VALID
... -- DATA FIELDS
CONSTRAINT PK_ENT_ID_TBL PRIMARY KEY (ENT_ID_ID),
CONSTRAINT FK_ENT_ID_ENT_TBL FOREIGN KEY (ENT_ID) REFERENCES ENT_TBL(ENT_ID),
CONSTRAINT FK_ENT_ID_EFFT_VRSN_TBL FOREIGN KEY (EFFT_VRSN_SEQ_ID) REFERENCES ENT_VRSN_TBL(VRSN_SEQ_ID),
CONSTRAINT FK_ENT_ID_OBSLT_VRSN_TBL FOREIGN KEY (OBSLT_VRSN_SEQ_ID) REFERENCES ENT_VRSN_TBL(VRSN_SEQ_ID)
)
Last updated
Was this helpful?