Seeking Relationship Advice

[kd2017]

*Title pun intended*



I have many [Jobs]. Each job belongs to a [Contractor], this contractor is the customer or company we have signed a contract with, we work for them. Each job has one or more [Subcontractors], that is companies that work for us.

A contractor has many jobs, job has many subcontractors.

Contractors might work for us on some jobs, and we might work for them on other jobs.

I'm thinking to have all Contractors and subcontractors in one table called Contractors, then using a many-to-many intermediate table called Subcontractors to identify which contractors are actually subcontractors for that job.

My question is when I link tables that are children of a Job back to a subcontractor should the link back to the subcontractor intermediate table or just link directly to the contractors table?


In the same vein I have an invoice table that has a foreign key field for a billing address and contact. There is an intermediate table table for both contacts and address to associate multiple contacts and addresses to a particular job. Should the invoice's foreign keys to the contact and billing address be to the intermediate tables cut out the middle man and link directly to the main contacts or addresses tables?



Lastly, does anyone see any other problems or better ways to design this?

I'm thinking I want to link my foreign key's directly to the main tables, cut out the middle man.

In case you're wondering, the reason the invoices table has a contact and address foreign key is because the address and contact could change from one invoice to the next within a job, not likely but possible.

[ssanfu]

I realize I don't have all of the fields/tables as above, but consider the following as a start:

[kd2017]

So there's no normalization issues with putting address fields in the job table when you already have a separate dedicated address table?

I see you don't have a foreign key in the jobs table for a contractor. Are you suggesting it's most appropriate to have the contractor and subcontractors listed per job in the same table? Should the invoice get it's own address fields or link back to the address table? A contractor can have multiple billing addresses. Most likely only one billing address per job but it's possible for the address to change mid job.

[ssanfu]

So there's no normalization issues with putting address fields in the job table when you already have a separate dedicated address table?

Contractors have an address of their location/home office/place of business.
Where is the Job location? At the contractor's place of business? I wouldn't put the Job address in a table because how many times do you work at the same job location? But I don't know what type of contracting you do..... if you do jobs at the same locations over and over, I would have a "tblJobAddresses" table for the Job addresses.

I see you don't have a foreign key in the jobs table for a contractor. Are you suggesting it's most appropriate to have the contractor and subcontractors listed per job in the same table?

One contractor can work many jobs AND one job can have many contractors.
That is a many-to-many relationship shown in the junction table "jctContractorsJobx".

Should the invoice get it's own address fields or link back to the address table? A contractor can have multiple billing addresses. Most likely only one billing address per job but it's possible for the address to change mid job.

Maybe the Invoice table should be linked to table "jctContractorsJobs" instead of Jobs table. Would you send an invoice for each trade for each contractor? (I think so)

I left the details of the invoice table (the "other fields") for later. Speaking of which, I don't see where there is any way to input work being done or completed. Is there an accounting group that does that? They don't send out invoices?

You say the invoice could go to different contractor billing addresses, so linking back to the address table would make sense.
Are you typing in the invoice data (accounting data) just to make it easier to send invoices?
My dad owned a welding business. My brother-in-law wrote an invoicing program (AppleIIe - jump in the way back machine ) that he entered in all of the money amounts and printed so he didn't have to hand write the invoices (300 - 500 a month)...



What about this?

[kd2017]

Contractors have an address of their location/home office/place of business.
Where is the Job location? At the contractor's place of business? I wouldn't put the Job address in a table because how many times do you work at the same job location? But I don't know what type of contracting you do..... if you do jobs at the same locations over and over, I would have a "tblJobAddresses" table for the Job addresses.

A job location could be anywhere, no reason to rule out getting a job at a contractor's place of business. Different jobs could very well be at the same location.

One contractor can work many jobs AND one job can have many contractors.
That is a many-to-many relationship shown in the junction table "jctContractorsJobx".

The thing is in the context of a specific job the contractor and a subcontractor are distinctly different things that the application will need to handle differently. A job always has one contractor (required) but also many subcontractors (optional). In your junction table should the distinction simply be noted with a boolean for isSub or something to that effect? I'm not sure about programming logic to require a contractor.

Maybe the Invoice table should be linked to table "jctContractorsJobx" instead of Jobs table. Would you send an invoice for each trade for each contractor? (I think so)

I send one invoice to the "contractor", an invoice has many InvoiceValues, each InvoiceValue belongs to a "subcontractor".

I left the details of the invoice table (the "other fields") for later. Speaking of which, I don't see where there is any way to input work being done or completed. Is there an accounting group that does that? They don't send out invoices?

I omitted a dozen or so tables from the screenshot for clarity. Some of those tables track the value of a jobs contract. One purpose of this db is to track % of contract billed which I can accomplish with the sum of job's invoice values / sum of job's contract values.

You say the invoice could go to different contractor billing addresses, so linking back to the address table would make sense.
Are you typing in the invoice data (accounting data) just to make it easier to send invoices?

After job setup the billing data entry into this db will mostly be automated. Veering off topic here. I'm not building an accounting system, this is more of a project management/tracking system. I tried to just included the tables in the screenshot above needed to help clarify my questions.

[EDIT]
Looking at your updated relationship example I don't believe that's what I'm after. One bill is sent for each job, even if I have several jobs with the same contractor I have to submit a separate bill for each job. I wouldn't send bills to my subcontractors either, they'd bill me and I'd bill the contractor.

[orange]

Do you have some sample "desired outputs" (report/form etc)? And some sample test data and test scenarios?
It may be time to try some of the proposed table/relationship configurations to see if it "works".

You may get some ideas from this post (Stump the Model).

[kd2017]

I can generate some test data. The model as I've built it does work as intended. What I'm trying to decide is if it's best practice or there is a less convoluted way I *should* be doing it in regard to linking to a junction table vs bypassing the junction table and linking to direct tables and just using the junction tables as references for user convenience. (Did that sentence make sense?)

[orange]

Think of your choices as potential prototypes.
Set up tests and analyze the process/code and output.
If there is considerable difference in effort/confusion, then make your decision from a point of knowledge - not a guess.
You know your set up and requirement better than any reader.

[ssanfu]

Last time, I promise......

A job always has one contractor (required) but also many subcontractors (optional). In your junction table should the distinction simply be noted with a boolean for isSub or something to that effect? I'm not sure about programming logic to require a contractor.

In the tblJobs table, I added back link to the contractor table. (my bad for taking it out. I misunderstood)
This would be the one contractor (required) for a job. You could set the required property in the table or use the before update event to check if a contractor was entered.

I send one invoice to the "contractor", an invoice has many InvoiceValues, each InvoiceValue belongs to a "subcontractor".

So back to invoice table linked to table jobs.



My main focus was the tables Contacts, JobContacts, Addresses, ContractorAddresses - they could be just 2 tables.....



Good luck with your project..... I'll step out now...