利与弊日期缺点列作为主键的一部分 [英] Pros & Cons of Date Column as Part of Primary Key

问题描述

我目前正在使用数据库，在该数据库中需要日志来跟踪大量不同的数据更改。诸如价格变化，项目状态变化之类的东西。要实现此目的，我制作了不同的日志表来存储需要保留的数据。

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.

到举一个可靠的例子，为了跟踪需要订购的零件的变化价格，我创建了一个名为 Part_Price_Log 的表。主键是由零件价格被修改的日期组成的复合键，以及零件表中零件的唯一ID的外键。

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.

我的逻辑是，如果您需要查找零件的当前价格，则只需查找该零件ID的最新条目。但是，有人告诉我不要以这种方式实现它，因为使用Date作为主键的一部分是获取数据错误的简便方法。

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.

所以我的问题是

将Date列用作复合主键的优点/缺点是什么？有什么更好的替代方法？

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

推荐答案

通常，我认为最好的主键是合成自动增量键。这些具有某些优点：

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

• 键值记录插入顺序。


• 键是固定的长度（通常为4个字节）。


• 对于外键引用，单键要简单得多。


• 在数据库（默认情况下为SQL Server）中，


• 它们相对容易键入和比较（我的眼睛在比较UUID时效果不佳）。

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

这篇关于利与弊日期缺点列作为主键的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！