I can tell you what I use. I have not seen any rule that indicates that it has to be this way... or another...
Of course always in English .
Table names always CAPITAL LETTERS and in the plural .
Field names always lowercase and singular .
The reason for the uppercase and lowercase letters is to quickly identify in an SQL which is the table and which is the referenced field . In the select with multiple JOINs I think it is clear that it is not a bad idea to use it like this.
The singular or plural motive . Do the tables store multiple records of or ? It is better for me Users
and does each record have or ? Well, I think it's more appropriate to say name . Unless the field is a foreign key , then it is appropriate to use small identifiers that visually help you identify that field that has something more than simple data. For example the table will have a field (not neither , nor strange things). Singular reference to the foreign table and indicates that you store the ID there,UserUsersnamenamesCOMMENTSuser_idusersid_usuarioUSERSuser_id
In the intermediate tables, for example any one to many relationships , such as usuarios( USERS) and all uploaded photos ( PHOTOS). I respect UPPERCASE and use the symbol _to concatenate USERS_PHOTOS.
In some cases, in the names of the tables or fields, a prefix is usually used to identify the project or service that the information comes from or where that information will be used. In the case of tables I don't like to use prefixes, but in fields I do ( siebel_id, joomla_id, twitter_token...)
However, there are no set rules about this. That's why I've come across things like table Población, or related tables like Población?Ciudador better yet Códigos postales. I think that in order not to hurt anyone, it is better to avoid accents and for "rare" concatenation characters such as white space, ?, -, +, ...
This is a relative issue since, as in programming languages, the answer is not unique and everyone could prefer one convention or another.
In my case, regardless of the database engine, I use the table names in the plural , and the column names in the singular , in both cases using PascalCase . If it is my choice, the names in English and use only one field for the primary key which is always called Idnumeric or UUID (depending on whether the table uses logical deletion or is very high concurrency).
Foreign keys carry the name of the table followed by Id, for when they are a single relationship (Example DetallesFacturaand Facturas). If it has more than one relation (Example Facturasand UsuariosUsuarioCreaId, UsuarioAnulaId), I put the name of the table, one or two words to denote the difference in the field with another of the same type of relation.
In SqlServer most of the objects accept names up to 128 so I have never had problems with these features.
In the case of Oracle , things change a bit, the maximum allowed is 30 characters, and although names can be used in lowercase, the custom is that everything is in uppercase, so we separate the words with _and use acronyms or abbreviations in many-to-many relationship tables, or with compound names.
Another consideration, and probably the reason why SQLServer uses this way, is that I usually use an ORM for database access, so the generated code maintains the style that we use in C#.
As you can see, it is a question of style. Here you will find a little more detail of Naming Conventions used in programming languages. Also on SO this question that includes very interesting explanations.
I can tell you what I use. I have not seen any rule that indicates that it has to be this way... or another...
The reason for the uppercase and lowercase letters is to quickly identify in an SQL which is the table and which is the referenced field . In the select with multiple JOINs I think it is clear that it is not a bad idea to use it like this.
The singular or plural motive . Do the tables store multiple records of or ? It is better for me Users and does each record have or ? Well, I think it's more appropriate to say name . Unless the field is a foreign key , then it is appropriate to use small identifiers that visually help you identify that field that has something more than simple data. For example the table will have a field (not neither , nor strange things). Singular reference to the foreign table and indicates that you store the ID there,
User
Users
name
names
COMMENTS
user_id
users
id_usuario
USERS
user_id
In the intermediate tables, for example any one to many relationships , such as
usuarios
(USERS
) and all uploaded photos (PHOTOS
). I respect UPPERCASE and use the symbol_
to concatenateUSERS_PHOTOS
.In some cases, in the names of the tables or fields, a prefix is usually used to identify the project or service that the information comes from or where that information will be used. In the case of tables I don't like to use prefixes, but in fields I do (
siebel_id
,joomla_id
,twitter_token
...)However, there are no set rules about this. That's why I've come across things like table
Población
, or related tables likePoblación?Ciudad
or better yetCódigos postales
. I think that in order not to hurt anyone, it is better to avoid accents and for "rare" concatenation characters such as white space,?
,-
,+
, ...For me one of the teachers in Sql Server is Phinal Dave, he has a detailed document of these standards is http://blog.sqlauthority.com/2007/06/06/sql-server-database-coding-standards-and-guidelines -complete-list-download/
This is a relative issue since, as in programming languages, the answer is not unique and everyone could prefer one convention or another.
In my case, regardless of the database engine, I use the table names in the plural , and the column names in the singular , in both cases using PascalCase . If it is my choice, the names in English and use only one field for the primary key which is always called
Id
numeric or UUID (depending on whether the table uses logical deletion or is very high concurrency).Foreign keys carry the name of the table followed by Id, for when they are a single relationship (Example
DetallesFactura
andFacturas
). If it has more than one relation (ExampleFacturas
andUsuarios
UsuarioCreaId
,UsuarioAnulaId
), I put the name of the table, one or two words to denote the difference in the field with another of the same type of relation.In SqlServer most of the objects accept names up to 128 so I have never had problems with these features.
In the case of Oracle , things change a bit, the maximum allowed is 30 characters, and although names can be used in lowercase, the custom is that everything is in uppercase, so we separate the words with
_
and use acronyms or abbreviations in many-to-many relationship tables, or with compound names.Another consideration, and probably the reason why SQLServer uses this way, is that I usually use an ORM for database access, so the generated code maintains the style that we use in
C#
.As you can see, it is a question of style. Here you will find a little more detail of Naming Conventions used in programming languages. Also on SO this question that includes very interesting explanations.