在设计表时，我养成了一个习惯，即有一个唯一的列，并将其作为主键。这可以通过三种方式实现，具体取决于需求:
自动递增的标识整数列。
唯一标识符(GUID)
短字符(x)或整数(或其他相对较小的数字类型)列，可作为行标识符列
数字3将用于相当小的查找，主要是读取表，这些表可能有一个唯一的静态长度字符串代码，或一个数值，如年或其他数字。
在大多数情况下，所有其他表都有一个自动递增的整数或唯一标识符主键。
问题:-)
我最近开始使用一些数据库，这些数据库没有一致的行标识符，而且主键目前聚集在各个列之间。一些例子:
datetime /字符
datetime /整数
datetime / varchar
字符/ nvarchar / nvarchar
这有有效的理由吗?我总是为这些情况定义一个标识符或唯一标识符列。
此外，还有许多根本没有主键的表。如果有的话，合理的理由是什么?
我试图理解为什么桌子被设计成这样，对我来说，它似乎是一个很大的混乱，但也许有很好的理由。
第三个问题在某种程度上帮助我解析答案:在使用多个列组成复合主键的情况下，与代理/人工键相比，这种方法是否有特定的优势?我主要考虑的是性能、维护、管理等方面。
主键有什么特别之处?
模式中表的用途是什么?表中键的作用是什么?主键有什么特别之处?围绕主键的讨论似乎忽略了一点，即主键是表的一部分，而表是模式的一部分。对表和表关系最有利的应该驱动所使用的键。
Tables (and table relationships) contain facts about information you wish to record. These facts should be self-contained, meaningful, easily understood, and non-contradictory. From a design perspective, other tables added or removed from a schema should not impact on the table in question. There must be a purpose for storing the data related only to the information itself. Understanding what is stored in a table should not require undergoing a scientific research project. No fact stored for the same purpose should be stored more than once. Keys are a whole or part of the information being recorded which is unique, and the primary key is the specially designated key that is to be the primary access point to the table (i.e. it should be chosen for data consistency and usage, not just insert performance).
ASIDE: The unfortunately side effect of most databases being designed
and developed by application programmers (which I am sometimes) is
that what is best for the application or application framework often
drives the primary key choice for tables. This leads to integer and
GUID keys (as these are simple to use for application frameworks) and
monolithic table designs (as these reduce the number of application
framework objects needed to represent the data in memory). These
application driven database design decisions lead to significant data
consistency problems when used at scale. Application frameworks
designed in this manner naturally lead to table at a time designs.
“Partial records” are created in tables and data filled in over time.
Multi-table interaction is avoided or when used causes inconsistent
data when the application functions improperly. These designs lead
to data that is meaningless (or difficult to understand), data spread
over tables (you have to look at other tables to make sense of the
current table), and duplicated data.
It was said that primary keys should be as small as necessary. I would says that keys should be only as large as necessary. Randomly adding meaningless fields to a table should be avoided. It is even worse to make a key out of a randomly added meaningless field, especially when it destroys the join dependency from another table to the non-primary key. This is only reasonable if there are no good candidate keys in the table, but this occurrence is surely a sign of a poor schema design if used for all tables.
It was also said that primary keys should never change as updating a primary key should always be out of the question. But update is the same as delete followed by insert. By this logic, you should never delete a record from a table with one key and then add another record with a second key. Adding the surrogate primary key does not remove the fact that the other key in the table exists. Updating a non-primary key of a table can destroy the meaning of the data if other tables have a dependency on that meaning through a surrogate key (e.g. a status table with a surrogate key having the status description changed from ‘Processed’ to ‘Cancelled’ would definitely corrupt the data). What should always be out of the question is destroying data meaning.
Having said this, I am grateful for the many poorly designed databases that exist in businesses today (meaningless-surrogate-keyed-data-corrupted-1NF behemoths), because that means there is an endless amount of work for people that understand proper database design. But on the sad side, it does sometimes make me feel like Sisyphus, but I bet he had one heck of a 401k (before the crash). Stay away from blogs and websites for important database design questions. If you are designing databases, look up CJ Date. You can also reference Celko for SQL Server, but only if you hold your nose first. On the Oracle side, reference Tom Kyte.
以下是我拥有25年以上开发经验后得出的经验法则。
所有表都应该有一个单列主键auto
增量。
将它包含在任何意味着可更新的视图中
主键在应用程序上下文中不应该有任何意义。这意味着它不应该是SKU、帐号、员工id或对应用程序有意义的任何其他信息。它只是一个与实体相关联的唯一键。
主键由数据库用于优化目的，应用程序除了用于标识特定实体或与特定实体相关外，不应该使用主键。
始终使用单一值主键使得执行upsert非常简单。
Favor multiple indices on single columns over multi-column indices.
For example, if you have a two column key, favor creating an index on each column over creating a two column index. If we create a multi-column key on firstname + lastname, we can't do indexed lookups on lastname without providing a firstname as well. Having indices on both columns allows the optimizer to perform indexed lookups on either or both columns regardless of how they are expressed in your WHERE clause.
If your tables are massive, explore partitioning the table into segments based on the most prominent search criteria.
If you have a table that has a significant number of Id fields in it, consider removing all except the primary key to a single table which has an id (PK), an org_id (FK to original table) and an id_type column. Create indices for all columns on the new table and relate it to the original table. In this manner, you can now perform indexed lookups of any number of ids using only a single index.
