Pros & Cons of Date Column as Part of Primary Key

Question

I am currently working on a database, where a log is required to track a bunch of different changes of data. Stuff like price changes, project status changes, etc. To accomplish this I've made different 'log' tables that will be storing the data needing to be kept.

To give a solid example, in order to track the changing prices for parts which need to be ordered, I've created a Table called Part_Price_Log. The primary key is composite made up of the date in which the part price is being modified, and a foreign key to the Part's unique ID on the Parts Table.

My logic here, is that if you need to look up the current price for a part, you just need to find the most recent entry for that Part ID. However, I am being told not to implement it this way because using Date as part of a primary key is an easy way to get errors in your data.

So my question is thus.

What are the pros/cons of using a Date column as part of a composite primary key? What are some better alternatives?

Answer 1

In general, I think the best primary keys are synthetic auto-incremented keys. These have certain advantages:

• The key value records the insertion order.
• The keys are fixed length (typically 4 bytes).
• Single keys are much simpler for foreign key references.
• In databases (such as SQL Server by default) that cluster the data based on the primary key, inserts go "at the end".
• They are relatively easy to type and compare (my eyes just don't work well for comparing UUIDs).

The fourth of these is a really big concern in a database that has lots of inserts, as suggested by your data.

There is nothing a priori wrong with composite primary keys. They are sometimes useful. But that is not a direction I would go in.

Answer 2

I agree that it is better to keep the identity column/uniqueidentifier as primary key in this scenario, Also if you make partid and date as composite primary key, it is going to fail in a case when two concurrent users try to update the part price at same time.The primary key is going to fail in that case.So the better approach will be to have an identity column as primary key and keep on dumping the changes in log table.In case you hit some performance barriers later on you can partition your table year wise and can overcome that performance challenge.