Fields with the same name in different tables

Asked

Viewed 911 times

1

Fields with the same name in different tables may cause conflict at the time of normalizar or make some inner join?

Examples

Supplier Table

  1. Field: name
  2. Field: surname
  3. Field: email

Product Table

  1. Field: name
  2. Field: delivery date
  3. Field: Amount

This is a very 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, That’s one way to escape?

According to style Guides of SQL.

Tables

Use a collective or less ideally plural name. By example, (in order of preference) staff and employees.

Do not use prefixes with tbl or any other descriptive prefix or notation hungarian.

Never give a table the same name as one of your columns and vice versa.

> Avoid when possible concatenating two table names to create a relationship table name.

In place of mechanics_de_car, prefer services.


Columns

Always use names in the singular.

When possible, avoid using only id as the primary identifier of 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 own).

So the part that says:

Avoid when possible concatenating two table names to create the name of a relationship table.

Would be worth the same to the fields?

mysql sql normalization

  • Yes can cause conflict, but is easily solvable, just prefix the field name with the table name. I do not see how this can have any impact on the standardisation process.

  • type, following the example given: the tabela Fornecedor would be: fornecedor_nome, fornecedor_sobrenome, fornecedor_email, and the tabela Produto: produto_nome, produto_data_de_entrega, produto_quantidade that way?

  • No, I meant tableName.fieldName, every time there is some kind of conflict with the table field names involved in the command. Not in the table definition.

2 answers

2


It can’t cause conflict, if you do everything right, which is what everybody does, right? When it comes to using the names on a query must have a complete 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 your name will be p.nome, which will be different from f.nome. If you don’t like the abbreviation use the full surname (produto.nome and fornecedor.nome), this is sufficient to disambiguate the names and avoid conflicts.

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 key, so if the foreign key is the id from a supplier, the name of this hill will be fornecedor_id. There are people who think that in this case the id on the table fornecedor it will also be to be completely qualified, even being redundant, so that all columns representing the same thing are equal, but there are many people who disagree with this.

Give simple semantic names to the 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, needless to say, but in fact there are those who do not agree with it.

The question quotes such a style guide SQL, seem good general recommendations (not rules to follow blindly), but has some bad points. That is the work of someone who should be respected, but it is not someone who has authority over the subject, it was something you found on the Internet, it does not mean that everything there is correct or that it is unanimous, 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.

That’s good, but it’s not an absolute truth.

Always use names in the singular

Usually yes, but there are cases where this should not be done.

Always use low cash, except where capitalization makes sense (as in first names)

Nonsense. I just like the person.

Avoid when possible concatenating two table names to create a relationship table name.

Avoid yes, never do no.

Would be worth the same to the fields?

In general, yes, just do not take as a fixed rule, as mentioned above.

What you call field is actually called column, field is an informality that people understand.

  • The question I will ask now departs a little from the scope of the main question cited above, but in questions to tables " [...] but if I have around 50 tables or more [...]", the example Tabela Curso and Tabela Evento and both have another table called Categoria, but as we can not put the name of two equal tables, then we should call the second Table of Categoria asCategoria_Curso / Categoria_Evento (breaking it in two), or unifying this single table as Categoria and make a relationship between the 3 tables?

  • So ask her a new question, but think about whether she’s coherent, the way she did it doesn’t make much sense. Anyway, I’ve already said that this is not an absolute truth, it depends on what you’re doing.

  • Calling the column field is something I know is not right but invariably forget.

1

No, because the name of the column can be added the name of the table to distinguish it.

When naming a column the concern should be to identify it/describe its content in the best possible way.

Names like provider name and product name are not necessary and should be avoided.

When a spine needs to be identified/distorted use supplier name. and product name.

The exception may be in fields representing foreign keys where there is no better way to distinguish them.

Browser other questions tagged mysql sql normalization

You are not signed in. Login or sign up in order to post.