Manage Learn to apply best practices and optimize your operations.

Columns and objects to support a surrogate key architecture

Ask a group of database administrators if they prefer surrogate keys over natural data keys to implement relationships between tables, and chances are you'll get some very different opinions. In the second part of this special feature, contributor Brian Walker supports the use of surrogate keys by providing the framework needed for such architecture.

My previous article described how and why to use a surrogate key structure, as an alternative to natural data keys, to implement relationships between tables. It also provides eight stored procedures to support the architecture. This article builds on that foundation with a framework for the proposed database architecture. It describes six standard table columns, six standard stored procedures, one standard user-defined function and two standard triggers. It also provides 12 stored procedures to create and manage the standard objects.

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

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

Dig Deeper on SQL Server Database Modeling and Design

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.