CODEWITHSUNDEEP
sql-serversql-server-2005database-designprimary-key
Hituptony
Hituptony

Reputation: 2860

SQL Primary Key Decisions

In my scenario I am tracking a population of members and their doctor changes

The columns concerned are

MemberID | Prov_Nbr | Prov_Start_Date | Prov_End_Date | Prov_Update_Date

My question is in regards to a primary key

In this scenario, would it be better to have a primary key on an Auto-Increment field, and add the column to the front like so: 

IDENTITY |MemberID | Prov_Nbr | Prov_Start_Date | Prov_End_Date | Prov_Update_Date

Or to create the primary key based on the business rules/uniqueness of the data?

MemberID - PK1 | Prov_Nbr - PK2 | Prov_Start_Date - PK3 | Prov_End_Date | Prov_Update_Date

This is how the data would look in table, after processing on a weekly basis:

MemberID | Prov_Nbr | Prov_Start_Date | Prov_End_Date | Prov_Update_Date
------------------------------------------------------------------------
ABC123| IR456|2014-01-01|null|null - original record
ABC123| IR102|2014-04-01|null|null - new record turns original record `Prov_End_Date` to New `Prov_Start_Date - 1 day`

So table looks like this:

ABC123 | IR456 | 2014-01-01 | 2014-03-31 | null
ABC123 | IR102 | 2014-04-01 | null       | 2014-04-30

Still with me?

There are situations where based on the nature of the business a member could have a "retro" which essentially means this:

ABC123 | IR456 | 2014-01-01| 2014-03-31 | null
ABC123 | IR102 | 2014-04-01| null       | 2014-04-30

gets a new record 

ABC123 | IR402 | 2014-01-01 | null | null

essentially retro-fitting the original record with a new provider.

Would this case ruin the uniqueness of the data? or would SQL know how to handle this as a primary key update?

Any help with this would be much appreciated.

Upvotes: 1

Views: 127

Answers (1)

Tim Lehner
Tim Lehner

Reputation: 15261

I would actually put both of your solutions into place, as in create an identity field as your primary key (probably clustered) and add a unique key on MemberID, Prov_Nbr, Prov_Start_Date.

The top SQL Server bloggers are almost always extolling the virtues of an identity as PK, including situations somewhat similar to this where it is a surrogate, and you can then additionally enforce your business rule with the UK. Of course, I hope I'm reading your requirements correctly, especially the "retro" part.

Upvotes: 2

Related Questions