Individuals and Contractors Working on Jobs (1 Viewer)

[wilderfan]

Seeking some feedback on the appropriate design of the tables in an E-R diagram.

One table contains a list of jobs (tblJob). Attributes would be: JobID, JobName.

Second table is the linking table between the jobs and the persons performing the jobs. (tblJobPerson). I was planning for this table to be the classic linking table to allow for M:N relationships between jobs and persons. Attributes would be: JobID, PersonID.

Third table contains the list of persons (tblPerson). Attributes for this table would be: PersonID, FName, MName, LName, Other. The Other attribute would be used for entries like "Jr." or "Sr."

It then occurred to me that the "Person" working on a job could also be an outside vendor or contractor.

So my question is: Should I create a fourth table (tblContractor) and create a 1:M relationship between that table and the PersonID foreign key in the second (linking) table?

Alternatively, I'm wondering if I need to create two subtype entities (one for individuals and one for contractors) that have 1:1: relationships with the table tblPerson.

Any suggestions ?

 

[plog]

Explain your data better. Not your tables and fields and all that, but the data going in there. Perhaps work up some sample data to demonstrate. Or better yet, look over some actual data to find out what you have to accomodate.

Answer these questions:
What data/fields would make up a Contractor record?
Would you have a contact person for that contractor?
Can there be multiple persons and multiple contractors per job?
Would a person (from tblPerson) be in charge of managing a contractor or contractors?

It really comes down to how much data do you need per person/contractor. If its not really important data, you could jam it in tblPerson (a contractor has a name after all, even if its "Acme Roofing"). If however you are going to want to do other stuff (billing, time allocation, etc), then maybe a different table would be necessary.

 

[wilderfan]

Yes, there could be multiple contractors and / or multiple individuals working on a job.

Initially, I thought only about individuals working on a job. Hence, my initial view that the attributes should simply be: PersonID, FName, MName, LName, Other

But then I realized that Acme Roofing could be associated with a job in addition to John K. Smith Jr.

I don't envision the Contractor record involving anything other than the name of the Contractor.

So I suppose that I could simply use the FName attribute for either the first name of an individual OR the full name of the contractor's business. But then, there are a lot of nulls for the other attributes (MName, LName, Other). Having all those extra nulls didn't strike me as being "best practice".

 

[The_Doc_Man]

In general, adding tables in parallel (i.e. an Employees table and a ContractEmployees table) is wrong. Add things in series. In the Employees table have one or two fields - always have IsContractor (a y/n field) and (depending on what is right for your situation), either a ContractorID field that can be blank for non-contractors, to be applied when a person can ONLY be associated with a single contract at a time, or have another junction table that links the contractor company to its employees and to the project. (If you have the case where the same person can be a contractor for two different companies on two different projects at the same time, may the Access gods bless you.)

Don't forget that when doing all of this, you can drive forms and reports from compounded, layered queries perfectly well. So you could build a query of employees left-joined with the contractor companies and just watch out for cases where you need the NZ function to prevent abominations in forms or reports. Then join that query to the one that shows employees on a project.

 

[Solo712]

Yes, there could be multiple contractors and / or multiple individuals working on a job.

Initially, I thought only about individuals working on a job. Hence, my initial view that the attributes should simply be: PersonID, FName, MName, LName, Other

But then I realized that Acme Roofing could be associated with a job in addition to John K. Smith Jr.

I don't envision the Contractor record involving anything other than the name of the Contractor.

So I suppose that I could simply use the FName attribute for either the first name of an individual OR the full name of the contractor's business. But then, there are a lot of nulls for the other attributes (MName, LName, Other). Having all those extra nulls didn't strike me as being "best practice".

Hi, I often design job-order or job-tracking systems that use alternately in-house and contract labour. For my purposes I always need to have separate tables for employees and contractors because they have very different set of attributes. I need those for other processing (eg. employee scheduling, VAT info, contractor status, etc). As a rule then I have an Employee table, and a Contractor table which both have a field called "Jobber_ID" that assigns a unique ID number to any new record in either of the tables. This allows creating a UNION query from the two tables to select the person or company doing a particular job. You might want to consider doing something like that.

Best,
Jiri