Manage Learn to apply best practices and optimize your operations.

Overview of proposed surrogate key architecture

Find out which columns and objects you need for a proposed surrogate key architecture.

The proposed database architecture offers up to six columns that could be included in every table. It's not likely that all six are needed for every database, but some are fairly universal. It would be best to use the same set of standard columns in every table of a particular database. That's not a requirement, but consistency is desirable; consistency allows for powerful dynamic routines that work for any table in the database.

The six standard columns are:

Name Data Type
CreateUser varchar(40) or int
CreateDate smalldatetime
ModifyUser varchar(40) or int
ModifyDate smalldatetime
DetectWork int
RecordMask tinyint

The CreateUser column is used to identify the user that originally created the row. The column may contain a SQL Server login name, or it may contain a foreign key to a system user table if the application being supported has internal security. This column would be populated by the application.

The CreateDate column is used to store the date and time when the row was originally created. This column could be populated by the application being supported, by a default value or by a trigger. This architecture suggests using a trigger.

The ModifyUser column is used to identify the user that last modified the row. The column may contain a SQL Server login name or it may contain a foreign key to a system user table if the application being supported has internal security. This column would be populated by the application.

The ModifyDate column is used to store the date and time when the row was last modified. This column could be populated by the application being supported or by a trigger. This architecture suggests using a trigger.

The DetectWork column is used to detect concurrent access to a particular row.


Columns and objects to support a surrogate key architecture

 Home: Introduction
 Part 1: Overview of proposed surrogate key architecture
 Part 2: Dealing with concurrent access
 Part 3: Standard routines for each table
 Part 4: Stored procedures: Create and manage standard objects

ABOUT THE AUTHOR:   
Brian Walker
Brian Walker is a senior database architect in an IS department that uses SQL Server 2000 and the .NET Framework. He has more than 25 years of experience in the IT industry with the last several years focused on databases and SQL Server. Walker is a software developer, database developer, database administrator and database consultant. He develops utility software as a hobby, including a large collection of SQL Server utilities.
Copyright 2006 TechTarget
This was last published in February 2006

Dig Deeper on SQL Server Database Modeling and Design

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close