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

user_140901 0
Viewed 118708

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.

database-design database
14 Answers

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.

user_2174030

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.

user_25068150

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.

user_61583410

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.

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

user_9174670
Related