a couple of newbie questions

[merlin777]

Please excuse the basic nature of these questions. I've tried to google them and had no luck.

I understand that when you set an ID field as a primary key e.g. EmployeeID, it should be data type of autonumber. When that field appears in another table and you create a relationship with it, should the datatype be a number type?

Also, I've just seen a tutorial on relationships where one of the tables contains:

employeeID
employeename
startdate
manager

Shouldn't 'manager' be a separate table as one manager might manage many employees and if that manager left then his replacement would need to be updated in every employee record? Or have I misunderstood?

 

[sneuberg]

Please excuse the basic nature of these questions. I've tried to google them and had no luck.

I understand that when you set an ID field as a primary key e.g. EmployeeID, it should be data type of autonumber. When that field appears in another table and you create a relationship with it, should the datatype be a number type?

Yes, Field Size: Long Integer which is normally the default field size for a number.

Also, I've just seen a tutorial on relationships where one of the tables contains:

employeeID
employeename
startdate
manager

Shouldn't 'manager' be a separate table as one manager might manage many employees and if that manager left then his replacement would need to be updated in every employee record? Or have I misunderstood?

A manager is an employee too so you could just put them in the employee table, perhaps with a Yes/No to indicate their status. The field for the manager of an employee could be just a number, foreign key of employeeID. If you do it this way then when you add the relationship you will need to show a second table of employee in relationships to form this self reference.

 

[The_Doc_Man]

Shouldn't 'manager' be a separate table as one manager might manage many employees and if that manager left then his replacement would need to be updated in every employee record? Or have I misunderstood?

There are a couple of ways to handle this, but you are correct that a change of manager might require a more complex update. Which leads to one of the "Old Programmer's Rules" - If you can't do it on paper, you can't do it in Access. What that means in practice is that you must decide ahead of time what actions you wish to perform and under what conditions.

There are two basic kinds of managerial structures: simple hierarchical or full matrix. (There is also a hybrid of that, but let's just look at the two extremes.)

In a true hierarchy, your employees don't work for a manager, they work for a department and that department is headed up by a manager. So you would have a department field for each employee and a slot in a department table for the person who is currently in charge of the department. In this hierarchy, your employees only work for one department and answer to the manager.

In a full matrix, you have to decide for whom the employees work if not in a department structure. In this case, perhaps a project structure? In this case, you need a junction table to show all the projects for which an employee can work and the project header needs to have a slot for the person managing the project. Your employees work for different people depending on which project they are supporting at a given moment.

A variant of this is that the employees work for a department based on their special abilities but get assignments from the project leaders. That nightmare, I leave to you!

Summary: In most cases, you need to have some sort of intermediate structure that is used to define the reporting structure and the manager in question merely occupies a slot in the descriptive structure that describes the flow of responsibility. So for the extreme cases, a change of manager just means an update of one slot. Only in the hybrid case does this get nasty.

 

[informer]

Hi merlin777

Read this wiki topic:httpshttps://en.wikipedia.org/wiki/Database_normalization#Normal_forms


Good reading