1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hituptony
  • 2,740
  • 3
  • 22
  • 44

1 Answers1

2

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.

Community
  • 1
  • 1
Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
  • yea no doubt, you're reading correctly. And I was speaking with some Data Architects that said using the unique ID from the data trumps the IDENTITY or AI, but that AI was still somewhat of a valid option. Good answer +1 Let's see what others say! – Hituptony May 28 '14 at 16:17
  • 2
    @Hituptony That's an ecumenical matter. Some believe in natural keys, some believe in surrogate keys. I fall into the latter belief group. – podiluska May 28 '14 at 16:24
  • 1
    This is usually the best of both worlds. You get the unique index you need on the natural key, but you don't have to duplicate the data to any related tables and can use the surrogate key for joins and such where the smaller lighter suggogate key will give faster performance and you never have to update a million child records because the natural key changed. In this case, I am not sure if you will ever need any child tables, so you could use the natural key. But I still have a strong preference for this solution. Mostly because when I think I will never need a child table... – HLGEM May 28 '14 at 20:30
  • 1
    Thanks to podiluska and HLGEM for adding some real substance to my brief answer. – Tim Lehner May 28 '14 at 20:57
  • Awesome I will ask my DBA to assign the primary key to those fields then, it was set to IDENTITY but I couldn't live with that. Thanks for your responses guys. – Hituptony May 29 '14 at 13:56