Suggestions for Proper Database/Table Design

  • Thread starter Thread starter ABW
  • Start date Start date
A

ABW

Greetings all:

I have an Access database that stores employee information that then is used
to create a departmental Organization Chart. The main table in the database
is an employee table. Entities for the employee table include FName, LName,
Address, Phone, etc..... as well as who the employee reports to (ManagerID).
My logic in the design of the table is that all managers must be employees
of the department and as such, each manager's Primary Key value is used as
the ManagerID for their downline.

My problem occurs when I try to query the employee table and retrieve the
manager [FName] & [LName]. I can only get the employee's name, not that of
the manager.

The only work-around I have found so far is to include the employee table in
the query twice (where the second instance of the table is named by the
system as employee_1). See the following SQL for an example:

SELECT tblEmployee.ID, [tblEmployee].[FirstName] & " " &
[tblEmployee].[LastName] AS EmployeeName, tblEmployee.ManagerID,
[tblEmployee_1].[FirstName] & " " & [tblEmployee_1].[LastName] AS
ManagerName
FROM tblEmployee INNER JOIN tblEmployee AS tblEmployee_1 ON
tblEmployee.ManagerID = tblEmployee_1.ID;

What is the consensus for the proper table/database design? Should I create
a "Manager" table? Should I continue to use this method? If I continue
this method, should the temporary employee_1 table be used in the database
relationship design form?

All help is greatly appreciated.
 
I think this method of using a self join is the appropriate solution. Since
most data entry is done in forms, you don't need to use the self join. You
can simply display the supervisor name in a combo box that is bound to the
ManagerID.
 
As a db designer for over 15 years your table design looks
correct and you also found the proper solution to generate
your organizational chart.
 
Back
Top