What kind of relationship is between these two tables.

  • Thread starter Thread starter joyo
  • Start date Start date
J

joyo

I have one table which called 'Order' table, another
table is called 'Employee'. The OrderID is the primary
key for 'Order' table and 'EmployeeID' is primary key
for 'Employee' table. If one order needs to go through
three employees. I added three fields (EmployeeID1,
EmployeeID2, EmployeeID3)in 'Order' table. What kind of
relationship is between these two tables. I tried to
build one to many relationship, but it was not allowed.

Why and how?

Thanks

joyo
 
You need a 3rd table. The EmployeeID sould be Indexed-No
Duplicates and the the OrderID should be Indexed-No
Duplicates. Create a 3rd table and have additional
OrderID and EmployeeID (with the same data type as the
original tables). Once you link them together, you should
be able to have as many employees (not just 3) working on
as many orders.
 
joyo said:
I have one table which called 'Order' table, another
table is called 'Employee'. The OrderID is the primary
key for 'Order' table and 'EmployeeID' is primary key
for 'Employee' table. If one order needs to go through
three employees. I added three fields (EmployeeID1,
EmployeeID2, EmployeeID3)in 'Order' table. What kind of
relationship is between these two tables.
Click to expand...


This is a many-to-many relationship (many employees can work
on an order and many orders can be assigned to an employee).
This is dealt with by creating a third table (often called a
"Junction" table), that contains at least two fields, a
foreign key to employee and a foreign key to orders. The
two fields are usually then used as a compound primary key
so that you can't assign an employee to an order more than
once. This allows you to have as many employees working on
an order as you need and not be limited to the number of
fields you've allocated in the order table.

It is helpful to keep in mind that anytime you have fields
named something like Employee1, Employee2, ... , you are
getting yourself in trouble.
"Rows are cheap - Columns are expensive"
 
Back
Top