微软数据库命名规范11.docx

1、微软数据库命名规范11Database Naming Conventions Version 1.1数据库命名协议 版本1.1野人翻译2008年3月16日Last Revised May 13, 2004 by Jason MaussJason Mauss2004年5月13日修订版The main goal of adopting a naming convention for database objects is so that you and others can easily identify the type and purpose of all objects contained

2、in the database. 为数据库对象编写命名协议的目的，就是能够轻松的定义数据库中全部对象。The information presented here serves as a guide for you to follow when naming your database objects. 这里提到的内容可以作为数据库对象命名的指导。When reading these rules and guidelines remember thatconsistentnamingcan bethe most important rule to follow. 当阅读此文涉及的规范和原则时，

3、请牢记始终规范的命名方法是数据库设计的最重要原则。Keep in mind that followingthe guidelines as outlined in this documentcan stillproduce long and crypticnames,ultimately, your unique situation will dictate the reasonability of your naming convention.请注意根据此协议的原则做出的数据库对象的命名相对较长并具有相关的意义，具体命名需要依照你个人及开发环境的情况。The goal of this par

4、ticular naming convention is to produce practical, legible, concise, unambiguous and consistent names for your database objects.此命名协议的制定目的，是提供较为形象、易读、简单、明确和原则性一致的数据库命名方法。While most databases contain more types of objects than those discussed here (User Defined Types, Functions, Queries, etc.), the 7

5、 types of objects mentioned here are common among all major database systems. Think of this as a generic DBMS-neutral guide for naming your objects. 当然，数据库涉及到的对象比这里讨论的更多（例如用户自定义的、函数、查询等），这里提到的7种对象涵盖了大多数数据库系统的内容。所以请将此协议作为“中性数据库”的命名指导。The following types of database objects are discussed here:这里讨论下列数据

6、库对象：1.Tables 表 2.Columns (incl. Primary, Foreign and Composite Keys) 列（包括主键、外键和复合键） 3.Indexes 索引 4.Constraints 参数 5.Views 视图 6.Stored Procedures 存储过程 7.Triggers 触发器ALL DATABASE OBJECTS全部数据库对象命名的统一规则Limit the name to 30 characters (shorter is better) 限制名称在30个字母以内（越短越好） Use only letters or underscores

7、 (try to avoid numbers) 仅仅使用字母和下划线（尽量避免数字命名） Try to use underscore characters as little as possible. 尽量减少使用下划线PascalCase notation achieves the same word separation without them. PascalCase标记法可以做到在不使用下划线的情况下分割单词 Use a letter as the first character of the name. (dont start names with underscores) 不要以下

8、划线作为名称的字母 Avoid abbreviations (can lead to misinterpretation of names) 避免使用所写（可能引起误解） Avoid acronyms (some acronyms have more than one meaning eg. ASP) 避免使用缩写（因为一些缩写例如ASP，有特定意义和用途） Makes the name readable (they shouldnt sound funny when read aloud) 确保名称可朗读 Avoid using spaces in names even if the sys

9、tem allows it. 无论是否系统允许都避免使用空格11. TABLES表When naming your database tables, give consideration to other steps in the development process. 当为你的表命名时，请同时考虑到后续开发的各个环节。Keep in mind you will most likely have toutilize the names you give your tables several times as part of other objects, for example, proce

10、dures, triggers or views may all contain references to the table name. 请记住你很有可能在后续开发其他对象，如存储过程、触发器或者视图时多次用到你所命名的表的名称，并作为相关参数使用。You want to keep the name as simple and short as possible. 所以尽量使用简单短小的单词。Some systems enforce character limits on object names also. 甚至一些系统强制限制对象名称的单词长度。For example, in Orac

11、le you are limited to about 30 characters per object.例如Oracle限制名称长度为30字母以内。Rule 1a (Plural Names复数原则) - Table names should be plural, for example, Customers instead of Customer. 表的名称应该为复数。Thisrule is applicablebecause tables are logical collections of one or more entities as records - just like coll

12、ection classes are logical collections of one or more objects. 因为表包含了很多数量的实体记录。If you were to first draw an abstract data model like a NIAM/ORM model, you might have singular entity names like Customer or User but, they should be changed tothe plural formwhen building the actual tables.尽管当你在建立数据库NIA

13、M/ORM模型时，可能使用单数形式表示实例，但是当建立具象事物表时，要使用复数名称。For table names with multiple words, only the last word should be plural, for example, UserRoles and UserRoleSettings.当由词组命名时，将最后一个单词变为复数。译者按：复数命名法一般情况下不可取，在长度，表述方面不佳，作者本身也承认使用复数命名的情况较少Rule 1b (Prefixes前缀) - Used correctly, table prefixes can help you organi

14、ze your tables into related groups or distinguish them from other unrelated tables. 正确的使用前缀能够帮助你识别表所处的分组或者和其他的表容易区分。Used poorly, they can cause you to have to type a lot of unnecessary characters. 使用前缀是避免使用复杂修饰、限制性词汇辅助命名的好方法。Well discuss what not to do first. 首先我们讨论被禁止的做法。Do not give your table name

15、s prefixes like tbl or TBL_ as these are just redundant and unnecessary. 不要使用tbl”或者TBL，因为这种方法多余且不必须。It will be obvious which names are the table names in SQL statements because they will always be proceeded by the FROM clause of the SELECT statement. 因为在选择查询语句中表名紧跟在FROM的后面。Not all prefixes are bad.不

16、是所有的前缀都不可取。 In some cases, your tables might be sharing a schema/database with other tables that are not related in any way. 在某些情况下，表可能被一些数据库共享引用，但是此表与其它的表没有任何关系。In this case, it is sometimes a good idea to prefix your table names with some characters that group your tables together. 这时，前缀可以将同类的表分组。

17、For example, for a healthcare application you might give your tables an Hc prefix so that all of the tables for that application would appear in alphabetized lists together.例如，对于医疗保健方面的应用程序，可以用Hc开头，好处是能够系统能够将相同的HC开头的表排列在一起，做到分组查看。 Note that even for the prefix, use Pascal Case. 注意前缀也是PascalCase命名法。T

18、his is discussed in Rule 1c. Do not use underscores in your prefixes, which is discussed in more depth in Rule 1d. 同样根据Rule 1c原则，不要使用下划线。The last kind of prefix that is acceptable is one that allows you to group logical units of tables. 最后一种能够使用前缀的条件是分组逻辑表单元。A plausible example could entail a large

19、application (30 to 40+ tables) that handled both Payroll and Benefits data. 例如涉及到30到40个以上表的应用程序，其中包括例如支出和盈利2大部分数据。You could prefix the tables dealing with payroll with a Pay or Prl prefix and give the tables dealing with benefits data a Ben or Bfts prefix. 这时可以以Pay或者Prl作为支出类表的前缀，Ben或者Bfts作为盈利类表的前缀。T

20、he goal of both this prefix and the aforementioned shared schema/database prefix is to allow you to group specific tables together alphabetically in lists and distinguish them from unrelated tables. 当然这些前缀的使用目的都是要使系统能够自动分组同类表的位置。Lastly, the shared schema/database prefix is a higher grouping level an

21、d comes first in the name, for example, HcPayClients not PayHcClients.最后，当使用符合前缀时，需要将表示大类的前缀排在前，小类前缀续后，如“医疗保健支出联系人”而不是“支出医疗保健联系人”。Rule 1c (Notation表示法) - For all parts of the table name, including prefixes, use Pascal Case. 所有的表名，包括前缀，都使用PascalCase拼写法。Using this notation will distinguish your table

22、names from SQL keywords (all capital letters). 这种方法能够使表名和SQL语句全部大写的关键字区分开。For example, SELECT CustomerId_Pk, CustomerName FROM MyAppGroupTable WHERE CustomerName = %S shows the notation for the table name distinguishing it from the SQL keywords used in the query. 例如此语句的区分比较明晰。PascalCase also reduces

23、 the need for underscores to visually separate words in names.同时这种表示法减少了下划线的使用。Rule 1d (Special Characters特殊字) - For table names, underscores should not be used.表名不能在下划线。 The underscore character has a place in otherobject names but, not for tables. 下划线有其它的用法，但是不能在表命名里出现。Using Pascal Case for your t

24、able name allows for the upper-case letter to denote the first letter of a new word or name. 表命名应该以PascalCase作为命名方法，即首字母大写表示下一个单词的起始。Thus there is no need to do so with an underscore character. 所以就没有必要再用下划线表示单词起始了。Do not use numbers in your table names either. 同时不能用数字作为表名。This usually points to a po

25、orly designed data model or irregularly partitioned tables. 数字表名一般特指失败的数据设计模型或者非常规表。Do not use spaces in your table names either. 同时不能使用空格作为表名。While most database systems can handle names that include spaces, some systems require you to add more characters around the name when referencing it (like t

26、able name for example) which goes against the rule of keeping things as short and simple as possible.当然大部分的数据库系统允许表名空格的存在，但是至少空格会增加名称长度，不符合简短命名原则。 If you are developing in a non-english language, do not use any of that languages special characters.如果使用非英语命名，不要使用非英语字符。Rule 1e (Abbreviations缩写) - Avoi

27、d using abbreviations if possible.尽量避免缩写 Use Accounts instead of Accts and Hours instead of Hrs. 使用全拼不用缩写单词。Not everyone will always agree with you on what your abbrevations stand for - and - this makes it simple to read and understand for both developers and non-developers. 对于同开发小组其他成员和用户来讲，缩写并非容易读

28、懂和认同。This rule can be relaxed for junction table names (See Rule 1f). 此原则同样在Rule1f原则出现，涉及到交叉表名。Do not use acronyms.同时避免任何形式的缩写前缀单词。Rule 1f (Junction a.k.a Intersection Tables交叉表) - Junction tables, which handle many to many relationships, should be named by concatenating the names of the tables that

29、 have a one to many relationship with the junction table. 交叉表应当以其表述的连锁意义来命名。For example, you might have Doctors and Patients tables. 例如有“医生”和“病人”2个表。Since doctors can have many patients and patients can have many doctors (specialists) you need a table to hold the data for those relationships in a ju

30、nction table. This table should be namedDoctorsPatients. 因为医生可能有很多病人，同时每个病人有不同科目的医生，所以交叉表名应该为“医生病人”。Since this convention can result in lengthy table names, abbreviations sometimes may be used at your discretion.考虑到交叉表名的长度，可以谨慎考虑缩写。2. COLUMNS列 - (incl. PRIMARY, FOREIGN, AND COMPOSITE KEYS包括主键，外键和复合键

31、)When naming your columns, keep in mind that they are members of the table, so they do not need the any mention of the table name in the name. 列是表的组成部分，所以不要在其中涉及表名。The primary key field is typically the only exception to this rule where including the table name is justified so that you can have a mo

32、re descriptive field name than just Id. 主键是唯一可以包括表名的，具备表的描述性。CustomerIdis acceptable but not required.主键包括表名单词并非必要。Just like with naming tables, avoid using abbreviations, acronyms or special characters. 就像表名需要避免缩写，全前缀缩写或者特殊字符。All column names should use Pascal Case to distinguish them from SQL keywords (all upper case). 所以列明要求使用PascaCase表示法区分SQL语句的全大写表示法。Rule 2a (Identity Primary Key Fields主键标识列) - For fields that are the primary key for a table and uniquely identify each record in the table, the name should simpl