SQL 命名数据库表和视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1587525/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Naming Database Tables and Views
提问by macleojw
I recently asked a colleague why they had included _TABLE at the end of all their database table names. They said it had been a standard at another orgainisation they had worked for. Other colleagues use V_ at the start of views.
Is this good practice?
我最近问过一位同事，为什么他们在所有数据库表名的末尾都包含了 _TABLE。他们说这是他们工作过的另一个组织的标准。其他同事在视图开始时使用 V_。
这是好的做法吗？
回答by akf
Consistency is the best approach. Adding a _TABLE or _VIEW at the end of an object name is overkill in my book, but if the database is designed that way, I wouldn't break from convention.
一致性是最好的方法。在我的书中，在对象名称的末尾添加 _TABLE 或 _VIEW 是矫枉过正的，但如果数据库是这样设计的，我就不会违反惯例。
For your colleague to bring his naming convention from a previous organization into a new one without checking 'local' standards is bad practice.
对于您的同事来说，在不检查“本地”标准的情况下，将他以前组织的命名约定带入新组织是不好的做法。
回答by HLGEM
Using v for view as a standard is particularly bad in my eyes because it prevents you from using one of the best ways of refactoring a database which is to rename the table and create a view with the old name that mimics the old structure so nothing breaks while you are making the change but you can start finding and fixing all the old references without having to fix all of them before the change is put to prod.
在我看来，将 v 用作视图标准特别糟糕，因为它会阻止您使用重构数据库的最佳方法之一，即重命名表并使用模拟旧结构的旧名称创建视图，因此没有任何破坏在进行更改时，您可以开始查找和修复所有旧引用，而无需在更改生效之前修复所有旧引用。
I'm also with akf on the idea that the real problem is taking naming conventions from some other organization and ignoring the naming conventions of the current organization. I'd stomp on this fast and insist that he change all the objects and associated code to whatever your standard is or this will continue to be a problem.
我也同意 akf 的想法，即真正的问题是采用其他组织的命名约定并忽略当前组织的命名约定。我会如此快速地踩踏并坚持要求他将所有对象和相关代码更改为您的标准，否则这将继续成为问题。
回答by Andomar
Using the v_ or vw_ prefix can be useful if you read SELECT queries often; you can see quickly whether you are selecting from a view or table. Prefixing views OR postfixing tables should be enough, no need for both. We use view prefixing.
如果您经常阅读 SELECT 查询，使用 v_ 或 vw_ 前缀会很有用；您可以快速查看您是从视图还是表中进行选择。前缀视图或后缀表应该就足够了，不需要两者兼而有之。我们使用视图前缀。
Additionally we use a "module" prefix to cluster tables and views around a functional group. For example, billing related tables are called BIL_* and billing related views VW_BIL_*. The module naming keeps related tables and views near each other in SSMS.
此外，我们使用“模块”前缀来围绕功能组对表和视图进行聚类。例如，计费相关表称为 BIL_*，计费相关视图称为 VW_BIL_*。模块命名使 SSMS 中的相关表和视图彼此靠近。
回答by Jerome
It think akf answered the question well. And HLGEM makes a good point about refactoring.
它认为akf很好地回答了这个问题。HLGEM 对重构提出了很好的观点。
However I would add this counterargument to having no prefix/suffix convention. In SQL Server (and probably other databases) you cannot have a table and a view with the same name in the same schema with the same owner. It is a common pattern to create a denormalized view for a table. If you haven't adopted a naming convention that distinguishes views from tables then you might end up with funny names for these views such as EMPLOYEE_DENORM instead of EMPLOYEE_V.
但是，我会将此反驳添加到没有前缀/后缀约定。在 SQL Server（可能还有其他数据库）中，您不能在同一个架构中拥有同一个所有者的同名表和视图。为表创建非规范化视图是一种常见模式。如果您没有采用区分视图和表的命名约定，那么您最终可能会为这些视图使用有趣的名称，例如 EMPLOYEE_DENORM 而不是 EMPLOYEE_V。
If the need arises for a refactoring such as HLGEM describes then your naming convention could allow for that. That way those views without the prefix or suffix are easily identified as "refactoring" views.
如果需要进行像 HLGEM 描述的重构，那么您的命名约定可以允许。这样那些没有前缀或后缀的视图很容易被识别为“重构”视图。
回答by Ryan Mann
I prefer to name my tables in all camelCase, as well as field names. For example...
我更喜欢用所有驼峰命名我的表，以及字段名称。例如...
CREATE TABLE [crm].[company]
(
[id] INT NOT NULL PRIMARY KEY,
[companyName] NVARCHAR(255) NOT NULL
)
And for views, I prepend them with the word "view". For example...
对于视图，我在它们前面加上“视图”一词。例如...
CREATE VIEW [crm].[viewFullEmployee]
AS SELECT e.id, e.companyId, e.niceId, e.startDate, e.fullPart, p.firstName, p.middleName, p.lastName, p.active, p.birthDate, p.email, p.alternateEmail, p.phone, p.phoneExt, p.homePhone, p.mobilePhone, p.jobTitle, p.suffix, p.prefix FROM [crm].[Employee] as e FULL JOIN [crm].[person] as p on e.id = p.id
I also split everything out into schemas and I do not use the default schema, forcing me to always specify a schema for things in my queries as nothing is in dbo.
我还将所有内容拆分为模式，并且不使用默认模式，这迫使我始终为查询中的内容指定模式，因为 dbo 中没有任何内容。
I know something is a table by lack of the word view in front of it. This also prevents having a table and a view with the same name.
我知道有些东西是一张桌子，因为它前面没有视图这个词。这也防止了具有相同名称的表和视图。
I don't like to use underscore names like "employee_v" because I generate all my data layer code with T4 templates "PetaPoco" and I don't like having to type _ in my code when referring to a type.
我不喜欢使用像“employee_v”这样的下划线名称，因为我使用 T4 模板“PetaPoco”生成所有数据层代码，而且我不喜欢在引用类型时必须在代码中键入 _。
I just like this better
我只是更喜欢这个
var person = uow.Db.Fetch<Models.viewFullEmployee>("SELECT * FROM [crm].[viewFullEmployee]");
vs
对比
var person = uow.Db.Fetch<Models.fullEmployee_V>("SELECT * FROM [crm].[fullEmployee_V]");
As for stored procedures I feel they don't need prefixes like "sp_" which I see so many people do. You know it's a stored procedure in use due to the prefix of EXEC, or Create Procedure, or Alter Procedure. As such I name my stored procedures like "addUpdatePerson", "getUserPermissions", etc.
至于存储过程，我觉得它们不需要像“sp_”这样的前缀，我看到很多人都这样做。由于 EXEC、Create Procedure 或 Alter Procedure 的前缀，您知道这是一个正在使用的存储过程。因此，我将存储过程命名为“addUpdatePerson”、“getUserPermissions”等。
Where I do use a prefix is on functions, like "fnValidateEmail" as they can clash with stored procedure names.
我在函数上使用前缀的地方，比如“fnValidateEmail”，因为它们可能与存储过程名称发生冲突。
回答by Espo
From http://vyaskn.tripod.com/object_naming.htm:
来自http://vyaskn.tripod.com/object_naming.htm：
There exist so many different naming conventions for database objects, none of them is wrong. It's more of a personal preference of the person who designed the naming convention. However, in an organization, one person (or a group) defines the database naming conventions, standardizes it and others will follow it whether they like it or not.
数据库对象有很多不同的命名约定，没有一个是错误的。这更多是设计命名约定的人的个人偏好。然而，在一个组织中，一个人（或一个组）定义了数据库命名约定，将其标准化，其他人无论喜欢与否都会遵循它。
Read the full articlefor details on how to implement/create it in your organisation.
阅读全文，详细了解如何在您的组织中实施/创建它。
