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.
Version 2.5 of SanteDB is undergoing a major code refactor. The physical model classes in the 2.5.x series of SanteDB will reside in the SanteDB.Persistence.Data assembly.

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:
1.Write an ORM Lite IDbProvider instance (see an example of the FirebirdSQL provider)
2.Write an ORM Lite IDbConfigurationProvider instance (see an example of the FirebirdSQL Configuration provider)
3.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
4.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 or TAG)
Names which represent a classified object carry their classification, for example:
OBS_TBL => Observation Table
SUB_ADM_TBL => Substance Administration Table
Association tables which exist only to link together two other tables should have ASSOC in their name
Longer table names are usually acronyms:
CD_TBL => Concept Definition Table 
CS_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 Table
CLS_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:
1
// Entity 
2
public class Entity 
3
{
4
}
5
​
6
// Person IS A Entity
7
public class Person : Entity
8
{
9
}
10
​
11
// Patient IS A Person
12
public class Patient : Person
13
{
14
}
Copied!
In the database schema this inheritance structure is represented as:
1
CREATE TABLE ENT_TBL
2
(
3
    ENT_ID UUID NOT NULL,
4
    ...
5
    CONSTRAINT PK_ENT_TBL PRIMARY KEY (ENT_ID)
6
);
7
​
8
-- PERSON IS A ENTITY
9
CREATE TABLE PSN_TBL
10
(
11
    ENT_ID UUID NOT NULL,
12
    ...
13
    CONSTRAINT PK_PSN_TBL PRIMARY KEY (ENT_ID)
14
    CONSTRAINT FK_PSN_ENT_TBL FOREIGN KEY (ENT_ID) REFERENCES ENT_TBL(ENT_ID)
15
);
16
​
17
-- PATIENT IS A PERSON
18
CREATE TABLE PAT_TBL
19
(
20
    ENT_ID UUID NOT NULL,
21
    ...
22
    CONSTRAINT PK_PAT_TBL PRIMARY KEY (ENT_ID)
23
    CONSTRAINT FK_PAT_PSN_TBL FOREIGN KEY (ENT_ID) REFERENCES PSN_TBL(ENT_ID)
24
);
25
​
Copied!
On a versioned database each class down the hierarchy should point to the version identification of the superclass rather than the identifier of the superclass - this is to track versioned changes to first class properties.
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.
The legacy dCDR SQLite provider is un-versioned. This means all of its tables are not versioned.
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:
1
CREATE TABLE ENT_TBL (
2
    ENT_ID UUID NOT NULL,
3
    -- NON-VERSIONED PROPERTIES
4
    CONSTRIANT PK_ENT_TBL PRIMARY KEY (ENT_ID)
5
);
6
​
7
-- ENTITY VERSION TABLE
8
CREATE TABLE ENT_VRSN_TBL (
9
    ENT_VRSN_ID UUID NOT NULL,
10
    VRSN_SEQ_ID BIGINT NOT NULL, -- THE VERSION SEQUENCEING IDENTIFIER
11
    ENT_ID UUID NOT NULL, -- THE ENTITY THIS VERSION APPLIES TO 
12
    CRT_UTC TIMESTAMP NOT NULL, -- THE TIME THE VERSION WAS CREATED
13
    CRT_PROV_ID UUID NOT NULL, -- THE PROVENANCE THAT CREATED THIS
14
    OBSLT_UTC TIMESTAMP, -- THE TIME THE VERSION IS NO LONGER VALID
15
    OBSLT_PROV_ID UUID, -- THE PROVENANCE OF WHAT OBSOLETED THIS
16
    RPLC_VRSN_ID UUID, -- THE REPLACED VERSION
17
    ... -- VERSIONED DATA FIELDS
18
    CONSTRAINT PK_ENT_VRSN_TBL PRIMARY KEY (ENT_VRSN_ID),
19
    CONSTRAINT FK_ENT_VRSN_ENT_TBL FOREIGN KEY (ENT_ID) REFERENCES ENT_TBL(ENT_ID),
20
    CONSTRAINT FK_ENT_VRSN_RPLC FOREIGN KEY (RPLC_VRSN_ID) REFERENCE ENT_VRSN_TBL(ENT_VRSN_ID)
21
);
22
​
23
CREATE UNIQUE INDEX ENT_VRSN_SEQ_UQ_IDX ON ENT_VRSN_TBL(VRSN_SEQ_ID);
Copied!
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:
1
-- ENTITY IDENTIFIER
2
CREATE TABLE ENT_ID_TBL (
3
    ENT_ID_ID UUID NOT NULL, -- SURROGATE PRIMARY KEY
4
    ENT_ID UUID NOT NULL, -- THE ENTITY TO WHICH THIS IDENTIFIER IS RELATED
5
    EFFT_VRSN_SEQ_ID BIGINT NOT NULL, -- THE VERSION OF THE ENTITY WHERE THIS ID WAS VALID
6
    OBSLT_VRSN_SEQ_ID BIGINT, -- THE VERSION OF THE ENTITY WHERE THIS ID IS NO LONGER VALID
7
    ... -- DATA FIELDS
8
    CONSTRAINT PK_ENT_ID_TBL PRIMARY KEY (ENT_ID_ID),
9
    CONSTRAINT FK_ENT_ID_ENT_TBL FOREIGN KEY (ENT_ID) REFERENCES ENT_TBL(ENT_ID),
10
    CONSTRAINT FK_ENT_ID_EFFT_VRSN_TBL FOREIGN KEY (EFFT_VRSN_SEQ_ID) REFERENCES ENT_VRSN_TBL(VRSN_SEQ_ID),
11
    CONSTRAINT FK_ENT_ID_OBSLT_VRSN_TBL FOREIGN KEY (OBSLT_VRSN_SEQ_ID) REFERENCES ENT_VRSN_TBL(VRSN_SEQ_ID)
12
) 
Copied!

Extended Data

Act Data Dictionary

Last modified 7mo ago

Copy link

Contents

Data Providers

Implementing new Data Providers

Patching SQL Tables

Conventions

Suffixes

General Naming Conventions

Inherited Tables

Versioning