{"id":6135,"date":"2017-01-10T20:37:26","date_gmt":"2017-01-11T01:37:26","guid":{"rendered":"https:\/\/www.caskeys.com\/dc\/?p=6135"},"modified":"2026-05-19T11:43:06","modified_gmt":"2026-05-19T15:43:06","slug":"master-table-design","status":"publish","type":"post","link":"https:\/\/www.caskeys.com\/dc\/master-table-design\/","title":{"rendered":"Master Table Versioning Concept"},"content":{"rendered":"\n<h1 class=\"wp-block-heading\">Introduction<\/h1>\n\n\n\n<p class=\"wp-block-paragraph\">This was an idea I played around with for centralized versioning. It&#8217;s nothing too ground breaking. I need to add live versioning to some tables. Temporal support looks promising, but I don&#8217;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. \ud83d\ude42<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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&#8217;s merit for something non-mission critical, so here&#8217;s the basic design if anyone wants to run with it. \u00a0<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Layout<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Master table layout and creation. The master table controls all data tables via one to one relationship and carries audit info. <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>We link all other tables in the schema in a one-to-one relationship. <\/li>\n\n\n\n<li>All CRUD operations start by updating the master table to populate audit fields and obtain an id before the local update proceeds.<\/li>\n\n\n\n<li>An active bit flag marks the currently active record to simplify queries and avoid any potential time zone issues from relying on timestamps..<\/li>\n\n\n\n<li>Forward only operation. We do not delete or alter records &#8211; only create and set active cursor accordingly.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-table is-style-stripes\"><table class=\"has-fixed-layout\"><thead><tr><th>Column<\/th><th>Type<\/th><th>Details<\/th><\/tr><\/thead><tbody><tr><td>id<\/td><td>int<\/td><td>Primary key for master table. Auto-incremented.<br>All controlled data tables must include an id field<br>that is not auto-incremented and links to this field<br>through a one-to-one relationship.<\/td><\/tr><tr><td>id_group<\/td><td>int<\/td><td>Version linking id. Multiple entries share an<br>identical group id to identify them as versions of<br>the same logical record. If no previous versions<br>exist, this column is seeded from the id field after<br>initial creation.<\/td><\/tr><tr><td>active<\/td><td>bit<\/td><td>If true, marks this entry as the active version of<br>a record. This allows faster lookup than a date<br>comparison and supports soft delete behavior.<\/td><\/tr><tr><td>create_by<\/td><td>int<\/td><td>Account creating this version. -1 = unknown.<\/td><\/tr><tr><td>create_host<\/td><td>varchar(50)<\/td><td>Host, usually supplied by control code, creating<br>this version.<\/td><\/tr><tr><td>create_time<\/td><td>datetime2<\/td><td>Time this version was created.<\/td><\/tr><tr><td>update_by<\/td><td>int<\/td><td>Account responsible for the CRUD operation that<br>created or deactivated this version. -1 = unknown.<\/td><\/tr><tr><td>update_host<\/td><td>varchar(50)<\/td><td>Host responsible for the CRUD operation.<\/td><\/tr><tr><td>update_time<\/td><td>datetime2<\/td><td>Time this version was created or deactivated.<\/td><\/tr><\/tbody><tfoot><tr><td>Column<\/td><td>Type<\/td><td>Detail<\/td><\/tr><\/tfoot><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Set Up<\/h2>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: Code:; notranslate\" title=\"Code:\">\nCREATE TABLE &#x5B;dbo].&#x5B;_a_tbl_master](\n    id              int IDENTITY(1,1)   NOT NULL,\n    id_group        int                 NULL,\n    active          bit                 NOT NULL,\n\n        -- Audit information for creating this version.\n        -- A new version is created on any CRUD operation\n        -- in the data tables controlled by master.\n\n    create_by       int                 NOT NULL,\n    create_host     varchar(50)         NOT NULL,\n    create_time     datetime2           NOT NULL,\n\n        -- Audit information for the operation responsible\n        -- for this version. When any CRUD is performed on\n        -- a controlled data table, the previously active\n        -- version is marked inactive. Deleting a record\n        -- simply marks all versions inactive.\n        --\n        -- In short, the only routine update made to a master\n        -- table row is toggling the active flag.\n\n    update_by       int                 NOT NULL,\n    update_host     varchar(50)         NOT NULL,\n    update_time     datetime2           NOT NULL,\n\n    CONSTRAINT PK__a_tbl_master PRIMARY KEY CLUSTERED\n    (\n        id ASC\n    ) WITH (\n        PAD_INDEX = OFF,\n        STATISTICS_NORECOMPUTE = OFF,\n        IGNORE_DUP_KEY = OFF,\n        ALLOW_ROW_LOCKS = ON,\n        ALLOW_PAGE_LOCKS = ON\n    ) ON &#x5B;PRIMARY]\n) ON &#x5B;PRIMARY]\n\nGO\n\nALTER TABLE &#x5B;dbo].&#x5B;_a_tbl_master]\nADD CONSTRAINT DF__a_tbl_master_active\nDEFAULT ((1)) FOR active\nGO\n\nALTER TABLE &#x5B;dbo].&#x5B;_a_tbl_master]\nADD CONSTRAINT DF__a_tbl_master_create_by\nDEFAULT ((-1)) FOR create_by\nGO\n\nALTER TABLE &#x5B;dbo].&#x5B;_a_tbl_master]\nADD CONSTRAINT DF__a_tbl_master_create_host\nDEFAULT (host_name()) FOR create_host\nGO\n\nALTER TABLE &#x5B;dbo].&#x5B;_a_tbl_master]\nADD CONSTRAINT DF__a_tbl_master_create_time\nDEFAULT (getdate()) FOR create_time\nGO\n\nALTER TABLE &#x5B;dbo].&#x5B;_a_tbl_master]\nADD CONSTRAINT DF__a_tbl_master_update_by\nDEFAULT ((-1)) FOR update_by\nGO\n\nALTER TABLE &#x5B;dbo].&#x5B;_a_tbl_master]\nADD CONSTRAINT DF__a_tbl_master_update_host\nDEFAULT (host_name()) FOR update_host\nGO\n\nALTER TABLE &#x5B;dbo].&#x5B;_a_tbl_master]\nADD CONSTRAINT DF__a_tbl_master_update_time\nDEFAULT (getdate()) FOR update_time\nGO\n\nCREATE UNIQUE NONCLUSTERED INDEX UX__a_tbl_master_active_group\nON &#x5B;dbo].&#x5B;_a_tbl_master] (id_group)\nWHERE active = 1 AND id_group IS NOT NULL\nGO\n\nCREATE NONCLUSTERED INDEX IX__a_tbl_master_id_group\nON &#x5B;dbo].&#x5B;_a_tbl_master] (id_group, id)\nGO\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong>Note:<\/strong> For a newly inserted logical record, <code>id_group<\/code> may be temporarily null. After the initial master record is created, seed <code>id_group<\/code> from <code>id<\/code>. All later versions of that logical record should reuse the original <code>id_group<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Master table design to track all data CRUD operations.<\/p>\n","protected":false},"author":1,"featured_media":6206,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":true,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"Master table design to track all data CRUD operations. #dc_mssql_versioning_project","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[71],"tags":[299,253,271,269,254,298,286],"class_list":["post-6135","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology-temerity","tag-career-chaos","tag-coding-database","tag-projects-database-record-versioning","tag-applications-inspector-blair","tag-coding-sql","tag-technology-temerity","tag-uk-ehs"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.caskeys.com\/dc\/wp-content\/uploads\/2017\/01\/logo-sql.png","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5lNM5-1AX","jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/posts\/6135","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/comments?post=6135"}],"version-history":[{"count":14,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/posts\/6135\/revisions"}],"predecessor-version":[{"id":8021,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/posts\/6135\/revisions\/8021"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/media\/6206"}],"wp:attachment":[{"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/media?parent=6135"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/categories?post=6135"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/tags?post=6135"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}