Naming Database Tables and Views

开发者_如何学JAVAI 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?

---

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.

For your colleague to bring his naming convention from a previous organization into a new one without checking 'local' standards is bad practice.

---

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.

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.

---

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.

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.

---

It think akf answered the question well. And HLGEM makes a good point about refactoring.

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.

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.

---

From 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 article for details on how to implement/create it in your organisation.

---

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.

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.

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.

Where I do use a prefix is on functions, like "fnValidateEmail" as they can clash with stored procedure names.

---

I think one of the best resources is always the AdventureWorks database from Microsoft. If you look at their views, they prefix them with a lowercase "v".

For example the table Employee has a view vEmployee.