Fields with same name in different tables
Fields with the same name in different tables can cause conflict at the time of normalizar or do some inner join?
Examples
Supplier Table
- field: name
- field: last name
- field: email
Product Table
- field: name
- field: delivery_date
- Field: Quantity
This is a quite simple example with only 2 tables, but if I have around 50 tables or more, within a given project, what is the effective solution? I've read about some topics saying to put nome_da_tabela_nome_do_campo, is this a way to escape?
According to SQL style guides .
Tables
Use a collective noun or less ideally, plurals. By example, (in order of preference) staff and employees.
No use prefixes with tbl or any other descriptive prefix or notation Hungarian.
Never Give A table the same name as one of its columns and vice versa.
> avoid when possible concatenating Two table names to create the name of a relationship table.
Instead of car mechanics, prefer service.
Columns
Always use names in the natural.
When possible, avoid using only id as the primary identifier of the table.
Do not add a column as the same table name and vice versa.
Always use box low, except where capitalization makes sense (as in names proper).
Then the part that says:
Avoid When possible concatenating Two table names to create the name of a relationship table.
Valeria a same thing for the fields?
2 answers
You can't cause conflict, if you do everything right, which is what everyone does, right? At the time of using the names in a query must have a full qualification, i.e. the column name and the table name (usually this name is used with an alias, so produto will probably be used as p), then its name will be p.nome, which will be different from f.nome. If you do not like the abbreviation use the full surname (produto.nome and fornecedor.nome), this is enough to disambiguate the names and avoid conflict.
Putting the table name in the column name is generally redundant and unnecessary. There are only a few cases where the name is descriptive. For example if you have a foreign key in a column, then you will not only use its name, you will use a qualified one, so if the foreign key is a vendor's id, the name of this hill will be fornecedor_id. There are people who think that in this case the id in the table fornecedor will also be completely qualified, even being redundant, so that all columns representing the same thing are the same, but there are a lot of people who disagree with this.
Give Simple and semantic names for columns, avoid redundancies and complications, so do not put unnecessary information, so the names you used are suitable. Some will say that data_da_ entrega already has redundancy, entrega is a date, no need to say, but in fact there are those who do not agree with this.
The question cites one such of style guide from SQL, seem good general recommendations (not rules to follow blindly), but it has some bad points. That is the work of someone who should be respected, but it is not someone who has authority on the subject, it was something that found on the internet, it does not mean that everything there is correct or that it is unanimity, in fact looking at all elements I disagree with several and some seem quite wrong, regardless of my taste.
Never give a table the same name as one of your columns and vice versa.
This is good, but it is not an absolute truth.
Always use names in the singular
Usually yes, but there are cases that this should not be done.
Always use low box, except where capitalization makes sense (as in proper names)
Nonsense. I just like the person.
Avoid when possible concatenating Two table names to create the name of a table relationship.
Avoid yes, never do no.
Would it be the same for the fields?
In general, yes, just do not take as a fixed rule, as quoted above.
What you call a field is actually called a column, field is an informality that people understand.
No, because the name of the table can be added to the column name to distinguish it.
When naming a column the concern should be to identify it/describe its contents as best as possible.
Names such as provider_name and product_name are not required and should be avoided.
When it is necessary to identify/distighten a column use provider.name and product.name
The exception may be in fields that they represent foreign keys, when there is no better way to distinguish them.