Database Management
Home / Developer / Database Tier / [Management](Database -Management)
Data Management.
This page describes some aspects of how data is structured and managed in the PxStat database.
Generally speaking, in the interests of referential integrity, data in PxStat is soft-deleted rather than hard-deleted. Most tables contain a DELETE_FLAG column which is normally set to '0'. If a row is to be soft-deleted, this flag is set to '1'. All stored procedures are amended to take this into account when reading data.
There are a number of exceptions to this where data is hard-deleted because there is no requirement for soft-deletion:
Statistic information
- TD_STATISTIC
- TD_CLASSIFICATION
- TD_VARIABLE
- TD_FREQUENCY
- TD_PERIOD
- TD_DATA
- TM_DATA_CELL
Keyword information
- TD_KEYWORD_PRODUCT
- TD_KEYWORD_SUBJECT
- TD_KEYWORD_RELEASE
Foreign keys are, generally speaking, enforced via foreign key constraints. Exceptions to this is where (a) a hard delete is employed, and (b) the foreign key constraint would result in a deep search of a large table. In these cases, the foreign key, while it exists, is not asserted via a constraint.
Transactions enable a complex operation to be atomic, i.e. either it is completed in its entirety or not at all. Transactions are not initiated in any stored procedures in PxStat. This is because it common for single API calls to require many calls to different stored procedures. For this reason, transactions are initiated and controlled only by the server layer.
The transaction setting process is handled by the (Server API Library)[https://github.com/CSOIreland/Server-API-Library].
In PxStat, the transactions are set with a Snapshot Isolation level. Snapshot isolation means that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. No locks are acquired on the underlying data rows.
Indexing is used widely on all data tables in order to prioritise the retrieval of data. Indexes are set in the DDL as part of table creation.
PxStat uses Always Encrypted to hide specific data. For an overview of how Always Encrypted works, click on the following link: https://www.red-gate.com/simple-talk/sql/database-administration/sql-server-encryption-always-encrypted/
The following columns are encrypted in PxStat:
- TD_DATA.TDT_VALUE
- TD_MATRIX.MTR_INPUT
Generally speaking, you will be able read to and write from these columns from an external source on which the certificate is loaded. This applies to the application, the development environment and SSMS. You can not join these columns as part of a query because the database itself does not have the certificate installed.
For details of how Always Encrypted is installed, click here.
