Design Issues - Contractor Rates and different companies 1

[FishPo]

Here's the situation.

Each contractor has different rates for each company, and usually they stay the same, but they occasionally change over time, so it'd be nice if i could change that later without affecting previously issued invoices.

How can I create a database design that when creating a new invoice, I can select the company, the contractor, the service, and have the rate automatically updated in the sub-form.

On the form, I have the Site Name, location, date, contractor, company, and then in a sub-form, the description, quantity, rate, and line-total.

Currently, I have 7 tables.

Site
-siteid
-contractorid
-locationname
-location
-companyid

Jobs
-Start/End Date
-siteid
-contractorid
-invoiceid
-geologistid

Contractor
-contractorid
-firstname
-lastname
-etc etc

Invoices
-invoiceid
-date
-invoicedetailsid

InvoiceDetails
-invoiceid
-service
-quantity
-rate
-rateid

Rates
-rateid
-companyid
-contractorid
-service
-rate

Company
-companyid
-name
-address
-etc etc

I haven't much experience with this, so... if my tables are completely wrong, well, let me know.

Notes: A contractor can relieve another sometimes which is why I have a 'Jobs' table.

Some services are also different as they may only occur once, so I'd need the flexibility to add it on-the-fly with its own rate.

Am I setting this up correctly, or do I just have my relationships messed up?

I'm thinking both, lol.

If anyone can help me out, that'd be great.

Thanks,
FP.

 

[Trendsetter]

The answer to your initial question needs the rate to be written to your invoicedetails table to be calculated with the Quantity. Do not use the rateid as this looks at the rates table and takes the current value. so delete rateid from invoicedetails.

table invoices should not have invoicedetailsid as this generates a circular reference with invoice id.
invoice should hold companyid - otherwise you don't know who you are invoicing and jobid so that you know why you are invoicing them.

If you have numerous rates for each contractor and these apply to a variety of clients then this appears to be a many to many relationship.


Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[FishPo]

Ah, okay. I should've caught that invoicedetailsid one.

Thanks for the tips, I'll give them a shot and see how it works out.

 

[lespaul]

You should also review 'The Fundamentals of Relational Database Design'

Leslie