Naming standards? Singular or Plural

It’s important that for any software application good standards exist. Standards ensure a number of key considerations. Standards are necessary to enforce and provide reproducible software and to provide a level of quality in a team environment, ease of readability and consistency.

If you were going to create a MySQL Naming Standard you have to make a number of key decisions. Generally there is no true right or wrong, however my goals tend towards readability and simplicity. In 2 decades of database design I’ve actually changed my preference between some of these points.

1. Pluralism

Option 1
All database objects are defined in the logical form, that being singular.

For example: box, customer, person, category, user, order, order_line product, post, post_category

Option 2

For database tables & views, objects are defined in plural. For columns, objects are defined singular.

For example: boxes, customers, people, categories, users, orders, order_lines, products, posts, post_categories

Issues
Inconsistency between table name and column name, when using plural. Column names simply are not plural.
When the plural of the name is a completely different spelled word. For example a table of People, and a primary key of PersonId.
When the plural rule is not adding ‘s’, for example replacing ‘y’ with ‘ies’ as with Category.
Strict rule necessary for relationship and intersection tables. Generally, only the last portion is plural.
What about other objects, such as stored procedures for example.

2. Case Sensitivity

Option 1
All database objects should be specified as lowercase only. Words are separated with ‘underscores’.

For example: customer, customer_history, order, order_line, product, product_price, product_price_history

Option 2
All database objects use CamelCase. Words are separated via Case.

For example: Customer, CustomerHistory, Order, OrderLine, Product, ProductPrice, ProductPriceHistory

Issues
Some database products have restrictions here, Oracle for example, UPPERCASES all objects. MySQL allows for both, except for some Operating Systems that does not support Mixed Case properly (e.g. Microsoft)

3.Key names

In a related post I will be discussing natural and surrogate keys. For this purpose, we will assume you are using surrogate keys.

All keys will have a standard name. For Example: id, key, sgn (system global number)

When referencing primary keys and foreign keys, what standard is used?

Option 1
The primary key is the same name across all tables, making it easy to know the primary key of a table.
For foreign keys, the name is prefixed with the table name or appropriate table alias.

For Example: id. The foreign key would be customer_id, order_id, product_id

Option 2
The primary key is defined as unique across the system, and as such the foreign key is the same as the primary key.

For Example: customer_id, order_id, product_id

Issues
When self referencing columns to a table, having a standard is also appropriate, for example parent_product_id.

4. Specific Object Names

Option 1
For the given type of object, have a standard prefix or suffix.

For Example, all tables are prefixed with tbl, all views are suffixed with _view, all columns for a given table are prefixed with table alias.

Option 2
Don’t prefix or suffix an object with it’s object type.

5. Reserved Words

Option 1
Don’t use them. When a word is reserved, find a more description name.

E.g. system_user, order_date,

Option 2
Allow them.

For Example: user, date, group, order

Issues
A number of database systems do not allow the use of reserved words, or historically have not. Sum such as MySQL for example, allow reserved words, but only when additional quoting is used.

6. Abbreviations

This indeed could be a entire topic it’s self. In simplicity, do you use abbreviations other then the most common and everybody knows abbreviations (to the point you don’t know what the abbreviation actually is in real life type abbreviations), or do you not.

I use the example of ‘url’. How many people actually know what url stands for. This is a common abbreviation.

Option 1
For objects, use abbreviations when possible. Don’t use it for key tables, but for child and intersection tables.
For Example: invoice, inv_detail, inv_id,

Option 2
Avoid abbreviations at all costs.
invoice, invoice_detail, invoice_id,

Issues
Unless you have a large schema (e.g. > 500 tables) the use of abbreviations should not be needed given the relative sizes of objects in modern databases.

My Recommendations

There are a lot more considerations then these few examples for naming standards, however as with every design, it’s important to make a start and work towards continual improvement.

1. All objects are singular (very adamant, people/person, category/categories, sheep/sheep for tables, but not columns – simplicity wins as English is complex for plurals).
2. All objects are lowercase and use underscore ‘_’ (I really like CamelCase for readability, but for consistency and simplicity, unfortunately lowercase is easier).
3. All primary key’s are defined as unique across the system.
4. Don’t use prefixes/suffixes to identity object types.
5. Never use reserved words.
6. Don’t use abbreviations except for the most obvious.

At the end of the day, I will work with what standard is in place. What I won’t work with is, when there is no documented, accountable standard.