{"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":"2017-01-23T16:21:49","modified_gmt":"2017-01-23T21:21:49","slug":"master-table-design","status":"publish","type":"post","link":"https:\/\/www.caskeys.com\/dc\/master-table-design\/","title":{"rendered":"Master Table Design"},"content":{"rendered":"

Introduction<\/h1>\n

Master table layout and creation. The master table controls all data tables via one to one relationship and carries audit info.<\/p>\n

 <\/p>\n

Layout<\/h2>\n

 <\/p>\n\n\n\n<\/tfoot>\n\n\n\n\n\n\n\n\n\n\n\n\n
Column<\/th>\nType<\/th>\nDescription<\/th>\n<\/tr>\n<\/thead>\n
id<\/td>\nint (Auto Increment)<\/td>\nPrimary key for master table. All data tables must include an ID field (NOT auto increment) linked to this field via one to one relationship.<\/td>\n<\/tr>\n
id_group<\/td>\nint<\/td>\nVersion linking ID. Multiple entries share an identical group ID to identify them as a single record with multiple versions. If no previous versions of a new record exist, then this column is seeded from ID field after initial creation.<\/td>\n<\/tr>\n
active<\/td>\nbit<\/td>\nIf TRUE, marks this entry as the active version of a record. Much faster than a date lookup and necessary for soft delete.<\/td>\n<\/tr>\n
create_by<\/td>\nint<\/td>\nAccount creating this version. -1 = Unknown.<\/td>\n<\/tr>\n
create_host<\/td>\nvarchar(50)<\/td>\nHost (usually IP provided by control code) creating entry.<\/td>\n<\/tr>\n
create_time<\/td>\ndatetype2<\/td>\nTime this entry was created.<\/td>\n<\/tr>\n
create_etime<\/td>\nComputed column<\/td>\nElapsed time in seconds since entry was created.<\/td>\n<\/tr>\n
update_by<\/td>\nSame as create_x<\/em>, but updated on every CRUD operation.<\/td>\n<\/tr>\n
update_host<\/td>\n<\/tr>\n
update_time<\/td>\n<\/tr>\n
update_etime<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Set Up<\/h2>\n
\r\nCREATE TABLE [dbo].[_a_tbl_master](\r\n\tid\t\t\t\tint IDENTITY(1,1)\tNOT NULL,\t\t\t\t\t\t-- Primary unique key.\r\n\tid_group\t\tint\t\t\t\t\tNULL,\t\t\t\t\t\t\t-- Primary record key. All versions of a given record will share a single group ID.\r\n\tactive\t\t\tbit\t\t\t\t\tNOT NULL,\t\t\t\t\t\t-- Is this the active version of a record? TRUE = Yes.\r\n\t\t-- Audit info for creating version. A new \r\n\t\t-- version is created on any CRUD operation \r\n\t\t-- in the data tables controlled by master.\r\n\tcreate_by\t\tint\t\t\t\t\tNOT NULL,\t\t\t\t\t\t-- Account creating this version. -1 = Unknown.\r\n\tcreate_host\t\tvarchar(50)\t\t\tNOT NULL,\t\t\t\t\t\t-- Host (usually IP from control code) creating version. \r\n\tcreate_time\t\tdatetime2\t\t\tNOT NULL,\t\t\t\t\t\t-- Time this version was created. \r\n\tcreate_etime\tAS (datediff(second, [create_time], getdate())),\t-- Elapsed time in seconds since creation.\t\r\n\t\t-- Audit information for updating version.\r\n\t\t-- When any CRUD is performed on a data\r\n\t\t-- table, the previously active version\r\n\t\t-- is marked inactive. Deleting a record\r\n\t\t-- simply marks all versions inactive.\r\n\t\t-- In short, the only updates made to \r\n\t\t-- a master table are toggling Active\r\n\t\t-- flag.\r\n\tupdate_by\t\tint\t\t\t\t\tNOT NULL,\t\t\t\t\t\t-- Account updating this version. -1 = Unknown.\r\n\tupdate_host\t\tvarchar(50)\t\t\tNOT NULL,\r\n\tupdate_time\t\tdatetime2\t\t\tNOT NULL,\r\n\tupdate_etime\tAS (datediff(second, update_time, getdate())),\r\n \r\nCONSTRAINT PK__a_tbl_master PRIMARY KEY CLUSTERED \r\n(\r\n\tid ASC\r\n) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\r\n) ON [PRIMARY]\r\n\r\nGO\r\n\r\nALTER TABLE _a_tbl_master ADD CONSTRAINT DF__a_tbl_master_active\t\tDEFAULT ((1))\t\t\tFOR active\r\nGO\r\n\r\nALTER TABLE _a_tbl_master ADD CONSTRAINT DF__a_tbl_master_create_by\t\tDEFAULT ((-1))\t\t\tFOR create_by\r\nGO\r\n\r\nALTER TABLE _a_tbl_master ADD CONSTRAINT DF__a_tbl_master_create_host\tDEFAULT (host_name())\tFOR create_host\r\nGO\r\n\r\nALTER TABLE _a_tbl_master ADD CONSTRAINT DF__a_tbl_master_created\t\tDEFAULT (getdate())\t\tFOR create_time\r\nGO\r\n\r\nALTER TABLE _a_tbl_master ADD CONSTRAINT DF__a_tbl_master_update_host\tDEFAULT (host_name())\tFOR update_host\r\nGO\r\n<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
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_post_was_ever_published":false,"_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}},"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":7,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/posts\/6135\/revisions"}],"predecessor-version":[{"id":6143,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/posts\/6135\/revisions\/6143"}],"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}]}}