Portable SQL/Tips/Identifier Naming

There is much discussion in the community how tables, columns and other identifiers should be named. I would like to give you some hints on what to keep an eye on, would like to propose a scheme that has proven itself in many projects and show you the advantages of this naming scheme.

Use table prefixes

Many problems arise from the thinking that your tables will be the only ones in the database in every installation worldwide. Unfortunately, this isn't the case and often applications need to be combined with others to create an improved user experience. To be able to access tables from both applications at once and to perform SELECTs on them to retrieve combined data, both applications have to use the same database and therefore have to share the same namespace.

Often, this is the point where conflicts arise. For example, a shop application uses a "categories" table as well as the forum application and both tables have incompatible definitions. In this cases it doesn't work that they share the same user table for login by simply adapting the login mechanism of the forum. These kind of problems often arise with third party products if you don't use prefixes for your tables. Therefore, always use a short string in front of the table name and separate both by an underscore.

Good examples are:

• mwiki_bookmarks
• shop_products
• lw_hosts

The prefix shouldn't be longer than 4-6 characters and it's best to use a short form of your application name.

Short and descriptive

The identifiers you use should help other users to understand what's the content of the table or column. It should describe the content as short as possible without sacrificing the descriptiveness. It's important that the name explains the purpose. I prefer column names in singular and table names in plural form as a column stores a single value but tables store multiple rows.

Table examples:

• mwiki_bookmarks
• shop_products
• lw_hosts

Column examples:

• bm_name
• sp_name
• lwh_name