每当我设计数据库时,我总是想知道是否有一种最好的方法来命名数据库中的项目。我经常问自己以下问题:
表名应该是复数吗? 列名应该是单数吗? 我应该为表或列添加前缀吗? 我应该在命名项目时使用大小写吗?
是否有推荐的指导原则来命名数据库中的项?
每当我设计数据库时,我总是想知道是否有一种最好的方法来命名数据库中的项目。我经常问自己以下问题:
表名应该是复数吗? 列名应该是单数吗? 我应该为表或列添加前缀吗? 我应该在命名项目时使用大小写吗?
是否有推荐的指导原则来命名数据库中的项?
当前回答
这里的回答有点晚,但简而言之:
复数表名:我的偏好是复数 单个列名:是的 前缀表或列:
表:*通常*没有前缀是最好的。 列:没有。
在命名项时使用任何大小写:表和列都使用PascalCase。
细化:
(1)你必须做什么。很少有事情是你每次都必须以某种方式去做的,但还是有一些。
Name your primary keys using "[singularOfTableName]ID" format. That is, whether your table name is Customer or Customers, the primary key should be CustomerID. Further, foreign keys must be named consistently in different tables. It should be legal to beat up someone who does not do this. I would submit that while defined foreign key constraints are often important, consistent foreign key naming is always important You database must have internal conventions. Even though in later sections you'll see me being very flexible, within a database naming must be very consistent . Whether your table for customers is called Customers or Customer is less important than that you do it the same way throughout the same database. And you can flip a coin to determine how to use underscores, but then you must keep using them the same way. If you don't do this, you are a bad person who should have low self-esteem.
你可能应该做的事。
Fields representing the same kind of data on different tables should be named the same. Don't have Zip on one table and ZipCode on another. To separate words in your table or column names, use PascalCasing. Using camelCasing would not be intrinsically problematic, but that's not the convention and it would look funny. I'll address underscores in a moment. (You may not use ALLCAPS as in the olden days. OBNOXIOUSTABLE.ANNOYING_COLUMN was okay in DB2 20 years ago, but not now.) Don't artifically shorten or abbreviate words. It is better for a name to be long and clear than short and confusing. Ultra-short names is a holdover from darker, more savage times. Cus_AddRef. What on earth is that? Custodial Addressee Reference? Customer Additional Refund? Custom Address Referral?
(3)你应该考虑什么。
I really think you should have plural names for tables; some think singular. Read the arguments elsewhere. Column names should be singular however. Even if you use plural table names, tables that represent combinations of other tables might be in the singular. For example, if you have a Promotions and an Items table, a table representing an item being a part of a promotion could be Promotions_Items, but it could also legitimately be Promotion_Items I think (reflecting the one-to-many relationship). Use underscores consistently and for a particular purpose. Just general tables names should be clear enough with PascalCasing; you don't need underscores to separate words. Save underscores either (a) to indicate an associative table or (b) for prefixing, which I'll address in the next bullet. Prefixing is neither good or bad. It usually is not best. In your first db or two, I would not suggest using prefixes for general thematic grouping of tables. Tables end up not fitting your categories easily, and it can actually make it harder to find tables. With experience, you can plan and apply a prefixing scheme that does more good than harm. I worked in a db once where data tables began with tbl, config tables with ctbl, views with vew, proc's sp, and udf's fn, and a few others; it was meticulously, consistently applied so it worked out okay. The only time you NEED prefixes is when you have really separate solutions that for some reason reside in the same db; prefixing them can be very helpful in grouping the tables. Prefixing is also okay for special situations, like for temporary tables that you want to stand out. Very seldom (if ever) would you want to prefix columns.
其他回答
表名:它应该是单数,因为它是一个表示真实世界对象的单数实体,而不是表示对象的单数实体。
列名:它应该是单数,只有这样它才表示它将持有一个原子值,并将确认归一化理论。然而,如果有n个相同类型的属性,那么它们应该以1,2,…作为后缀。n,等等。
表/列前缀:这是一个巨大的主题,将在后面讨论。
外壳:应该是驼色的
我的朋友Patrick Karcher,我请求你不要写任何可能冒犯别人的东西,就像你写的那样,“此外,外键必须在不同的表中一致命名。如果有人不这样做,殴打他应该是合法的。”我从来没有犯过这样的错误,我的朋友帕特里克,但我写一般。如果他们一起打算为此揍你呢?:)
基本数据库命名约定(和样式)(点击这里查看更详细的描述)
表名 选择简短、明确的名字,不要超过一两个单词 容易区分表 方便唯一字段名的命名以及查找和链接表 给表取单数名,而不是复数名(更新:我仍然同意这种惯例的原因,但大多数人真的喜欢复数表名,所以我的立场软化了)…请点击上面的链接
我们的偏好:
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.
我知道这有点晚了,这个问题已经得到了很好的回答,但我想就#3关于列名前缀的问题提出我的看法。
所有列都应该使用一个对定义它们的表唯一的前缀命名。
例如,给定表“customer”和“address”,让我们分别使用前缀“cust”和“addr”。"customer"中会有"cust_id", "cust_name"等。“address”将包含“addr_id”,“addr_cust_id”(FK返回给客户),“addr_street”等。
当我第一次看到这个标准时,我坚决反对它;我讨厌这个主意。我无法忍受所有额外的输入和冗余。现在我已经有了足够的经验,我再也不会回去了。
这样做的结果是数据库模式中的所有列都是唯一的。这有一个主要的好处,它压倒了所有反对它的论点(当然,在我看来):
您可以搜索整个代码库,并可靠地找到涉及特定列的每一行代码。
The benefit from #1 is incredibly huge. I can deprecate a column and know exactly what files need to be updated before the column can safely be removed from the schema. I can change the meaning of a column and know exactly what code needs to be refactored. Or I can simply tell if data from a column is even being used in a particular portion of the system. I can't count the number of times this has turned a potentially huge project into a simple one, nor the amount of hours we've saved in development work.
另一个相对较小的好处是,当你进行自连接时,你只需要使用表别名:
SELECT cust_id, cust_name, addr_street, addr_city, addr_state
FROM customer
INNER JOIN address ON addr_cust_id = cust_id
WHERE cust_name LIKE 'J%';
这里的回答有点晚,但简而言之:
复数表名:我的偏好是复数 单个列名:是的 前缀表或列:
表:*通常*没有前缀是最好的。 列:没有。
在命名项时使用任何大小写:表和列都使用PascalCase。
细化:
(1)你必须做什么。很少有事情是你每次都必须以某种方式去做的,但还是有一些。
Name your primary keys using "[singularOfTableName]ID" format. That is, whether your table name is Customer or Customers, the primary key should be CustomerID. Further, foreign keys must be named consistently in different tables. It should be legal to beat up someone who does not do this. I would submit that while defined foreign key constraints are often important, consistent foreign key naming is always important You database must have internal conventions. Even though in later sections you'll see me being very flexible, within a database naming must be very consistent . Whether your table for customers is called Customers or Customer is less important than that you do it the same way throughout the same database. And you can flip a coin to determine how to use underscores, but then you must keep using them the same way. If you don't do this, you are a bad person who should have low self-esteem.
你可能应该做的事。
Fields representing the same kind of data on different tables should be named the same. Don't have Zip on one table and ZipCode on another. To separate words in your table or column names, use PascalCasing. Using camelCasing would not be intrinsically problematic, but that's not the convention and it would look funny. I'll address underscores in a moment. (You may not use ALLCAPS as in the olden days. OBNOXIOUSTABLE.ANNOYING_COLUMN was okay in DB2 20 years ago, but not now.) Don't artifically shorten or abbreviate words. It is better for a name to be long and clear than short and confusing. Ultra-short names is a holdover from darker, more savage times. Cus_AddRef. What on earth is that? Custodial Addressee Reference? Customer Additional Refund? Custom Address Referral?
(3)你应该考虑什么。
I really think you should have plural names for tables; some think singular. Read the arguments elsewhere. Column names should be singular however. Even if you use plural table names, tables that represent combinations of other tables might be in the singular. For example, if you have a Promotions and an Items table, a table representing an item being a part of a promotion could be Promotions_Items, but it could also legitimately be Promotion_Items I think (reflecting the one-to-many relationship). Use underscores consistently and for a particular purpose. Just general tables names should be clear enough with PascalCasing; you don't need underscores to separate words. Save underscores either (a) to indicate an associative table or (b) for prefixing, which I'll address in the next bullet. Prefixing is neither good or bad. It usually is not best. In your first db or two, I would not suggest using prefixes for general thematic grouping of tables. Tables end up not fitting your categories easily, and it can actually make it harder to find tables. With experience, you can plan and apply a prefixing scheme that does more good than harm. I worked in a db once where data tables began with tbl, config tables with ctbl, views with vew, proc's sp, and udf's fn, and a few others; it was meticulously, consistently applied so it worked out okay. The only time you NEED prefixes is when you have really separate solutions that for some reason reside in the same db; prefixing them can be very helpful in grouping the tables. Prefixing is also okay for special situations, like for temporary tables that you want to stand out. Very seldom (if ever) would you want to prefix columns.