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


  • 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


  • ContractID, ContractType are both the PK and FK.
  • ContractType has CHECK constraint to restrict to Tom or Dick or Harry
  • 1
    +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. – Mike Sherrill 'Cat Recall' Jul 26 '11 at 13:05
  • @Catcall: Proper normalisation and design for the rest goes without saying. I'd also consider using a view too. – gbn Jul 27 '11 at 6:21

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.


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


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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.