Singular vs Plural and Other Database Table Naming Conventions

Are you wondering if you should name your Oracle database tables in the singular form or plural form?

Learn what the preferred convention is and some reasoning behind it, as well as other database table naming conventions, in this article.

Why Do We Need SQL Table Naming Conventions?

You might be wondering why we need conventions or best practices for naming SQL tables in the first place.

Can’t we just name it what we want, and move on?

We could do that… but it’s better to have naming conventions for several reasons:

Consistency for developers. If the developers are working with their own coding standards, using the same conventions for table names and object names in their code will make life easier for the team. There’s a lot of time and effort wasted trying to work out if your table is called Customer or Customers, or if it’s your object in your source code that’s called Customers and your table called Customer.

Databases may outlive applications. A well designed database can stand for a long time – longer than many applications. I’ve worked on databases where the application has been redesigned or replaced, but the database has stayed the same. It’s important to get it right at the start and follow conventions.

Saving time. If you set up table naming conventions at the start, you can save time later on when you need to create new tables or enhance the database in other ways.

Setting up a naming convention for your database tables will take a bit of time at the start, but it will save time with the developers.

So, let’s move on to what I think are good Oracle database table naming conventions.

Get Your Free PDF: 9 Ways to Improve your Database Skills

Why Oracle-Specific?

I’ve documented these database conventions to be used with Oracle databases.

These SQL table naming conventions can be applied across any relational database. You’re not limited to just Oracle.

But, I’ve focused on developers working on Oracle databases for several reasons:

Who Says Plural Table Names are Better?

Why I Prefer Singular Database Table Names

There are several reasons why I prefer singular table names over plural table names.

Consistency Throughout Your System

The biggest reason why I prefer singular table names instead of plural table names when it comes to database table naming conventions is that it ensures consistency between all areas of your application.

Developers work with SQL code, database tables, and their own application code. In object-oriented programming, classes are singular nouns (e.g. Bike, Car, Student).

This means that if we use singular table names (such as car or student), we can ensure that the same convention is easily followed for all types:

A bag containing applies can be called an AppleBag, regardless of how many apples it contains. Tables are the same thing.

Singular Names are Easier to Create

It’s easy to come up with singular names for SQL table names. Well, it might not be that easy, but it’s easier than plural names.

You might have a “person” table. This is a singular name. What would you call the plural version of the table? People? Persons?

The pluralisation of words is an English language concept to help with speaking. SQL and databases don’t need to know about plurals of words.

There Are Many Non-English Developers

The number of non-English-speaking developers is rising. These non-English native speaking developers may not understand the concept of pluralisation of words. It’s easier for these developers to understand singular versions of words.

Whether your team contains all English-speaking developers, or has some non-English native developers, it’s better to plan for the future and make your code as easy to understand as possible to all developers.

The Concept of Master and Detail is Easier to Understand

If you’ve seen examples of databases where a master and detail record is used, or even when you’ve created some yourself, you might have found it easier to understand to use singular names.

A master detail relationship example could be an order for many products.

The main order can contain many order line items.

To represent this in tables, they could be named using singular form:

Order

OrderDetail

Or, the table names using plural form:

Orders

OrderDetails

The plural version is a little inconsistent as it uses a different term.

For master detail tables, it’s easier to use a singular table name.

Other Database Table Naming Conventions

In addition to using singular or plural words for SQL table names, there are several other conventions that I would recommend when working with database tables.

Use Underscores To Separate Words Instead Of Camel Case

Several sources recommend using CamelCase to separate words.

This isn’t a good idea when working with Oracle, for several reasons.

It’s harder to read when skimming down an SQL file.

For example:

SELECT * FROM ApplicationUsers;

Or

SELECT * FROM applicationUsers;

It’s better to use an underscore to separate words, to improve readability.

SELECT * FROM application_users;

This way it’s clearer what the name of the table is.

The second reason, and probably the more important one, is that Oracle SQL is case-insensitive.

It doesn’t care what case your table names are in.

This matters because the objects are stored in the database in the same case, so naming them using camel case will be lost.

Also, when you use SQL formatters (and you should, especially the built-in one in SQL Developer), it may cause you to lose the camel case.

Abbreviating words in the table name using commonly known abbreviations (e.g. customer_historic_invoice_billed (32 characters) could become cust_hist_invoice_billed (24 characters))

Avoid Reserved Words

There are several words in Oracle SQL that are reserved for use by the database. These words should not be used to create tables with.

A common example is User. Creating a table with the name of User will probably cause an error, because user is a reserved word. If you create it without an error (for example, by enclosing it in quotes), then it will still cause confusion.

So, in general, it’s a good idea to avoid using reserved words for table names in SQL.

Don’t Add Prefix of Object Type to the Name

In the earlier days of database development, it was a common practice to add a prefix to an object name that signals what kind of object it is.

For example, tbl_customer for a table, or vw_all_customers for a view.

The idea is that developers could see the object name and immediately know it was a view or a table, and develop on it accordingly.

However, using prefixes on an object name not only wastes valuable characters, it also makes maintenance worse.

What if you needed to change the table to a view of the same name? If you kept using this standard, you’d have to rename it from tbl_ to vw_, causing your code to break unless you changed the code.

If you avoid using prefixes in your SQL table names, it makes maintenance easier. It’s a good SQL table naming convention to follow.

A student can have many subjects, and a subject can have many students.

This is a many-to-many relationship. In relational database design, this needs to be reflected using a joining table – a table that captures this relationship.

This table also needs a name.

Common suggestions of table name conventions would be to name the table the concatenation of the two tables that join to it. In this case, it would be student_subject (or subject_student)

This is not a good idea as it doesn’t effectively describe the object you’re referring to. Giving it a better name will improve the developer’s code and improve understanding of the database.

In this example, I could name the table subject_enrolment or class_enrolment or something, which reflects the fact a student has enrolled in a subject.

Conclusion – Singular Table Names are Preferred

I hope you’ve learnt something from this post and understand the benefits of using singular table names in your database.

The main point here is that you should be consistent. If your team already uses plural names, then stick with it. If there is no table naming convention, then choose singular.

What are your thoughts? Are you pro-singular or pro-plural? Are there any other naming conventions for tables that I’ve missed out? Do you have any other questions? Let me know in the comments below.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your Free PDF: 9 Ways to Improve your Database Skills

2 Comments

Brad Jefferson
on December 3, 2018 at 11:16 pm

Great article on table naming conventions. Though, I am confused when you used some examples with the plural applied (e.g., “subject_enrolments or class_enrolments “). Is this a special case where you want a plural table name used?

Good point Brad – I seem to have contradicted myself! Those tables should probably be called “subject_enrolment” and “class_enrolment” to be consistent. I’ll update the article.
I can’t think of a special case to use plural names, but it may depend on the project.