SSW Update - What sort of primary keys do you use - Natural or Surrogate?
Microsoft Gold Partner Logo

 

[ the cutting edge ]

G'Day Developers!

Now this is a controversial one. Which one do you use?

  1. A "Natural" (or "Intelligent") key is actual data
    • Surname, FirstName, DateOfBirth
  2. An "Acquired Surrogate" (or "Artifical" or "System Generated") key is NOT derived from data eg. Autonumber
    • eg. ClientID 1234
    • eg. ClientID JSKDYF
    • eg. ReceiptID 1234
  3. A "Derived Surrogate" (or "User Provided") key is indirectly derived from data eg. Autonumber
    • eg. ClientID SSW (for SSW)
    • eg. EmpID AJC (for Adam Jon Cogan)
    • eg. ProdID CA (for Code Auditor)

The problems with Natural Keys:

  • Because they have a business meaning, if that meaning changes (eg. they change their surname), then that value NEEDS to changed. Changing a value with data is a little hard - but a lot easier with Cascade Update.
  • The main problem is that the key is large and combined and this needs to be used in all joins

The Problem with Acquired Surrogate Keys:

  • A surrogate key has no meaning to a user
  • It always requires a join when browsing a child table eg. The InvoiceDetail table

The Problem with Derived Surrogate

  • The user needs to enter a unique value
  • Because they have a business meaning, if that meaning changes (eg. they change their company name), then that value MAY NEED to changed. Changing a value with data is a little hard - but a lot easier with Cascade Update
  • More likely to have a problem with Merge Replication

So our recommendations are:

Sample code for the SSW Derived Surrogate:

Cheers until next time, Adam
SSW Chief Architect and Microsoft Regional Director, Australia 

Got a comment for Adam? 6164616d636f67616e407373772e636f6d2e6175

Sydney .NET User Group -
The best place to learn .NET for free in Sydney

User Group

2 days to go!

Sydney .NET User Group
Wednesday 21st November 2007
5:45pm - 9:00pm
Microsoft, Sydney

Visual Studio 2008 and LINQ

Come and hear Adam Cogan and Eric Phan present Visual Studio 2008 and LINQ

LINQ is the future of ORM on the .NET platform and with the release of Visual Studio 2008 only months away it will become mainstream. In this presentation you will learn about the different types of LINQ and how to use LINQ to quickly build data-driven applications.

See You There!!!| Sorry I can't make it

Featured Product: SSW .NET Toolkit

SSW .NET Toolkit Develop applications more efficiently while following best practices with SSW .NET Toolkit

The SSW .NET Toolkit is an example of a distributed multi-layer application based on the Microsoft Northwind database. Designed by Developers for Developers, the real-world source can be used as a model and basis for custom-designed applications perfect for small and medium enterprise (SME) applications. Microsoft's examples like Duwamish and IBuySpy are code intensive and marketed towards architects, not developers. The SSW .NET Toolkit delivers more functionality using less code.

More on SSW's .NET Toolkit

Advertise with SSW

If you would like to advertise your product or service with SSW or would like to find out more about various advertising opportunities, please email your enquiry to 736f7068696562656c6c65407373772e636f6d2e6175
or call: +61 2 9953 3000.
Copyright ?SSW 1990-2007. All Rights Reserved. Proudly developed by SSW Sydney Web Design
Advertise with SSW| Unsubscribe | Previous SSW Updates | Got a Hot Tip to share? | Got a comment to make?