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?