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.

 

[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.