Singular vs Plural and Other Database Table Naming Conventions

Are you wondering if you should name your SQL 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 for the developers.

(Related – How to Become a Database Developer: The Definitive Guide)

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


Singular vs Plural Table Names: Should Database Table Names Be Singular or Plural?

I’ve done some research on this topic from a variety of sources. I’ve also used my own experience in deciding whether singular or plural table names are better.

If you have existing standards within your team or project, use those. It’s better to be consistent than to use one convention in particular.

If you don’t have standards, then you’ll need to decide on singular vs plural table names.

In my opinion, singular table names are better than plural.

I have several reasons for this, which I’ll mention shortly.

But first, let me show you several different sources who have different opinions on this topic.

The number of articles for each opinion shouldn’t matter. I’ve just added them to demonstrate that there are different views on the topic and which pages/websites have each of these views.


Who Says Singular Table Names are Better?

There are several sources that I found that believe singular table names are better.


Who Says Plural Table Names are Better?

There are several sources that state they prefer plural table names:


Why I Prefer Singular Database Table Names

There are several reasons why I prefer singular table names over plural table names. I’ve written more about them here in my SQL Best Practices and Style Guide post.


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:

  • Database tables (including views and synonyms)
  • SQL statements that query these tables
  • Functions and APIs that use this SQL or tables
  • Objects in source code in all languages and technologies being used in the application

This way, a developer knows that their student class uses a getStudent() function and returns a Student object using a SELECT query on the student table.

Well, maybe that’s not the exact method they would use, but the point is that the class and database table and everything else are all singular.

This is a big time saver and prevents developers from trying to work out which form they used when naming tables.


The Concept of a Table is a Container of Single Rows

The second reason relates to what a table is. A table stores rows. It can store zero, one, or many rows. The name should not imply the number of items in the table.

A table is, therefore, a container. And a container is a single object. Whether it contains multiple rows or a single row does not impact its name, and therefore the name should be singular.

A good example is the AppleBag example used in the top-rated singular/plural answer in this Stack Overflow question.

A bag containing apples 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 for 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 the 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 SQL, for several reasons.

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

For example:

SELECT * FROM ApplicationUsers;


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 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.

The SQL database doesn’t need camel case and doesn’t differentiate, so neither should you.

So, using underscores to separate words in SQL table names is easier to distinguish and will be stored as expected in the database.


Don’t Use Quotes

If you want to store a table name in a certain case or in a certain format, you could use quotes to store the name.

For example:

CREATE TABLE "ApplicationUser" ...

This will store the table name as ApplicationUser.

However, this means that every time you want to refer to the table, you’ll need to use quotes.

And sometimes you’ll forget, and get an error.

Or other developers won’t know or won’t remember, and they will get an error or get incorrect results.

So, avoiding quotes is a good SQL table naming convention to use.

This also means you should avoid spaces in database table names. The only way to add spaces is to enclose the entire name in quotes (such as “Application User”), which will cause the same issues.


Use Full Words

It might be tempting to save a few keystrokes and abbreviate your table names.

So, instead of creating a customer table, you decide to call it cust.

Now, it’s a little less clear as to what that table means. It’s probably customer, but are you sure?

There’s no need to abbreviate words just for the sake of it. Use the full words when naming tables. It’s one of the easier database table naming conventions to follow.

The exception to this is if you’ve reached Oracle’s 30 character limit for table names. Other database vendors may not have this restriction.

If you have, then you can play around with the name of the table to ensure it is less than or equal to 30 characters. You can do this by:

  • Removing vowels from words (e.g. customer_historic_invoice_billed (32 characters) could become customer_historic_invc_blld (27 characters))
  • 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 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, but it also makes maintenance worse.

What if you needed to change the table to a view of the same name? If you keep 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.


Avoid Concatenating Table Names in Many-to-Many Relationships If Possible

Let’s say you had a student table and a subject table as I’ve used in many of my SQL function examples.

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 learned 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!

9 thoughts on “Singular vs Plural and Other Database Table Naming Conventions”

  1. 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?

    1. 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.

      1. No I think it shows t hat it is more natural to use the plural for at collection. If you wish to use the singular then you should name your tables subject_enrolments_table and class_enrolments_table but otherwise just use the plural for all table names and the singular for a tuple or instantiantion. A class may (arguably) be best named in the singular as it represents an archetype but tables should definitely be plural as should the collections you would load the into. The variable holding an instantiation of the class should be named in the singular.
        In an ideal world of course, one should name tables xxx_table tuple xxx_tuple collection xxx_colle instances xxx_insta, but I do not know anyone who could be bothered with extra typing ;-D

        1. Thanks for the comment Alistair! I’ve seen arguments both sides (singular and plural) so as long as you can justify the reasoning, and it’s consistent, then it’s OK!
          Regarding adding “_table” to the end of the table name, I don’t think this is necessary. It takes up valuable characters and it’s also clear the object is a table without adding “_table”.
          Also “tuple” is not something I’ve heard since university days so I don’t think it’s a concept that’s well known and isn’t something that should go into a table name. Plus it’s extra typing as you said :)

  2. > A table is, therefore, a container. And a container is a single object. Whether it contains multiple rows or a single row does not impact its name, and therefore the name should be singular.

    By this logic arrays should be singular and noone names them like that.
    So there is some big flaw in reasoning…

    1. Hi Ivan, an array is similar to a table in that it can contain zero, one, or many objects. The naming of arrays is up to the development team and is likely less of an issue compared to tables, as tables are widely available. Hope that makes sense!

  3. I’ve tried both singular and plural over the years. Each is better in some ways. (I’m currently doing singular but not out of a strong preference.)

    I totally agree with stick with what your team uses, and in all cases be consistent.

    AppleBag is not a great example, IMHO. That would be like Customer_Table. I would have named the bag apples. That doesn’t imply that it has more than one apple. It just means a collection that stores apples. English is pretty weird. Zero apples is plural. Then switch to one apple, then two apples.

    As an app dev and a wanna-be dba, I usually try to be consistent across the two worlds. That means I use camelCase. Good points on switching to underscores. I’ll consider it next time I’m doing a green field.

    It’s impossible to be consistent across both worlds when it comes to the singular-plural chasm. Is a table more like a class name? Or more like an array? A table is truly both. For that reason, I don’t see the debate going away.

    In any case, you presented one side well, and you provided links to the other side, and you weren’t too zealous about the “Right Way”.

    1. Thanks David for your comments, you make some good points! It is hard to be consistent, and as you mentioned, a table can be considered a class name or an array or both!

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.