每当我设计数据库时,我总是想知道是否有一种最好的方法来命名数据库中的项目。我经常问自己以下问题:

表名应该是复数吗? 列名应该是单数吗? 我应该为表或列添加前缀吗? 我应该在命名项目时使用大小写吗?

是否有推荐的指导原则来命名数据库中的项?


当前回答

SELECT 
   UserID, FirstName, MiddleInitial, LastName
FROM Users
ORDER BY LastName

其他回答

请参阅ISO 11179-5:命名和识别原则 你可以在这里获得:http://metadata-standards.org/11179/#11179-5

我之前写过一篇博文:ISO-11179命名约定

我认为这些问题的最佳答案将由您和您的团队给出。有一个命名约定比命名约定的具体方式重要得多。

因为这个问题没有正确答案,你应该花点时间(但不要太多)选择你自己的习惯——这是重要的部分——坚持它。

当然,寻求一些关于标准的信息是很好的,这就是你要问的,但不要因为你可能得到的不同答案的数量而焦虑或担心:选择一个对你来说更好的答案。

以防万一,以下是我的答案:

是的。表是一组记录,老师或演员,所以…复数。 是的。 我不用它们。 我经常使用的数据库——Firebird——所有内容都是大写的,所以没关系。不管怎样,当我在编程时,我以一种更容易阅读的方式写名字,比如releaseYear。

好吧,既然我们有意见:

我认为表名应该是复数。表是实体的集合(表)。每一行表示一个实体,表表示集合。因此,我将Person实体表称为People(或Persons,随您喜欢)。

对于那些喜欢在查询中看到单一“实体名称”的人来说,这就是我使用表别名的原因:

SELECT person.Name
FROM People person

有点像LINQ的“from person in people select person. name”。

至于2、3和4,我同意@Lars的观点。

我的观点是:

1)不,表名应该是单数。

虽然对于简单的选择(select * from Orders)似乎有意义,但对于OO等效(Orders x = new Orders)则没有意义。

数据库中的表实际上是该实体的集合,当你使用set-logic时,它更有意义:

select Orders.*
from Orders inner join Products
    on Orders.Key = Products.Key

最后一行,连接的实际逻辑,看起来与复数表名混淆。

我不确定是否总是使用别名(如Matt建议的那样)可以消除这种情况。

2)它们应该是单数,因为它们只拥有一种属性

3)如果列名有歧义(如上所述,它们都有一个名为[Key]的列),表名(或其别名)永远不能很好地区分它们。您希望查询能够快速键入,并且简单-前缀会增加不必要的复杂性。

4)无论你想要什么,我都推荐CapitalCase

我不认为有任何一套绝对的指导方针。

只要你在应用程序或数据库中选择的是一致的,我不认为这真的很重要。

我们的偏好:

Should table names be plural? Never. The arguments for it being a collection make sense, but you never know what the table is going to contain (0,1 or many items). Plural rules make the naming unnecessarily complicated. 1 House, 2 houses, mouse vs mice, person vs people, and we haven't even looked at any other languages. Update person set property = 'value' acts on each person in the table. Select * from person where person.name = 'Greg' returns a collection/rowset of person rows. Should column names be singular? Usually, yes, except where you are breaking normalisation rules. Should I prefix tables or columns? Mostly a platform preference. We prefer to prefix columns with the table name. We don't prefix tables, but we do prefix views (v_) and stored_procedures (sp_ or f_ (function)). That helps people who want to try to upday v_person.age which is actually a calculated field in a view (which can't be UPDATEd anyway). It is also a great way to avoid keyword collision (delivery.from breaks, but delivery_from does not). It does make the code more verbose, but often aids in readability. bob = new person() bob.person_name = 'Bob' bob.person_dob = '1958-12-21' ... is very readable and explicit. This can get out of hand though: customer.customer_customer_type_id indicates a relationship between customer and the customer_type table, indicates the primary key on the customer_type table (customer_type_id) and if you ever see 'customer_customer_type_id' whilst debugging a query, you know instantly where it is from (customer table). or where you have a M-M relationship between customer_type and customer_category (only certain types are available to certain categories) customer_category_customer_type_id ... is a little (!) on the long side. Should I use any case in naming items? Yes - lower case :), with underscores. These are very readable and cross platform. Together with 3 above it also makes sense. Most of these are preferences though. - As long as you are consistent, it should be predictable for anyone that has to read it.