Table Naming: Underscore vs Camelcase? namespaces? Singular vs Plural?

I've been reading a couple of questions/answers on StackOverflow trying to find the 'best', or should I say must accepted way, to name tables on a Database.

Most of the developers tend to name the tables depending on the language that requires the database (JAVA, .NET, PHP, etc). However I just feel this isn't right.

The way I've been naming tables till now is doing something like:

doctorsMain
doctorsProfiles
doctorsPatients
patientsMain
patientsProfiles
patientsAntecedents 

The things I'm concerned are:

• Legibility
• Quick identifying of the module the table is from (doctors||patients)
• Easy to understand, to prevent confusions.

I would like to read any opinions regarding naming conventions. Thank you.

Being consistent is far more important than what particular scheme you use.

I typically use PascalCase and the entities are singular:

DoctorMain
DoctorProfile
DoctorPatient

It mimics the naming conventions for classes in my application keeping everything pretty neat, clean, consistent, and easy to understand for everybody.

Case insensitive nature of SQL supports Underscores_Scheme. Modern software however supports any kind of naming scheme. However sometimes some nasty bugs, errors or human factor can lead to UPPERCASINGEVERYTHING so that those, who selected both Pascal_Case and Underscore_Case scheme live with all their nerves in good place.

Since the question is not specific to a particular platform or DB engine, I must say for maximum portability, you should always use lowercase table names.

/[a-z_][a-z0-9_]*/ is really the only pattern of names that seamlessly translates between different platforms. Lowercase alpha-numeric+underscore will always work consistently.

As mentioned elsewhere, relation (table) names should be singular: http://www.teamten.com/lawrence/programming/use-singular-nouns-for-database-table-names.html

An aggregation of most of the above:

• don't rely on case in the database
• don't consider the case or separator part of the name - just the words
• do use whatever separator or case is the standard for your language

Then you can easily translate (even automatically) names between environments.

But I'd add another consideration: you may find that there are other factors when you move from a class in your app to a table in your database: the database object has views, triggers, stored procs, indexes, constraints, etc - that also need names. So for example, you may find yourself only accessing tables via views that are typically just a simple "select * from foo". These may be identified as the table name with just a suffix of '_v' or you could put them in a different schema. The purpose for such a simple abstraction layer is that it can be expanded when necessary to allow changes in one environment to avoid impacting the other. This wouldn't break the above naming suggestions - just a few more things to account for.

I use underscores. I did an Oracle project some years ago, and it seemed that Oracle forced all my object names to upper case, which kind of blows any casing scheme. I am not really an Oracle guy, so maybe there was a way around this that I wasn't aware of, but it made me use underscores and I have never gone back.

I tend to agree with the people who say it depends on the conventions of language you're using (e.g. PascalCase for C# and snake_case for Ruby).

Never camelCase, though.

After reading a lot of other opinions I think it's very important to use the naming conventions of the language, consistency is more important than naming conventions only if you're (and will be) the only developer of the application. If you want readability (which is of huge importance) you better use the naming conventions for each language. In MySQL for example, I don't suggest using CamelCase since not all platforms are case sensitive. So here underscore goes better.

These are my five cents. I came to conclusion that if DBs from different vendors are used for one project there are two best ways:

1. Use underscores.
2. Use camel case with quotes.

The reason is that some database will convert all characters to uppercase and some to lowercase. So, if you have myTable it will become MYTABLE or mytable when you will work with DB.

Naming conventions exist within the scope of a language, and different languages have different naming conventions.

SQL is case-insensitive by default; so, snake_case is a widely used convention. SQL also supports delimited identifiers; so, mixed case in an option, like camelCase (Java, where fields == columns) or PascalCase (C#, where tables == classes and columns == fields). If your DB engine can't support the SQL standard, that's its problem. You can decide to live with that or choose another engine. (And why C# just had to be different is a point of aggravation for those of us who code in both.)

If you intend to ever only use one language in your services and applications, use the conventions of that language at all layers. Else, use the most widely used conventions of the language in the domain where that language is used.

Unfortunately there is no "best" answer to this question. As @David stated consistency is far more important than the naming convention.

there's wide variability on how to separate words, so there you'll have to pick whatever you like better; but at the same time, it seems there's near consensus that the table name should be singular.

C# approach

Singular/Plural

• singular if your record in row contains just 1 value.
• If it is array then go for plural. It would make perfect sense also when you foreach such element. E.g. your array column contains MostVisitedLocations: London, NewYork, Bratislava

then:

foreach(var mostVisitedLocation in MostVisitedLocations){
    //go through each array element
}

Casing

PascalCase for table names and camelCase for columns made the best sense to me. But in my case in .NET 5 when I had json objects saved in dbs with json object names in camelCase, System.Text.Json wasnt able to deserialise it to object. Because your model has to be public and public properties are PascalCase. So mapping table columns(camelCase) and json object names(camelCase) to these properties can result in error(because mapping is case sensitive). Btw with NeftonsoftJson this problem is not present.

So I ended app with:

Tables: App.Admin, App.Pricing, UserData.Account

Columns: Id, Price, IsOnline.

2 suggestions based on use cases:

1. Singular table names.

Although I used to believe in pluralizing table names once, I found in practise that there is little to no benefit to it other than the human mind to think in terms of tables as collections.
When singularising the table names, you can silently add -table to the singular table name in your head, and then it all makes sense again.

SELECT username FROM UserTable 

Sounds more natural than

SELECT username FROM UsersTable 

But post-fixing every table with

is just a waste.

The actual practical argumentation for singularising table names:

What is the plural of person: persons or people?
This is still ok.
But how do you like a table with postfix -status? Statuses?
That sucks, sorry. It is easy to inadvertently make a human mistake by singularizing the status table, but pluralizing the other tables.

2. PascalCasing + Underscore convention.

Given table User, Role and a many-to-many table User_Role.
Considering underscore cased user_role is dubious when all table names are using underscore per default. Is user_role a table that contains user roles? In this case it is not, it is a join table.

When deciding on table name conventions I think it is useful to let go of personal preference and take into account the real practical considerations of real life problems in order to minimize dubious situations to occur.
As the many answers and opinions have indicated, whatever your personal opinion is, different people think differently, and you will not be the only person working on the database despite being the one who sets it up (unless you do, in which case you're only helping yourself).
Therefore it is useful to have practical argumentation (practical in the sense of, does it help my future co-workers to avoid dubious situations) when your past decision is being questioned.