Manager issue in DB
Manager issue in DB
(OP)
Let’s say a have a TableA:
And I have a Manager table:
All is nice and works OK.
But here is the situation: Managers move from place to place, department to department, they retire, quit, etc. If I have an on-going project in TableA (Status of Active, New, etc.), I can just keep the Manager’s ID and refer to Manager’s table. But when the project (record in TableA) is done and marked as Archive (Status: Z), I want to keep the information who was the Manager for that Project even if this person is no longer with the Company or moved to be the Manager some place else, or was replaced with another Manager.
Do I keep another field in TableA with Manager’s Name? That way I would copy info from Manager’s table, not the best solution.
What do you do in this situation?
PK Status SomeField ManagerID 1 A ABC 123 2 N XYZ 345 3 Z OPQ 675
And I have a Manager table:
ID ManagerName 123 Susie Brown 345 Bob Smith 675 Joe Water
All is nice and works OK.
But here is the situation: Managers move from place to place, department to department, they retire, quit, etc. If I have an on-going project in TableA (Status of Active, New, etc.), I can just keep the Manager’s ID and refer to Manager’s table. But when the project (record in TableA) is done and marked as Archive (Status: Z), I want to keep the information who was the Manager for that Project even if this person is no longer with the Company or moved to be the Manager some place else, or was replaced with another Manager.
Do I keep another field in TableA with Manager’s Name? That way I would copy info from Manager’s table, not the best solution.
What do you do in this situation?
Have fun.
---- Andy
There is a great need for a sarcasm font.
RE: Manager issue in DB
You may need data and status.
There's also the issue of title. So if you refer to the presence president, the date of that reference is important.
So are you referring to the person holding that title at a date in time or are you just referring to a specific person?
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Manager issue in DB
SQL2016 has a nice set of features for that demand, too, called Temporal Tables, just in case you use MSSQL.
In the simplest case you keep data in the manager table and add a status field there, eg "Active" or "Retired" or whatever as bit being true or false. This status then has to influence the list of persons you can assign to new/current projects, obviously, and all "historical" Managers remain as reference.
Bye, Olaf.
RE: Manager issue in DB
The DB is Oracle.
>You may need data and status
So if Joe is no longer 'available' Manager as of 1/1/2000, I have something like this: (right?)
So I can keep all Managers that are 'used' in TableA, and no Manager is ever gone from any tables - they just change Status. Makes sense.
>are you referring to the person holding that title at a date in time or are you just referring to a specific person?
I am just referring to a specific person. In this case I may not need a Date in Manager's table...
Have fun.
---- Andy
There is a great need for a sarcasm font.
RE: Manager issue in DB
If the manager active/nonactive status is suffient for your case, as any referenced manager was manager at the time the project was active and the time of retirement or any other exit reason is of no interest a status of the manager table is ok.
On the other hand having an extra table just for managers is a design I'd at least expand. Being a manager is a certain attribute of a person, persons almost any time play a major role in any database and almost always deserve a separate table. There is good reason for a specific manager table with a primary managerid separate from a personid to be able to have a managerid in other tables only allowing managers as referenced persons and still have normal referential integrity foreign key constraints without any extra rule for which subset of persons are allowed to reference. But still the person table will be the one to list all involved persons and sepcial role persons may either just have attributes indicating their position or scuh a specific table mainly pointing back to a personid and thereby defining the subset of such persons.
Bye, Olaf.
RE: Manager issue in DB
They ALWAYS exist, their keys always exist (they must - they are legacy data that may be required in future).
The key in tableA will contain manager 'A's key whilst it's ongoing.
When it is finished, that managers key is stored in there forever (and that record is locked / untouched from thereon).
When that manager 'leaves' - that manager still exists in tblManager (just flagged as inactive).
ATB,
Darrylle
RE: Manager issue in DB
==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity
RE: Manager issue in DB
In that case, you need to split the relationship out into another table:
TableA: PK Status SomeField 1 A ABC 2 N XYZ 3 Z OPQ Project_Manager Project_Id Manager_Id Start_Date End_Date 1 123 2010-01-01 2 123 2012-10-02 2013-12-12 2 345 2013-12-13 3 675 2014-03-14 Manager: ID ManagerName 123 Susie Brown 345 Bob Smith 675 Joe Water
-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd
RE: Manager issue in DB
But yes, a Project_MAnager table with start and end date of this relation is also enabling to store change of manager over time, whereas a project.managerid can only denote the most probably last manager handling a project. Another reason for temporal validity (Oracle) or Temporal Table (new MSSQL 2016 feature).
Bye, Olaf.