DevCampus Database Naming Conventions
Database Naming Conventions Version 1.1
Last Revised May 13, 2004 by Jason Mauss
The main goal of adopting a naming convention for database objects is so that you and others can easily identify the type and purpose of all objects contained in the database. The information presented here serves as a guide for you to follow when naming your database objects. When reading these rules and guidelines remember that consistent naming can be the most important rule to follow. Keep in mind that following the guidelines as outlined in this document can still produce long and cryptic names, ultimately, your unique situation will dictate the reasonability of your naming convention. The goal of this particular naming convention is to produce practical, legible, concise, unambiguous and consistent names for your database objects.
While most databases contain more types of objects than those discussed here (User Defined Types, Functions, Queries, etc.), the 7 types of objects mentioned here are common among all major database systems. Think of this as a generic DBMS-neutral guide for naming your objects.
The following types of database objects are discussed here:
- Tables
- Columns (incl. Primary, Foreign and Composite Keys)
- Indexes
- Constraints
- Views
- Stored Procedures
- Triggers
ALL DATABASE OBJECTS
- Limit the name to 30 characters (shorter is better)
- Use only letters or underscores (try to avoid numbers)
- Try to use underscore characters as little as possible. PascalCase notation achieves the same word separation without them.
- Use a letter as the first character of the name. (don't start names with underscores)
- Avoid abbreviations (can lead to misinterpretation of names)
- Avoid acronyms (some acronyms have more than one meaning eg. "ASP")
- Makes the name readable (they shouldn't sound funny when read aloud)
- Avoid using spaces in names even if the system allows it.
1. TABLES
When naming your database tables, give consideration to other steps in the development process. Keep in mind you will most likely have to utilize the names you give your tables several times as part of other objects, for example, procedures, triggers or views may all contain references to the table name. You want to keep the name as simple and short as possible. Some systems enforce character limits on object names also. For example, in Oracle you are limited to about 30 characters per object.
Rule 1a (Plural Names) - Table names should be plural, for example, "Customers" instead of "Customer". This rule is applicable because tables are logical collections of one or more entities as records - just like collection classes are logical collections of one or more objects. If you were to first draw an abstract data model like a NIAM/ORM model, you might have singular entity names like "Customer" or "User" but, they should be changed to the plural form when building the actual tables. For table names with multiple words, only the last word should be plural, for example, "UserRoles" and "UserRoleSettings".
Rule 1b (Prefixes) - Used correctly, table prefixes can help you organize your tables into related groups or distinguish them from other unrelated tables. Used poorly, they can cause you to have to type a lot of unnecessary characters. We'll discuss what not to do first. Do not give your table names prefixes like "tbl" or "TBL_" as these are just redundant and unnecessary. It will be obvious which names are the table names in SQL statements because they will always be proceeded by the FROM clause of the SELECT statement. Not all prefixes are bad. In some cases, your tables might be sharing a schema/database with other tables that are not related in any way. In this case, it is sometimes a good idea to prefix your table names with some characters that group your tables together. For example, for a healthcare application you might give your tables an "Hc" prefix so that all of the tables for that application would appear in alphabetized lists together. Note that even for the prefix, use Pascal Case. This is discussed in Rule 1c. Do not use underscores in your prefixes, which is discussed in more depth in Rule 1d. The last kind of prefix that is acceptable is one that allows you to group logical units of tables. A plausible example could entail a large application (30 to 40+ tables) that handled both Payroll and Benefits data. You could prefix the tables dealing with payroll with a "Pay" or "Prl" prefix and give the tables dealing with benefits data a "Ben" or "Bfts" prefix. The goal of both this prefix and the aforementioned shared schema/database prefix is to allow you to group specific tables together alphabetically in lists and distinguish them from unrelated tables. Lastly, the shared schema/database prefix is a higher grouping level and comes first in the name, for example, "HcPayClients" not "PayHcClients".
Rule 1c (Notation) - For all parts of the table name, including prefixes, use Pascal Case. Using this notation will distinguish your table names from SQL keywords (all capital letters). For example, "SELECT CustomerId_Pk, CustomerName FROM MyAppGroupTable WHERE CustomerName = '%S'" shows the notation for the table name distinguishing it from the SQL keywords used in the query. PascalCase also reduces the need for underscores to visually separate words in names.
Rule 1d (Special Characters) - For table names, underscores should not be used. The underscore character has a place in other object names but, not for tables. Using Pascal Case for your table name allows for the upper-case letter to denote the first letter of a new word or name. Thus there is no need to do so with an underscore character. Do not use numbers in your table names either. This usually points to a poorly designed data model or irregularly partitioned tables. Do not use spaces in your table names either. While most database systems can handle names that include spaces, some systems require you to add more characters around the name when referencing it (like [table name] for example) which goes against the rule of keeping things as short and simple as possible. If you are developing in a non-english language, do not use any of that language's special characters.
Rule 1e (Abbreviations) - Avoid using abbreviations if possible. Use "Accounts" instead of "Accts" and "Hours" instead of "Hrs". Not everyone will always agree with you on what your abbrevations stand for - and - this makes it simple to read and understand for both developers and non-developers. This rule can be relaxed for junction table names (See Rule 1f). Do not use acronyms.
Rule 1f (Junction a.k.a Intersection Tables) - Junction tables, which handle many to many relationships, should be named by concatenating the names of the tables that have a one to many relationship with the junction table. For example, you might have "Doctors" and "Patients" tables. Since doctors can have many patients and patients can have many doctors (specialists) you need a table to hold the data for those relationships in a junction table. This table should be named DoctorsPatients". Since this convention can result in lengthy table names, abbreviations sometimes may be used at your discretion.
2. COLUMNS - (incl. PRIMARY, FOREIGN, AND COMPOSITE KEYS)
When naming your columns, keep in mind that they are members of the table, so they do not need the any mention of the table name in the name. The primary key field is typically the only exception to this rule where including the table name is justified so that you can have a more descriptive field name than just "Id". "CustomerId" is acceptable but not required. Just like with naming tables, avoid using abbreviations, acronyms or special characters. All column names should use Pascal Case to distinguish them from SQL keywords (all upper case).
Rule 2a (Identity Primary Key Fields) - For fields that are the primary key for a table and uniquely identify each record in the table, the name should simply be “Id“ since, that's what it is - an identification field. This name also maps more closely to a property name like “Id“ in your class libraries. Another benefit of this name is that for joins you will see something like
"Customers JOIN Orders ON Customer.Id = Orders.CustomerId“
which allows you to avoid the word “Customer“ again after the Customer table.
Rule 2b (Foreign Key Fields) - Foreign key fields should have the exact same name as they do in the parent table where the field is the primary key - with one exception - the table name should be specified. For example, in the Customers table the primary key field might be "Id". In an Orders table where the customer id is kept, it would be "CustomerId". There is one exception to this rule, which is when you have more than one foreign key field per table referencing the same primary key field in another table. In this situation, it might be helpful to add a descriptor before the field name. An example of this is if you had an Address table. You might have another table with foreign key fields like HomeAddressId, WorkAddressId, MailingAddressId, or ShippingAddressId.
Rule 2c (Composite Keys) - If you have tables with composite keys (more than one field makes up the unique value) then instead of just “Id“ you should use a descriptor before the “Id“ characters. Two fields like “ModuleId“ and “CodeId“ might make up the composite key, for example. If you don't see an “Id“ column in the table - you'll know that a composite key is used to uniquely identify records.
Rule 2d (Prefixes) - Do not prefix your fields with "fld_" or "Col_" as it should be obvious in SQL statements which items are columns (before or after the FROM clause). Including a two or three character data type prefix for the field is optional and not recommended, for example, "IntCustomerId" for a numeric type or "VcName" for a varchar type. However, these data type abbreviations are DBMS specific and are outside the scope of this document.
Rule 2e (Data Type Specific Naming) - Boolean fields should be given names like "IsDeleted", "HasPermission", or "IsValid" so that the meaning of the data in the field is not ambiguous. If the field holds date and/or time information, the word "Date" or "Time" should appear somewhere in the field name. It is sometimes appropriate to add the unit of time to the field name also, especially if the field holds data like whole numbers ("3" or "20"). Those fields should be named like "RuntimeHours" or "ScheduledMinutes".
3. INDEXES
Since indexes are always related to a table or view, it makes the most sense to use the name of the table or view, as well as the column(s) they index, in the index name, along with some characters that specify the type of index it is. This naming convention also allows you, if looking at a list of indexes, to see the indexes ordered by table, then column, then index type.
Rule 3a (Naming Convention) - The naming convention for indexes follows this structure:
{TableName}{ColumnsIndexed}{U/N}{C/N}
where "U/N" is for unique or non-unique and "C/N" is for clustered or non-clustered. This naming convention is unique among database objects, so adding characters to denote it being an index, like "idx" is not necessary. The naming convention alone is self-documenting and indentifies it as an index. For indexes that span multiple columns, concatenate the column names. "ProductsIdUC" indicates a unique, clustered index on the Id column of the Products table. OrderDetailsOrderIdCustomerIdNN" indicates a non-unique, non-clustered index on the OrderId and CustomerId columns in the OrderDetails table. Since this name is rather lengthy with both "OrderId" and "CustomerId" spelled out, they could be shortened to OrdId and CustId. However, notice that by using Pascal Case, thus not needing to use underscores, it is possible to keep the name of a complex index to about 30 characters.
Rule 3b (Prefixes and Suffixes) - Avoid putting a prefix like "idx" or "IDX_" before your indexes. This is not necessary due to the naming convention discussed in Rule 3a. A suffix of "_idx" or "IDX" is not necessary either for the same reason.
4. CONSTRAINTS
Constraints are at the field/column level so the name of the field the constraint is on should be used in the name. The type of constraint (Check, Referential Integrity a.k.a Foreign Key, Primary Key, or Unique) should be noted also. Constraints are also unique to a particular table and field combination, so you should include the table name also to ensure unique constaint names across your set of database tables.
Rule 4a (Naming Convention) - The naming convention syntax for constraints looks like this:
{constraint type}{table name}_{field name}
Examples:
1. PkProducts_Id - primary key constraint on the Id field of the Products table
2. FkOrders_ProductId - foreign key constraint on the ProductId field in the Orders table
3. CkCustomers_AccountRepId - check constraint on the AccountRepId field in the Customers table
The reason underscores are used here with Pascal Case notation is so that the table name and field name are clearly separated. Without the underscore, it would become easy to get confused about where the table name stops and the field name starts.
Rule 4b(Prefixes) A two letter prefix gets applied to the constraint name depending on the type
Primary Key: Pk
Foreign Key: Fk
Check: Ck
Unique: Un
5. VIEWS
Views follow many of the same rules that apply to naming tables. There are only two differences (Rules 5a and 5b). If your view combines entities with a join condition or where clause, be sure to combine the names of the entities that are joined in the name of your view. This is discussed in more depth in Rule 5b.
Rule 5a (Prefixes) - While it is pointless to prefix tables, it can be helpful for views. Prefixing your views with "Vw" or "View" is a helpful reminder that you're dealing with a view, and not a table. Whatever type of prefix you choose to apply, use at least 2 letters and not just "V" because a prefix should use more more than one letter or its meaning can be ambiguous.
Rule 5b (View Types) - Some views are simply tabular representations of one or more tables with a filter applied or because of security procedures (users given permissions on views instead of the underlying table(s) in some cases). Some views are used to generate report data with more specific values in the WHERE clause. Naming your views should be different depending on the type or purpose of the view. For simple views that just join one or more tables with no selection criteria, combine the names of the tables joined. For example, joining the "Customers" and "StatesAndProvinces" table to create a view of Customers and their respective geographical data should be given a name like "VwCustomersStatesAndProvinces". For a view that is more like a report, a name like "VwDivisionSalesFor2004" might make more sense.
6. STORED PROCEDURES
Unlike a lot of the other database objects discussed here, stored procedures are not logically tied to any table or column. Typically though, stored procedures perform one of the CRUD (Create, Read, Update, and Delete) operations on a table, or another action of some kind. Since stored procedures always perform some type of operation, it makes sense to use a name that describes the operation they perform. Use a verb to describe the type of operation, followed by the table(s) the operations occur on.
Rule 6a (Prefixes or Suffixes) - The way you name your stored procedures depends on how you want to group them within a listing. If you'd like to group them by the type of CRUD operation they perform, then prefix the name with "Create", "Get", "Update" or "Delete". Using this kind of prefix will, for example, group all of your "Create" procedures together since they will all start with the Create prefix, like "CreateProductInfo" or "CreateOrder". If instead, you would like to have your procedures ordered by the table they perform a CRUD operation on, adding "Create, Get, Update, or Delete" as a suffix will do that for you. For example, "ProductInfoCreate" or "OrdersCreate". If your procedure returns a scalar value, or performs an operation like validation, you should not use a CRUD prefix or suffix. Instead use the verb and noun combination. For example, "ValidateLogin"
Rule 6b (Grouping Prefixes) - If you have many stored procedures, you might want to consider using a grouping prefix that can be used to identify which parts of an application the stored procedure is used by. For example, a "Prl" prefix for Payroll related procedures or a "Hr" prefix for Human Resources related procedures can be helpful. This prefix would come before a CRUD prefix (See Rul 6a).
Rule 6c (Bad Prefixes) - Do not prefix your stored procedures with something that will cause the system to think it is a system procedure. For example, in SQL Server, if you start a procedure with "sp_", "xp_" or "dt_" it will cause SQL Server to check the master database for this procedure first, causing a performance hit. Spend a little time researching if any of the prefixes you are thinking of using are known by the system and avoid using them if they are.
7. TRIGGERS
Triggers have many things in common with stored procedures. However, triggers are different than stored procedures in two important ways. First, triggers don't exist on their own. They are dependant upon a table. So it is wise to include the name of this table in the trigger name. Second, triggers can only execute when either an Insert, Update, or Delete happens on one or more of the records in the table. So it also makes sense to include the type of action that will cause the trigger to execute.
Rule 7a (Prefixes and Suffixes) - To distinguish triggers from other database objects, it is helpful to add "Trg" as a prefix or suffix. For example any of these combinations work: Trg_ProductsIns, ProductsInsTrg, Products_InsTrg, or InsProducts_Trg. As long as you include the table name, the operation that executes the trigger (Ins, Upd, or Del) and the "Trg" letters, it should be obvious to someone working with the database what kind of object it is. As with all conventions you use, pick one and remain consistent.
Rule 7b (Multiple Operations) - If a trigger handles more than one operation (both INSERT and UPDATE for example) then include both operation abbreviations in your name. For example, "Products_InsUpdTrg" or "TrgProducts_UpdDel"
Rule 7c (Multiple Triggers) - Some systems allow multiple triggers per operation per table. In this case, you should make sure the names of these triggers are easy to distinguish between. For example "Users_ValidateEmailAddress_InsTrg" and "Users_MakeActionEntries_InsTrg".