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

Question

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.

Answer 1

I typically use Pascal case and the entities are singular:

DoctorMain
DoctorProfile
DoctorPatient
etc...

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

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

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.

Answer 8

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.