*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.