Browse DevX
Sign up for e-mail newsletters from DevX


Develop a Consistent Naming Convention for Your Database Objects : Page 2

The sheer number of objects in a database makes keeping track of them difficult. As your system grows in complexity, identifying the objects' dependencies and their purposes can quickly become confusing. Adopt a naming convention that limits the ambiguity and uncertainty surrounding an object and its purpose.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Getting Started
When you are naming a new database object, you can segment the name to communicate its role in the system. A useful rule of thumb is to have a template for your names: a prefix, a stem, and a modifier that stand for the type, name, and function of the object you're naming, respectively. This approach can be overkill, but more often than not it makes the name much more communicative. As an example of overkill, it doesn't make much sense to add the prefix "tbl" to a table name. In this case, your convention could be to assume that a base table is named for the conceptual entity for which it stands, and forget about the prefix. On the other hand, prefixing a view of that table with a "v" would make plain that the user of that object is dealing with some variant (subset or join) of a base table.

A table sometimes represents more than a conceptual entity. For example, when expressing a many-to-many relationship, to decomposing the relationship into its participants is a must. The challenge arises from having to "downshift" the management of many-to-many relationships into terms that the database can handle (i.e., you recognize many-to-many relationships conceptually, but the database can manage only one-to-ones or one-to-manys). So you need to manage the representation of a real world fact in terms the database can handle. For associative relationships or intersection tables, mapping to reality breaks down when you try to cleanly represent the thing you are describing.

How Do the Experts Do It?
Consider how SQL Server's creators signal the meaning of their own objects. Take a look at a user database or one of the system database tables. You'll notice a bunch of them are prefixed with "sys". In user databases, I averred that it makes sense to ignore the prefix rule for the conceptual entities, but with utility tables or helper tables (a temporary table or a calendar table, for example) keeping them segregated from the application-based tables is helpful. I still advocate keeping prefixes off tables when the best you can think of is "tbl".

In SQL Server's user database system tables, the convention is to segregate the sys tables from the ones you will create and maintain. Likewise the system stored procedures are prefixed with sp, and some are further modified with MS, help, or repl, communicating more about their application and purpose. A table prefix is a useful convention applied judiciously to entities you or your application shouldn't touch.

Transactional vs. Analysis Oriented
An extension to this option is to prefix table names with the subject area to which they are related. Naming tables by subject area in a transactional system is a bad idea because a transactional database can be shared among many applications. Transactional databases are application oriented, and their entities need to be shared among the applications they service. A sales order-processing system may reference service products that have no packaging in contrast to an inventory product. Inventory products may be composed of kits, individual inventory items bundled together with accessories. Physically expressing these subtypes (service and inventory items for sale) as tables and naming them with prefixes contributes more confusion than clarity. It makes more sense to use a subtype identifier to distinguish between them and express the bundles in their own intersection table.

Conversely, naming tables for their subject area is an excellent idea for tables that have been migrated to a data warehouse. Grouping tables by subject area in a transaction-oriented system confuses the aims of a transactional system with a data warehouse implementation, where the tables are subject area oriented. This begs the question of the appropriate time to move to two systems, one transaction oriented and the other analysis oriented, and this decision is usually motivated by system performance rather than academics. The subject area prefix in the analysis-oriented implementation is fine.

Plural vs. Single
Another issue in table naming is the plural versus singular argument. Some think tables should be named in the plural, while others think they should be named in the singular. I think that plurals make more sense from a practical perspective. For example, I think of the Employees table as a collection of individual Employee instances. It makes more sense to think that you are selecting an employee instance, a single row, from a collection that houses all the Employees. So while reading a Where clause, it sounds like better grammar to say that you want to select the Employee from Employees where the Employee number is 12.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date