Relationship of tables with the same attributes

1

My system is for employee control.

Where we have the following types of people (or positions): agent, analyst, supervisor, coordinator and manager.

How I did it:

I have created a table for each type of these, plus a user table that contains the login and password to access the system.

The tables contain almost the same information, some will have fewer or more attributes. Ex: agent has more attributes than supervisor.

Problem:

All these tables have in relation to the "charge" table. 1 charge - N agents.

If I create an agent, and in the future it will be promoted to supervisor, I will only change its position, but it will remain in the "agent" table.

What can be done?

    
asked by anonymous 02.09.2017 / 22:30

1 answer

1

The table that "commands" there is that of employee, this is something concrete. If the person is an employee, that's where it should be registered.

The decision is only where to put the data that depends on the position that the employee is occupying at that moment.

The simplest solution is to place all the specific columns of all positions in this same employee table, and do not mind that several of them are always null. There will be a column indicating the position held and the application will know which columns are of interest to that position. I do not like this much, but it's more feasible than people usually imagine.

The solution that seems most conceptually correct is to have a set of auxiliary tables, one for each job with only the required specific columns. The employee table will also have a column indicating the position. Performance tends to be slightly smaller, but quite acceptable.

Note that these tables are not the job title (this describes the job itself, generally for everyone), it is one that lists the job data for each employee, so if you have 20 agents working in the company, it will have 20 lines.

So I understand you will keep the current tables by removing only the columns that are common to every employee and these columns will go to the new employee table that will always be related to the charges table.

    
02.09.2017 / 22:48