Database Schema with a shared identity Field

Question

I'm a developer treading into the database world so bear with me please.

I'm creating a Contract database with separate tables for various types of contracts.

I have 5 tables but would like to share a common 'Identity' field between them, so that there will be a unique contract # for any given contract regardless of type.

What is the recommended way to do this? Something that won't over complicate simple crud transactions?

My application will be using a Sql Server 2008 R2 server..the app itself is Silverlight 4 using WCF Ria services for CRUD transactions.

A schema idea would be great, a tutorial of something similar ideas and technology would be even better.

Thanks for looking Robert

• gbn: possible duplicate of Emulate a TSQL sequence via a stored procedure
• gbn: Actually, I'm wrong. This can be done cleanly without sequences

Answer 1

Until SQL Server supports sequences (next version "Denali") then you'll have to have a common table.

However, if I understand you, I think you're looking at the subtype/supertype pattern. A sequence would be nice but if you designed using, say, Object Role Modelling then you'd generate this pattern/schema

Basically, you have a common "Contract" table:

ContractID, PK, UQ1 (Identity)
ContractType, FK, UQ1
InsertedDateTime
UpdatedDateTime
CounterParty

Notes:

• the PK is ContractID to give a unique value
• ContractType defines your "various types". Let's go for Tom, Dick and Harry as types
• The unique key UQ1 is on both ContractID, ContractType (this is a "super key")
• InsertedDateTime, CounterParty etc are common attributes

Then you have sub-tables with specific attributes for each contract type

• ContractTom: ContractID, ContractType, TomAttrib1, TomAttrib2, ..., TomAttribn
• ContractDick: ContractID, ContractType, DickAttrib1, DickAttrib2
• ContractHarry: ContractID, ContractType, HarryAttrib1, HarryAttrib2, HarryAttrib3

Notes:

• ContractID, ContractType are both the PK and FK.
• ContractType has CHECK constraint to restrict to Tom or Dick or Harry

• Mike Sherrill 'Cat Recall': +1. I'd only add that all the attibutes that apply to all contracts go in the "Contract" table, or reference the "Contract" table through foreign keys. For example, "date signed" probably belongs in the Contract table; names of the people who signed it should probably reference the Contract table through foreign keys. Also, it helps to have one updatable view per contract type, and to restrict most data access to those views.
• gbn: @Catcall: Proper normalisation and design for the rest goes without saying. I'd also consider using a view too.

Answer 2

Doing this requires either having a single table that you insert new rows into and get the values from there, or you write your own sequence process until you can upgrade to SQL Server "Denali" which supports this out of the box.

Answer 3

I prefer separated table that stores last unique identifier for a some scope:

SCOPE_NAME
START_VALUE
END_VALUE
CURRENT_VALUE

An identity does not restore its value after transaction is roll backed. The same situation in Oracle (sequences as I know) and the same will be in SQL Server "Denali" for sequences (I think).