Foriegn keys

[borrisjd]

I have a table, lets call it contacts that holds personal info like name phone and such, it also holds a fk to a table called workplace. the workplace table holds company info. one column in the workplace table is instructor, which holds a fk back to the contact table. Is this the way it should be done?

I am trying to connect all employees with their workplace and at the same time know who is the primary instructor at any given workplace.

[prabirchoudhury]

ok.. its depends on what relation you are tryng to build .. is that one to many or many to many relationship.

1. is that one to many relation ship (contacts ->workplace) that is one person may have ome then one workplace . so then take the contact pk as fk in workplace table or one workplace(role) may have more people then workplace pk as fk in contacts table

2. in many to many relationship, one contacts may have morethen one workplace or one workplace may have more then one contact. then you need tp make another connecting table that would take contacts pk and workplace pk as fk in that connecting table .. say connecting table name contacts_workplace
PK | FK | FK
cw_id | contacts pk | workplace pk


think that gonna help you ..

more online on relational database tutorial

[Atli]

Hi.

I can't see a problem with the structure you described.
Other than it limits employees to a single workplace, and only allows a single instructor per workplace.
In other words; both relationships are one-to-many (1:N) relationships.

If that's all you need, then it should work fine.

If you needed more, like say; to allow each employee to join multiple workplaces, and have multiple instructors per workplace, then you would have to switch over to many-to-many (N:M) relationships.

Adding to prabirchoudhury's explanation; this means extracting the FK columns from the actual tables and putting them into intermediary tables, where a list of relationships would be stored.
Like:

Employee:      Workplace:
+----+------+  +----+--------+
| ID | Name |  | ID | Name   |
+----+------+  +----+--------+
|  1 | Joe  |  |  1 | First  |
|  2 | Jane |  |  2 | Second |
|  3 | Jack |  |  3 | Third  |
+----+------+  +----+--------+
 
EmployeeWorkplaces:
+------------+-------------+
| EmployeeID | WorkplaceID |
+------------+-------------+
|          1 |           1 |
|          1 |           2 |
|          2 |           2 |
|          2 |           3 |
|          3 |           1 |
|          3 |           3 |
+------------+-------------+
 
WorkplaceInstructors:
+-------------+------------+
| WorkplaceID | EmployeeID |
+-------------+------------+
|           1 |          1 |
|           1 |          2 |
|           2 |          3 |
|           2 |          1 |
|           3 |          1 |
+-------------+------------+

[prabirchoudhury]

thats good, Thanks Atli for more explain in details

[borrisjd]

Thanks for the explanations. I think thats what I need to do, is add the second table for the many to many relationship.