Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help on Relationship design 1

Status
Not open for further replies.

BryanY

MIS
Aug 18, 2001
54
US
Ok my basic setup is that I have a Projects table. Each Record is a different project. The projects table includes an Employees field. How would I go about setting it up so that multiple employees can be assigned to a project instead of just one.

Right now it is set up so that you can select one employee from the Employees table.

I would also like to set it up in the future so that I can generate a form that tells me what projects each employee is working on.

Any help would be greatly appreciated! Thanks!
 
Bryan,

You need an intersection table. This would store the Project ID and any applicable Emp ID's for that project. This table enables a many to many relationship (between emps and projects) by allowing a one to many relationship between this intersection table and the other two.

Set the primary key to the combination of PRJCT_ID & EMP_ID and you'll prevent duplicate emps per project.

Set referential integrity between the projects & emps and this table too--this will prevent entry of non-existent projects and emps, and, unless you set cascade deletes on, it will prevent deleting projects and/or emps when either has a record in this table.
You can store additional data relative to emp/project relationship in this table too.


Col: PRJCT_ID | Col: EMP_ID |Col: STARTDATE

222 345 3 Jun 02
222 367 11 Jun 02

This data entry may be done by a child form from either (or both) projects and/or emps forms.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top