Query KeyID from one of two tables.

[bballhermit]

I have a situation where I have a table for employee information with a key ID for each employee as well as a table for subcontracting companies each with a key ID. I have a table with Assignments which assigns either an employee or a subcontracting company to a specific amount of hours in a specific month on a certain project.

Is there a way to make each record in the Assignments table require one and only one of either a subcontractor ID or an employee ID? What is the best way to implement this functionality? How then do I write a query that, assuming a given project and year, will list all of the subcontractors and employees on that project? Thanks.

 

[jzwp22]

I generally keep people in a separate table from companies as you describe but others have said that people and companies should be treated as the same entity type since companies are just a collection of people. In your case, since an assignment can have either an employee or a company, then you will need 1 table that holds both.

In your assignment table you would just have a foreign key field that references the primary key of the table that holds the companies/employees

tblPeople
-pkPeopleID primary key autonumber
-txtPeopleName (the employee's name or the company name)
-txtAddress

tblProjects
-pkProjectID primary key, autonumber
-other project related fields

tblProjectAssignments
-pkProjAssignID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkPeopleID foreign key to tblPeople