每当我设计数据库时,我总是想知道是否有一种最好的方法来命名数据库中的项目。我经常问自己以下问题:
表名应该是复数吗? 列名应该是单数吗? 我应该为表或列添加前缀吗? 我应该在命名项目时使用大小写吗?
是否有推荐的指导原则来命名数据库中的项?
每当我设计数据库时,我总是想知道是否有一种最好的方法来命名数据库中的项目。我经常问自己以下问题:
表名应该是复数吗? 列名应该是单数吗? 我应该为表或列添加前缀吗? 我应该在命名项目时使用大小写吗?
是否有推荐的指导原则来命名数据库中的项?
当前回答
表名为单数。假设您正在建模某人与其地址之间的关系。 例如,如果您正在读取一个数据模型,您会选择 每个人可以住在0、1或多个地址。”或 每个人可以住在0、1或多个地址。 我认为用复数的称呼比把人写成person更容易。加上集合名词通常与单数名词不同。
其他回答
我们的偏好:
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.
这里有一个链接,提供了一些选择。我正在寻找一个简单的规范,我可以遵循,而不是依赖于一个部分定义的规范。
http://justinsomnia.org/writings/naming_conventions.html
我知道这有点晚了,这个问题已经得到了很好的回答,但我想就#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%';
我建议你看看微软的SQL Server样本数据库: https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks
AdventureWorks示例使用了非常清晰和一致的命名约定,它使用模式名组织数据库对象。
表的单数名称 列的单数名称 表前缀的架构名称(例如:SchemeName.TableName) 帕斯卡壳(又称上驼峰壳)
请参阅ISO 11179-5:命名和识别原则 你可以在这里获得:http://metadata-standards.org/11179/#11179-5
我之前写过一篇博文:ISO-11179命名约定