Normalization of MySQL tables

Before talking about normalization, a few things to note are:

• In name normalization do not be afraid to write enough, it's much better something like: external_functional instead of id_fun_ex . Plus easy to understand will not make your database heavier.

• Use the most appropriate data types, as follows:

  • IDs: use an integer type, possibly the type SMALLINT is more than enough, but you can choose another one that fits as well (Link: Integer Types ).
  • Dates: Use types Date because you want to save only the date so you can use functions to Selects easily.
  • Text Fields: In text fields that the size can be variable (as in func_name ) choose to use varchar instead of char because char stores all space defined and varchar as needed (Link: Varchar and Char )

• I also recommend that in the table tb_funcionario you enter a field for RG and / or CPF so that it is set to unique key to ensure that there is no duplicate employee. Although it is rare, two people can have exactly the same name.

Normalization issues - > First Normal Form:

p>
• li>
• In the id_func , id_depto fields of the tb_funcao table set to Primary Keys and make the relationship correct.

For more information I recommend: Standardization in DB

A suggestion using what you posted:

tb_funcionario (id_func, id_funcao, id_depto, nome_func, data_nasc, end_fun, dt_admissao, dt_demissao)

tb_funcao (id_func,id_depto,nome_funcao)

tb_depto (id_depto, nome_depto)

It would look like this:

person(
  id unique,
  datebirth,
  person_name,
  person_surname
)

person_addresses(
  id unique,
  person_id,
  country_code,
  postal_code,
  province_code,
  city,
  address
  address_add
)

person_roles(
  id unique,
  role_id,
  person_id
  date_ini (data de início ou admissão)
  date_end (data de término ou demissão)
)

roles(
  id unique
  title
)

department(
  id unique
  title
)

department_roles(
  id unique
  role_id
)

Below, I'll explain just a few reasons for the above modeling, without covering the data types (integer, var, date, etc.).

The first step is to standardize the nomenclature and, for this, it is recommended to follow an internationalized standard using the English language, since it is the most widespread in the world.

As discussed by Giovani, avoid abbreviating column names. Describe them clearly.

I removed redundancies from table names and column names, for example, using "tb_" for tables is unnecessary since they are already tables. The end_fun column is somewhat confusing, remembering something like "end function", "end of function?" .. But on second thought, it seems to be "employee address."

If it is an address, then how do I do it if the employee has more than one address? At that point the modeling is plastered. You will have future problems and lots of work to remodel to multiple addresses. Note that in the table "person_addresses" has a column for country code. So many people think, "But the system will only be in my country, why do I need a country code?" Once again we fall into the question of globalization. Think of an immigrant official, a Bolivian, an Argentine, a Colombian, and this is very common in Brazil. So the company needs to register this employee but he does not have a fixed address in Brazil but obviously has a fixed address in his native country. If the system does not allow international addresses, then you have a system limitation.

Why did I change tb_functional to "person"? So that the structure is useful for other situations and not be tied only to the registration of "employees". This same table can be used for clients, for example, and this is also the reason to remove other columns such as id_depto, id_funcao, etc, and organize them into relationship tables.

In "person_roles" is where you make the relationships between the id of the person and the id of the function. This table also includes start date / admission and termination / dismissal. The dates could also be on one table, but I find it unnecessary. There may be cases where the same person enters and leaves a position in a department and returns to the same position and department. Therefore, it should allow multiple entries for the same person.

Choose something more generic because the date of termination in a position does not always mean dismissal. The person / employee can be transferred or promoted or simply finished some stage, died, fell ill, pregnancy, retired, anyway. The interesting thing would be to create one more table to make the relationship of reasons of the end, if you want to have more control.

Note also that the table "person_roles" allows the same person to have several positions. And this is very common in many companies to have people working in 2 or more departments performing functions sometimes equal or totally different.

Citing an example, a polyglot executive. It is the administrative department whose main function is to trader. During the time you are not traveling around the world, you stay in the office as a translator or whatever. This is a simple example of a multi-function employee.

On the proposed modeling, how to know which department (s) a particular employee belongs to? Through the relationship in the table "department_roles" you get the information.

Notice that the tables are independent of each other. The important thing is the relationship tables. This is what makes the system flexible and reusable.

* Nomenclatures, just like the proposed modeling is merely a suggestion. It does not mean it should be exactly as presented. Just try to understand how to organize tables and create relationships more dynamically. Always think about reusing the structure and flexibility of the system.