Naming Database Tables and Views

Question

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?

Solution 1

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.

Solution 2

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.

Solution 3

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.

Solution 4

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.

Solution 5

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.

View more solutions