×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Manager issue in DB

Manager issue in DB

Manager issue in DB

(OP)
Let’s say a have a TableA:
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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Manager issue in DB

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.

RE: Manager issue in DB

(OP)
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?)

ID    ManagerName  Status  SomeDate
123   Susie Brown   A
345   Bob Smith     A
675   Joe Water     Z      1/1/2000
 
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. smile

>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

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/tutori...

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

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

RE: Manager issue in DB

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


RE: Manager issue in DB

Quote (my emphasis)

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:
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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close