Relational Database Design

[ultramel123]

Good Morning

I am putting together a plan for a database which can be used to keep track of our client's rates, supplier cost and profit. I am however struggling with which tables would be best suited ?

So far I have the following which does not seem to be correct - and I cannot get my head around a better solution:

tblClients - would contain basic client information
tblInventory - would be the various services we offer
tblPriceList - (lookup inventory code from "tblInventory" ) and also contain the customer's rate.


tblSuppliers - basic supplier information
tblSupplierRates - (lookup inventory code from "tblInventory") and also contain the supplier's rate for that particular service

Reports:
Profit Report - reflects the list of clients - along with the client rate from tblPriceList - and compares it with the supplier rate from tblSupplierRates

- the problem is that more than 1 supplier can have a rate for each different inventory item

Any advice or guidance will be appreciated

Thank you

[Dave A]

Reports:
Profit Report - reflects the list of clients - along with the client rate from tblPriceList - and compares it with the supplier rate from tblSupplierRates

- the problem is that more than 1 supplier can have a rate for each different inventory item

And rates can vary not just against suppliers, but also over time. I suggest introduce a date field for each of those rates.

Are you using this for estimating purposes or for tracking actual costs against actual projects?

[ultramel123]

Thank you. I will use a date for the rates to be valid from - and until ( for example 1 year and then they can be reviewed)
it is to be used for tracking actual costs against actual projects ...