For database abstraction, should I use uppercase or lowercase column names?

Question

I am developing an application that will need to connect to any database. I am using Laravel for this.

Laravel’s ORM maps the database fields and provides my class as an attribute automatically, but if the database is capitalized, Laravel will create the property in uppercase as well:

$User->name; => $User->NAME;

To avoid problems with different databases, which default should I use to name not only the columns, but also the tables in my database.

Important: I should not depend on database settings, as this is a setting that the application cannot depend on.

Answer 1

Use Upper Camel Case (or Capitalizedwords) for table names and Lower Camel Case for field name.

Other approaches using underline may please you, but adopt a standard and publicize it. See more about Camelcase.

Answer 2

Everything depends on the taste of the team and whether there is already a standard in the company. As there is no 100% standard I will give the tips of the rules I follow, after a few years fiddling with databases, and mainly by the fact that you say you want to program for Atms.

Come on, first a detail, Oracle doesn’t have Lower Case, that’s all Upper Case. So leaving this way I use the rules for all banks:

• Tables - All in Upper Case
• Uso Camelcase
• Do not use composite PK
• Table names all in Portuguese or English, no mixing
• Standard for PK, FK, CK, UK and related use:
  • Name of the table_XXyy, where XX is the type and yy is a sequential number. So it is much easier to find the problem, and also if you search in the database constraints they come in order and organized
• Always use single PK, even in tables N x N. This allows to greatly simplify the FK and also the use of ORM. In addition the joins are much easier
• In query, I always use the commands select, from and the like in Lower case, tables in Upper Case and fields in Camelcase, getting very visual and easy to understand the querys

With these rules you can do select in this way:

select
  UsuarioID, Nome, Login
from
  USUARIO
order by
  Login

Example with Join

select
  a.UsuarioID, a.Nome, a.Login, a.EmpresaID
from
  USUARIO a
  inner join EMPRESA b on b.EmpresaID = a.EmpresaID
order by
  a.Login

I hope I’ve helped.