Manager issue in DB

[Andrzejek]

Let’s say a have a TableA:[pre]
PK Status SomeField ManagerID
1 A ABC 123
2 N XYZ 345
3 Z OPQ 675[/pre]

And I have a Manager table:[pre]
ID ManagerName
123 Susie Brown
345 Bob Smith
675 Joe Water[/pre]

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.

 

[SkipVought]

HI,

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Olaf Doschke]

Skip has a major point of keeing a date. If you also keep data history you can lookup the data as it was at the time the project was either inserted or last changed, whatever datetime reference you keep with your data,

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.

 

[Andrzejek]

Thank you guys.
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?)
[pre]
ID ManagerName Status SomeDate
123 Susie Brown A
345 Bob Smith A
675 Joe Water Z 1/1/2000
[/pre]
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.

 

[Olaf Doschke]

The concept for temporal validity is implemented in Oracle already for a longer time. Here's a descrption of how to use that:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/temporal/temporal.html

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.

 

[Darrylles]

I never delete data (such as 'managers') - I always flag them as 'inactive'.
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

 

[johnherman]

Darrylles's suggestion is a good one. For possible referential integrity issues, as well as the ability to recreate reports or extracts using data "as of" the original report data, it is better to "deactivate" the record. This is especially true for Data Warehouse and Analytical Reporting, but is a good practice in any case.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[ChrisHunt]

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.

So what you're saying is that there's a many-to-many relationship between projects (if that's what's in tableA) and managers - a manager might have many projects, and a project could have many managers (over time, at least).

In that case, you need to split the relationship out into another table:
[pre]
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
[/pre]





-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Olaf Doschke]

Doing that, you'd still want a retirement flag or inactive status of managers, to never assign them to new projects. Whatever suits better, also depends on whether managers only assign themselves (and obviously don't do so after retirement) or whether they are assigned from one level higher management.

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.