Database Schema with a shared identity Field

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

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

StackExchange Database Administrators Q#4105, answer score: 6