4  

As others have pointed out, there are two types of keys for records: natural keys and surrogate (artificial) keys. The two major questions, then, are: do you need to use a surrogate key, and if so, what should that surrogate key be?

正如其他人所指出的,记录有两种类型的键:自然键和代理(人工)键。那么,两个主要问题是:您是否需要使用代理密钥,如果是,那该代理密钥应该是什么?

As to the first question: You only need to use a surrogate key if you have no valid natural key for use as a primary key on the table. All sane database systems support the 'ON UPDATE CASCADE' clause, which means that if you are using a natural key which happens to change, the change will be propagated to everything which is declared to reference it. Of course, if your database system does not support foreign keys, then your best bet is to use a surrogate key, if only to work around the lack of functionality in the database system (and surrogate keys will make your database easier to consistency check in light of that fact). That said, if you are designing an application that has requirements for high uptime and high robustness, select a database implementation that gets foreign keys correct, or you will most likely find that data integrity bugs will be found late in development (or even in maintenance) and you will have to write utilities that will check your data for consistency in various modes of failure.

关于第一个问题:如果您没有有效的自然键用作表中的主键,则只需使用代理键。所有理智的数据库系统都支持'ON UPDATE CASCADE'子句,这意味着如果您正在使用恰好更改的自然键,则更改将传播到声明为引用它的所有内容。当然,如果您的数据库系统不支持外键,那么最好的办法是使用代理键,如果只是为了解决数据库系统中缺少的功能(而代理键会使您的数据库更容易一致性检入这个事实的亮点)。也就是说,如果您正在设计一个具有高正常运行时间和高稳健性要求的应用程序,请选择能够正确识别外键的数据库实现,否则您很可能会发现数据完整性错误将在开发后期(甚至在维护中发现)中找到并且您必须编写实用程序,以检查您的数据在各种故障模式下的一致性。

For the second question: If you use a surrogate key, especially if you are working around a deficiency of a database system, you should always treat it as if it were immutable and globally unique. ALWAYS. This will aid in many situations later on: companies can merge (and split), databases can be merged (and split), and about a million other situations can happen that aren't anticipated when the database is designed that are capable of causing problems if the surrogate keys are not globally unique. Since surrogate keys are not at all related to the data they hold (they have no relation to the other fields in the table other than the artificial one that you have bestowed upon it) it's just best that way. For these reasons, when I must use a surrogate key, I use a UUID (which is essentially a 128-bit integer, but not incremental). Now you don't have to worry about renumbering record numbers and references when unexpected events occur. (Yes, it does slow things down, particularly if your server is running on a 32-bit platform. But if you need to handle more load, distribute the load better---do not sacrifice integrity for speed, ever, when you're working with important data!)

对于第二个问题:如果使用代理键,特别是如果您正在解决数据库系统的缺陷,则应始终将其视为不可变且全局唯一。总是。这将在以后的许多情况下提供帮助:公司可以合并(和拆分),数据库可以合并(和拆分),并且当数据库被设计为能够导致问题时,可能会发生大约一百万个其他情况如果代理键不是全局唯一的。由于代理键与它们所持有的数据完全无关(它们与表中的其他字段没有任何关系,除了你赋予它的人工字段),这是最好的方式。由于这些原因,当我必须使用代理键时,我使用UUID(基本上是128位整数,但不是增量式)。现在,您不必担心在发生意外事件时重新编号记录编号和引用。 (是的,它确实减慢了速度,特别是如果你的服务器在32位平台上运行。但是如果你需要处理更多的负载,更好地分配负载---不要因为速度而牺牲完整性,当你'重新处理重要数据!)

8  

The id field is an example of a surrogate key. It is a good idea to use a surrogate key as a primary key in a database because it is totally unrelated to and therefore unaffected by external events in the real world.

id字段是代理键的示例。使用代理键作为数据库中的主键是一个好主意,因为它与现实世界中的外部事件完全无关,因此不受其影响。

Using a natural key such as the email address could cause problems because if a user changes their email address your key will have to change. This can create difficulties as it will break foreign key contraints. It will also make querying for events relating to a specific user over time more difficult as you have no guaranteed single key that is consistent for that user's entire history.

使用诸如电子邮件地址之类的自然键可能会导致问题,因为如果用户更改其电子邮件地址,您的密钥将不得不更改。这会造成困难,因为它会打破外键的限制。它还将使查询与特定用户相关的事件随着时间的推移变得更加困难,因为您没有保证与该用户的整个历史记录一致的单个密钥。

If you have more than one database in your company that needs the keys, or you export data from your database to other applications or systems then when you change a key in your database you may also need to change the keys in those systems too, something which cannot be done automatically by using ON CASCADE UPDATE.

如果公司中有多个需要密钥的数据库,或者将数据从数据库导出到其他应用程序或系统,那么当您更改数据库中的密钥时,您可能还需要更改这些系统中的密钥,使用ON CASCADE UPDATE无法自动完成。

3  

Relations between tables.

表之间的关系。

Is uneffective have relation to username or email address because this is a string and comparing this values takes much more time, and indexes are bigger, optimal solution is add ID like a primary key for relations to other tables as userid.

无效与用户名或电子邮件地址有关,因为这是一个字符串,并且比较这些值需要更多的时间,并且索引更大,最佳解决方案是添加ID作为与其他表关系的主键作为userid。

0  

Although you want usernames to be unique you should not rely on your database to control uniqueness. It is best practice for your code to test if the username and e-mail already exists in the database.

虽然您希望用户名是唯一的,但您不应该依赖数据库来控制唯一性。最佳实践是您的代码测试数据库中是否已存在用户名和电子邮件。

The purpose of length is for limiting the input of data. For instance varchar with a length of 10 will only allow a 10 character length input. Value is for default purposes. If you insert a new row without declaring this field it will automatically be filled with the value, if set.

长度的目的是限制数据的输入。例如,长度为10的varchar只允许10个字符长度的输入。值用于默认目的。如果在未声明此字段的情况下插入新行,则会自动填充该值(如果已设置)。

0  

The point is to lighten the index. If you don't use an identity field and you choose to use (username, email) as primary key, the search for a user will take some more time looking for the username firt and then the email. Furthermore, these fields are strings, you can't compare the size of an integer and the size of strings like usernames and emails.

重点是减轻指数。如果您不使用身份字段并选择使用(用户名,电子邮件)作为主键,搜索用户将花费更多时间查找用户名firt,然后查找电子邮件。此外,这些字段是字符串,您无法比较整数的大小和字符串的大小,如用户名和电子邮件。

And using an identity field will allow you to do things like Comments (id, user_id) and not Comments (id, username, email)...

使用标识字段将允许您执行注释(id,user_id)而不是注释(id,用户名,电子邮件)...