Introduction
This was an idea I played around with for centralized versioning. It’s nothing too ground breaking. I need to add live versioning to some tables. Temporal support looks promising, but I don’t really know much about it yet. Even if I did, I think any feature less than a year in is too new to trust and I prefer direct control over workflow anyway. While rolling up my own, figured I could apply some DRY along the way. Centralized source and injected dependence appeal to the coder in me. 🙂
The concept is a single master table acting as parent, with all other tables linked through a one-to-one relationship. This would give us a single repository for all edits, delete control, and versioning. I ended up scrapping the idea because there are just too many brittle parts to break in production. Getting it production ready would need so many guardrails, constraints, and edge case rules it defeats the whole point of simplified control. Still, maybe there’s merit for something non-mission critical, so here’s the basic design if anyone wants to run with it. Â
Layout
Master table layout and creation. The master table controls all data tables via one to one relationship and carries audit info.
- We link all other tables in the schema in a one-to-one relationship.
- All CRUD operations start by updating the master table to populate audit fields and obtain an id before the local update proceeds.
- An active bit flag marks the currently active record to simplify queries and avoid any potential time zone issues from relying on timestamps..
- Forward only operation. We do not delete or alter records – only create and set active cursor accordingly.
| Column | Type | Details |
|---|---|---|
| id | int | Primary key for master table. Auto-incremented. All controlled data tables must include an id field that is not auto-incremented and links to this field through a one-to-one relationship. |
| id_group | int | Version linking id. Multiple entries share an identical group id to identify them as versions of the same logical record. If no previous versions exist, this column is seeded from the id field after initial creation. |
| active | bit | If true, marks this entry as the active version of a record. This allows faster lookup than a date comparison and supports soft delete behavior. |
| create_by | int | Account creating this version. -1 = unknown. |
| create_host | varchar(50) | Host, usually supplied by control code, creating this version. |
| create_time | datetime2 | Time this version was created. |
| update_by | int | Account responsible for the CRUD operation that created or deactivated this version. -1 = unknown. |
| update_host | varchar(50) | Host responsible for the CRUD operation. |
| update_time | datetime2 | Time this version was created or deactivated. |
| Column | Type | Detail |
Set Up
CREATE TABLE [dbo].[_a_tbl_master](
id int IDENTITY(1,1) NOT NULL,
id_group int NULL,
active bit NOT NULL,
-- Audit information for creating this version.
-- A new version is created on any CRUD operation
-- in the data tables controlled by master.
create_by int NOT NULL,
create_host varchar(50) NOT NULL,
create_time datetime2 NOT NULL,
-- Audit information for the operation responsible
-- for this version. When any CRUD is performed on
-- a controlled data table, the previously active
-- version is marked inactive. Deleting a record
-- simply marks all versions inactive.
--
-- In short, the only routine update made to a master
-- table row is toggling the active flag.
update_by int NOT NULL,
update_host varchar(50) NOT NULL,
update_time datetime2 NOT NULL,
CONSTRAINT PK__a_tbl_master PRIMARY KEY CLUSTERED
(
id ASC
) WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[_a_tbl_master]
ADD CONSTRAINT DF__a_tbl_master_active
DEFAULT ((1)) FOR active
GO
ALTER TABLE [dbo].[_a_tbl_master]
ADD CONSTRAINT DF__a_tbl_master_create_by
DEFAULT ((-1)) FOR create_by
GO
ALTER TABLE [dbo].[_a_tbl_master]
ADD CONSTRAINT DF__a_tbl_master_create_host
DEFAULT (host_name()) FOR create_host
GO
ALTER TABLE [dbo].[_a_tbl_master]
ADD CONSTRAINT DF__a_tbl_master_create_time
DEFAULT (getdate()) FOR create_time
GO
ALTER TABLE [dbo].[_a_tbl_master]
ADD CONSTRAINT DF__a_tbl_master_update_by
DEFAULT ((-1)) FOR update_by
GO
ALTER TABLE [dbo].[_a_tbl_master]
ADD CONSTRAINT DF__a_tbl_master_update_host
DEFAULT (host_name()) FOR update_host
GO
ALTER TABLE [dbo].[_a_tbl_master]
ADD CONSTRAINT DF__a_tbl_master_update_time
DEFAULT (getdate()) FOR update_time
GO
CREATE UNIQUE NONCLUSTERED INDEX UX__a_tbl_master_active_group
ON [dbo].[_a_tbl_master] (id_group)
WHERE active = 1 AND id_group IS NOT NULL
GO
CREATE NONCLUSTERED INDEX IX__a_tbl_master_id_group
ON [dbo].[_a_tbl_master] (id_group, id)
GO
Note: For a newly inserted logical record, id_group may be temporarily null. After the initial master record is created, seed id_group from id. All later versions of that logical record should reuse the original id_group.