CODEWITHSUNDEEP
databasedatabase-design
MarioRicalde
MarioRicalde

Reputation: 9487

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

How do I name tables in a database?

Most developers tend to name the tables depending on the language that requires the database (JAVA, .NET, PHP, etc.). This isn't right.

I've been naming like:

doctorsMain
doctorsProfiles
doctorsPatients
patientsMain
patientsProfiles
patientsAntecedents

I'm concerned with:

Upvotes: 126

Views: 169315

Answers (15)

Kebechet
Kebechet

Reputation: 2407

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 NewtonsoftJson this problem is not present.

So I ended app with:

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

Columns: Id, Price, IsOnline.

Upvotes: 2

AmishJohn81
AmishJohn81

Reputation: 225

I'm no expert, but I have qualms about the Underscore approach mentioned elsewhere. I was taught to use underscore to indicate subschemas, and I find it to be very useful. I also typically use Pascal Casing because I think it's easier to read:

Schema.SubSchema_TableName

What I have learned from experience is that each DB creator does whatever the hell they want. So, do what you like, just be consistent, write up a standards document for your DB. Indoctrinate your subordinates, start a cult, nothing is real!

Upvotes: -1

user5182503
user5182503

Reputation:

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 databases will convert all characters to uppercase and some ones to lowercase. So, if you have myTable it will become either MYTABLE or mytable when you work with DB.

Upvotes: 3

Trace
Trace

Reputation: 18889

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.

Upvotes: 3

Chris Golledge
Chris Golledge

Reputation: 392

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.

Upvotes: 1

Justin Niessner
Justin Niessner

Reputation: 245479

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.

Upvotes: 33

David Oneill
David Oneill

Reputation: 13105

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

Upvotes: 277

rich remer
rich remer

Reputation: 3577

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

Upvotes: 22

Vlad Manuel Mureșan
Vlad Manuel Mureșan

Reputation: 780

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.

Upvotes: 8

KenFar
KenFar

Reputation: 1620

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.

Upvotes: 12

terR0Q
terR0Q

Reputation: 1367

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.

Upvotes: 16

Javier
Javier

Reputation: 62621

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.

Upvotes: 0

Mark Rendle
Mark Rendle

Reputation: 9414

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.

Upvotes: 6

Ray
Ray

Reputation: 21905

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.

Upvotes: 10

Kane
Kane

Reputation: 16812

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

Upvotes: 1

Related Questions