Standards

Standards are a set of guidelines on how to design, develop and write code for a database. They often include everything from how to name tables, to how to format your T-SQL code. The purpose of defining standards is to help make things predictable for everyone working with the database.

There are several objectives related to using standards:

  • Make the definition and naming of database objects consistent
  • Follow best practices in writing and using T-SQL code
  • Help developers follow best practices for SQL Server

Table and Column Naming

Some standards will vary from organization to organization. Table and column naming standards, for example, are often specific to an organization.

Naming Style Names

Many organizations adopt a standard of using camel-casing for database object names. For example, these standards might specify that multiple-syllable names such as “customer code” should be named as customerCode or Customer_Code (some refer to the latter as "snake case").

Best practice for SQL Server is to use “natural” naming for most objects. For example, a table for customers is normally named “Customers”. A table that stores account data is named “Accounts”. A table for customer addresses might be named “CustomerAddresses”.

Table names should NEVER include spaces. Names cannot begin with a number. Some organizations stipulate that table names should not include any special characters, while others allow underscores and hyphens.

Table names should be descriptive of the data they contain. The table name “CuAd437” does not describe anything; it is not helpful as an object name. “CustomerAddresses” provides a good description of the table purpose.

[The author prefers all lowercase naming and uses that in most projects.]

Plural or Singular Names?

This is a continuing point of argument among developers. Some prefer plural table names and other prefer singular table names. The former group would name a table “Customers” and the latter group would name the table “Customer”. It really does not matter which standard is followed. Neither is more or less correct than the other. The point of standards, however, is to be consistent. So only one convention should be followed, once the standard is established.

Abbreviations

Abbreviations in table naming should be avoided, unless the abbreviations are approved and documented. For example, a table should not be named “CustomerAccts” unless the abbreviation “Acct” has been approved for the project. Once approved, the abbreviation should normally be used consistently in all appropriate places. In other words, if “Acct” is an approved abbreviation, some tables should not be named “CustomerAccts” and some named “PreferredAccounts”.

Prefixes

Database objects should not prefixed with the object type. This is an old standard that was used in some databases, where tables were named tblCustomers”, for example. This is an outdated practice, and should not be followed.

Column Naming

Standards for column naming are the same as for tables (natural names, no spaces, no special characters, consistent casing, etc.). Columns should not be prefixed with the column data type (an old standard followed by some). As with tables, abbreviations should only be used when approved for the project, and then used consistently.

In some cases, column naming may be established for various types of data. For example, it is not unusual for all dates to be named with the word “Date” first, so that columns might be named “DateStart” and “DateEnd”, rather than “StartDate” and “EndDate”. The same thing might apply to multiple columns such as “QtyOnHand” and “QtyUsed”, rather than “OnHandQty” and “UsedQty” (assuming “Qty” has been approved as an abbreviation). These are conventions that would be organization or project specific. As with all standards, teams should strive for consistency in how they apply the guidelines.

In some cases, a standard might stipulate that any column including the word “Number” must be an actual number (instead of allowing alphanumeric characters) or that any column with a suffix of “ID” must be a primary or foreign key. The benefit of such standards are that developers are not surprised when writing code to use a number, for instance, when the values for the column may be alphanumeric characters.

Bit columns (which contain the Boolean values of 1 and 0) in SQL Server often have a standard that they include a prefix indicating the column is a Boolean, such as “IsActive”, “CanWorkWeekends”, “IsAvailable”, “HasOptions”, etc. This is another indicator to the developer that the column will contain only true/false Boolean values.

Table Design Standards

Beyond naming, standards may include guidelines for how tables are designed, related, indexed, etc.

For example, an organization might include a standard that all tables must include a primary key, and that the primary key must be the first column. Some standards may stipulate that the primary key is always an identity column and always numeric. Other organizations may stipulate that primary keys are always a Unique Number or Global Unique ID (GUID), which is a 36-byte character string. See More on GUID's

There are cases where these types of standards are "guidelines" that should be followed most of the time, but sometimes have exceptions. There may be cases, for example, where all tables can use a numeric primary key, but selected tables would benefit from using a GUID instead. In these cases, at a minimum, developers should document and explain why some objects vary from the standard.

Standards for Column Data Types

Assigning the data type for each table column depends on what type of data is being stored. There are general standards, however, that can be applied .

In SQL Server, it is a best practice to avoid NULL columns. That means that numeric and bit columns should be required and set with defaults using a numeric value. String columns can be set as required or use defaults of an empty string.

The two types of columns that will often allow NULL’s are Date columns and Foreign Keys.

Best practices are recommendations on how to use and work with a given database - developed by industry experts over many years. Not everyone agrees on Best Practices, but often a large part of the developer community does agree.

Below is a list of articles related to Best Practices for SQL Server. Be aware that many suggestions are related to advanced coding practices, and that not all experts agree on any one set of practices.

How are Standards Set?

Databases rarely exist by themselves. They usually support Internet and desktop applications, which means multiple development teams may need to interact and share requirements for different levels of the project. In these cases, it may be important that all teams agree on the same standards. Even standards such as column naming may be extremely important to establish as the project starts, and may adversely affect some teams more than others.

Let’s consider an example. An application being developed in Microsoft .Net, and using Visual Studio for front-end development, often uses a built-in Object Relational Mapper (ORM) to prepare code that works with the project database. ORM’s often read and use database properties such as column names and data types. This means that if the database objects have been defined without input of the front-end development team, the resulting coding objects might be more difficult to use than expected.

Example of Badly Designed App

The diagram below illustrates why standards are important. The database was designed to store numeric and date data as strings (varchar columns). As with many applications, the main server application (using an Object Mapper or ORM) reads and automatically converts the database design into business objects. In this case, the ORM converts the columns, as is, into strings in the business class for Customer. When data is requested from the client app, the API reads the table data using the business layer customer class, converts it into JSON, and sends it the client. On the client, JavaScript functions read the data and merge it with HTML for a form or report. In this case, the JavaScript functions will try to read a CreditLimit amount as a number, but instead receive a value such as "$5,000" - which is a string. If the point of the application, for instance, is to offer a customer an additional $1,000 line of credit, an error will occur if the code attempts to add "$5,000" to 1000. Obviously, the web and database developers were not on the same page as they built the separate parts of this application.

Data Flow for Badly Designed Web App

Figure 1: Web App and Database with Poor Standards