Table Names
Naming conventions are a set of rules (written or unwritten) that should be utilized in order to increase the readability of the data model. You may apply these rules when naming anything inside the database, including tables, columns, primary and foreign keys, stored procedures, functions, views, etc. You need not apply rules to all database objects. For instance, it would be perfectly fine to limit naming convention rules to tables and column names. It's really your decision, as using a naming convention is not mandatory, but beneficial nonetheless. This three part series will present some commonly used naming conventions and provide some tips for formulating your own. Part 1 will cover Table names, while Part 2 will focus on column names. Finally, Part 3 will address Naming Conventions for other database objects such as Foreign Keys, Procedures, Functions, and Views.
Why You Should Use a Naming Convention
Databases rarely have a small number of tables. In fact, it's not at all uncommon to have hundreds of tables. By following a naming convention, you'll make your life a lot easier by increasing the overall model readability, and making it easier to locate database (DB) objects.
Another good reason is that the database will slowly evolve over time. Although changes to the schema are usually avoided and done only when necessary, changing the name of a database object could affect your application code in a myriad of ways. Since you can expect the database will remain, more or less, very similar to its initial incarnation, if you apply best practices from the start and continue using them as you add new objects, you'll keep your database structure well organized over time.
Singular vs. Plural Table Names
One of the most commonly asked questions regarding the naming of tables is whether to use the singular or plural form. There are many differing opinions on this matter. In fact, we can see both views expressed in the schemas of the MySQL classicmodels and sakila sample databases, with the former employing plural table names, and the latter utilizing singular naming:
If it helps, most DBAs go with singular names. One reason is that plural names like "users" "roles" could lead to some weird table names down the road such "users_have_roles" rather than "user_has_role".
Describing Real-World Entities
Any time that you're naming entities that represent real-world things, you should use their proper nouns. These would apply to tables like employee, customer, city, country, etc. Usually, a single word should exactly describes what is in that table.
There are times that you'll have to use more than one word to describe what is in a table. One such example can be seen in the classicmodels database. There is one table for "orders" and another for "order_details":
The "orders" table contains fields such as the Customer ID, Employee ID, Order Date, Shipped Date, Freight, and Shipping Address. Meanwhile, the "order_details" contains data about the products ordered, such as the Quantity ordered and Price. The field could have been named "product_details" but that would not convey that the product was associated with an order.
Naming Related Tables
For relations between two tables, it's standard practice to use both tables' names. A verb may also be added between both names to describe what that action is, for example "user_has_role", or simply "user_role". The Sakila Sample Database follows this convention by joining related tables with an intermediary table that combines both names. We can observe two examples in the database model below - "film_actor" and "film_category":
Final Thoughts on Table Naming Conventions in SQL
Don't be afraid to stray from a naming convention if it doesn't make logical sense in a given situation. For example, if we had a product and invoice table, and we wanted to specify which products were on which invoice, the name "invoice_item" might make more sense than either "invoice_product" or "invoice_contains_product".
