Data Storage
Introduction
Every DCP backend component has their own implementation to persist data. Other modules do not have direct access to this data using SQL. If data needs to be shared across components, data is always transferred via HTTP(S) calls.
DCP doesn't use a fully normalized structure, instead it takes advantage of the option to store JSON Documents in a SQL table, which was introduced in MS SQL server 2016. Storing in JSON has several advantages:
- storage of complex objects, different model classes, format changes, etc.
As JSON queries are only partly supported by entity framework (v8.0), raw JSON queries may be used, however using LINQ and the ORM remains the preferred way for accessing data.
Inside DCP the code-first approach with running migrations is used. Therefore, the database contains a separated table named MigrationHistories. The table is used by entity framework to detect which migrations need to be applied in order to match the database with the DbContext in the application.
Column descriptions are maintained using custom annotations and written to the database.
To capture the changes made to electronic records, these tables are system versioned. System versioned or temporal tables have built-in support for providing information about data stored in the table at any point in time, rather than only the data that is correct at the current moment in time. A temporal table contains a reference to another table with a mirrored schema. The system uses this table to automatically store the previous version of the row each time a row in the temporal table gets updated or deleted. Doing so, the full history of the record can be retrieved. Like in other frameworks (e.g. DevExpress) this feature is used to build/implement a secure audit trail.
Timestamps stored in DCP databases are always in UTC timezone and use ISO8601 format.