Change of Department

[ABC123]

I am looking at developing a database for employees. It will list an
employee and their relevant department and that department's city, so "Joe
Bloggs, Marketing, Ohio." For this employee, there will be some sort of
subform listing his fortnightly targets, ie "January - 42 new clients, $400
in sales etc..."

At some point, Joe Bloggs may move department and city and become "Joe
Bloggs, Sales, New York." Again though, I still want to list his fortnightly
targets.

I would want to find a way, so that when I run a report to see how "Ohio"
performed between 2002-today, Joe Bloggs targets are highlighted up until
the day he moved department. Likewise, when I run one for "Sales", it will
included Blogg's targets from the day he arrived at his new department 'til
today.

How should I organise my tables for this, so that it will allow employees
different departments, with targets in a subform relating to a specific
department and city at a certain time?

 

[Douglas J. Steele]

This is a situation for the classic many-to-many relationship. Because of
the time element, one employee can work in many different departments. As
well, a department will have many different employees working in it.

Have a table that holds the employee information (name, date of hire, that
sort of thing, with a primary key of EmployeeId) and a second table that
holds the department information (with a primary key of DepartmentId).
Introduce a third resolution table that resolves the many-to-many
relationship between the other two tables. This third table would require
something that points to the employee (EmployeeId), something that points to
the department (DepartmentId), and fields to indicate when the assignment
started and when it ended (set the end date to Null if they're still in that
job). The primary key of the third table would consist of the three fields
EmployeeId, DepartmentId and StartDtm.

 

[Neil Greenough]

Cheers for that Doug.

In what table should I put the 5 fornightly target columns?

 

[Douglas J. Steele]

You shouldn't have fortnightly columns: they should be rows in another
table. How that other table looks depends on whether the targets are
specific to the employee (in which case the PK of the new table would be
EmployeeId and TargetDtm) or to the department (in which case you'd probably
want EmployeeId, DepartmentId and TargetDtm as the PK)

It sounds to me as though you might benefit from a bit of study relating to
database normalization. Jeff Conrad has a bunch of good links in the
Database Design 101 section at
http://www.ltcomputerdesigns.com/JCReferences.html

 

[ABC123]

Cheers again Doug.

The targets would be specific to both the employee, the department and the
city. Ideally, I want to be able to run a report either for an individual, a
department or a city and get back results for the chosen criteria.

So, I should have an:-

- employee table (PKemployeeID, Name, Surname etc....)
- Department table (PKdeptID, DeptName)
- City table (PKcityID, CityName)

Could you just run me through the next few tables in more detail if you have
time? Could you list it like I have above?

Sorry to be a pain again, however I am not so clued up on many-to-many
relationships. I need to link employees to departments and cities, with this
being able to change at any time. Likewise, departments will fall in
specific cities as, for example, 'sales' is based in London, 'marketing' in
Madrid etc.....

Cheers once again