Software development struggles with effective, efficient database design. This becomes even more prominent with an advent of databases operating in a context of distributed services.
The following is just another attempt to provide some basic guidelines around structured data organised in tables, but not neccessarily any specific relational database system. The goal was to provide a generic pattern that would operate across different systems and technologies - delivering completely vendor agnostic patterns. The design is meant to be optimised for performance, not consistency. The design implements basic versioning of entities, also allowing quick specification of when the entities become effective.
- Each entity should be identifiable by an unique identifier (ID) in a given record storage space.
- Each entity record should contain a field holding a deleted flag (RD) indicating removed record.
- Each entity record should contain field holding most precise timestamp of when the record was created (RC).
- Each entity record should contain a field holding a timestamp of a version used to create it (RP).
- Each entity record should contain a field holding information who owns the specific version record (RA).
- Each entity record should contain a field holding information on when the record becomes effective (RE).
- Master identity (MR) can contain a relation field that must point to a specific detail record (DR) by its identifier (ID).
- Master record (MR) can contain a relation field that may point to a specific detail record (DR) version (RC) by its timestamp.
- Master record (MR) can contain a relation field that may not point to a specific detail record (DR) version (RC) by using null.
- With each modification system should only contain copies of MR and DR with different (incremental) timestamps (RC).
- With each modification system should store a new version (RC) of a given entity (ID) pointing to its best known previous version (RP).
- With each modification system should detect conflicts by checking if previous version is the current version of an entity being saved.
- With each modification system may prevent conflicts where possible or detect them later attempting to merge or reject conflicted changes.
- With each deletion the system should create a new version of a deleted record marked with DF set to deleted (RD=1).
- Where master record points to a specific version of the detail record, the relation is fixed to a specific version of the detail record (RC).
- Where master record does not point to specific version of detail record, such relation maps to the effective (RE<=[NOW]), and newest (RC=[MAX]) detail record.
- Where multiple detail records (DR) must be aggregated, the relation must use the same value of [NOW] in building relation with for detail records.
- System implementing relations must detect collisions of identifiers (ID) in order to re-generate ID and retry persisting record in its storage space.
- System implementing relations must detect collisions of timestamps (RC) within the scope of each ID in order to re-generate RC and retry persisting record in its storage space.
- System implementing relations must retry a fixed number of times and raise an error in order to prevent unacceptable delays in persisting data in storage system.
- System implementing relations must implement only read/create operations preventing modifications or deletions of any existing records in storage system.
- Author
Maciej Zagozda
Company founder, and director of engineering.
- Comments
- Leave a Comment