Relationships Question

[Shep]

I have a basic design question.

In an Order table, I have a Writer field and a Tech field.
Writer and Tech can be the same person.

The Writer field in the Order table relates to the EmployeeID in an Employee table.

Tech should relate to the same ID number but of course I cannot establish that relationship directly.

How do I construct the necessary relationship?

 

[charityg]

Add another Employees table to your relationship grid, It will probably automatically be aliased as Employees_1. Then create your relationship with Tech and Employees_1

 

[Travis]

Are you saying:

1. The Writer is an Employee.

2. The Tech Can be an Employee but not always.


If item 2 is true, do you have a seperate Tech Table? If you do then add the EmployeeID field to the table and update it with the employee ID of Tech's that are Employees. Then make a relationship between the Tech table and the Employee Table (This join would be a LEFT Join as the Tech may not be an Employee). Make another relationship between the Order table and the Tech Table.

Order Table Tech Table Employee Table
-------------- -------------- --------------------
TechID -> TechID
EmployeeID -> EmployeeID

 

[Shep]

That's what our vendor did with the product we are currently using - but their product is rife with design flaws. So, I naturally suspect any technique they've used.

I am worried that this technique breaks basic relational design rules, in which I have no formal training.

At the least, I worry that it is a sloppy way to get around a problem; that their might be a 'correct' way to accomplish this.

 

[Shep]

Travis,

The Writer is always an Employee.
The Tech is always an Employee.

Writer and Tech may or may not be the same Employee.

I do have a separate Tech table at the moment - for testing, but cannot relate Tech to Employee table for (hopefully) obvious reasons.

 

[charityg]

It's not obvious to me why you can't link the tech to the employees table. Did you attempt the suggestion I gave? It is indeed possible (and correct) to add a table more than once in relationships design. Just add the Employees table again and create the relationship between the Orders table and the Employees alias table Employees_1

 

[Shep]

Yes charityg...actually I have done this many times in the past in working with our current database.

I realize that it works, but I wanted to confer with you folks in case this technique is considered to be a crutch for the real thing. You state in your last post that this is correct - so I'll go with it unless 67 other people tell me it's not.

My thanks, charityg.

 

[charityg]

I can't imagine any other technique that would be more correct. Unless, Pat Hartman herself pipes up with a reason why this method is incorrect, I would go with it.

 

[Pat Hartman]

The suggested method is correct. However (why is there always a however), Tech and Writer are actually roles that an employee can take. That makes a many-to-many relationship between orders and employees. I suspect that at sometime you'll discover other roles that an employee can have so you might want to change the tables at this point.

The change would involve removing the Tech and Writer fields from the order table and storing them in a relation table along with their role in the order.

tblOrderRole
OrderID (pk field 1)
RoleID (pk field 2)
EmployeeID


If you have any requirements to report on employees and their roles, the structure I suggested will be much easier to work with.

You can also expand the concept if not all employees can take all roles. Then you would need a table that defines what roles an employee could take:

tblEmployeeRole
EmployeeID
RoleID

The above table would help you populate combo boxes and come between the employee table and the order role table in the relationship window.

 

[Shep]

I like the possibility of later expansion that this structure lends and I dislike getting into a situation down the road that makes me wish I had built some expandability in.

If the two roles were all I ever anticipated needing, then simply dropping a copy of the Employee table into the relationships window would suffice. I suspected there was a better way to construct this, though, and I believe you've pointed me in the right direction. Thanks, Pat and charityg.