Relationship of tables with the same attributes

Asked

Viewed 282 times

1

My system is for employee control.

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

Like I did:

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

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 table "position". 1 position - N agents.

If I create an agent, and in the future he is promoted to supervisor, I will only change his position, but he will remain in the table "agent".

What can be done?

mysql database modeling relational-model

1 answer

1


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

The decision is only where to place the data that depend on the position that the employee is occupying at that time.

The simplest solution is to put all the columns specific to all positions in this same employee table and 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 matter for that position. I don’t like it much, but it’s more viable than people usually imagine.

The solution that seems more conceptually correct is to have a set of auxiliary tables, one for each position with only the specific columns required. The official table will also have a column indicating the position. The performance tends to be slightly lower but well acceptable.

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

From what I understand will keep the current tables 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 positions.

  • But what would be the relationships between Manager > Coordinator > Supervisor > Analyst/Agent.

  • I see no difficulty in that, but anyway this is not in the question.

  • I’ve been looking for some information about a relationship. Basically I would have the function table, which would have inside it the attributes like: id(int, notnull) PK name(varchar, notnull) cargoId(int, notnull) - FK supervisorID(int, null)-FK (WHERE THESE FOLLOWING FK’s are the primary key of the working table itself) co-ordinator ID (int, null)- FK gerenteID (int, null)- FK So if the employee himself is a "supervisor", he will get the null field. Correct ?

  • Probably..

Browser other questions tagged mysql database modeling relational-model

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