Cost vs quote design question

G

Guest

Hi,
I seem to be having some problems designing these days, mental block or
something.
I have the following scenario for a small painting/building business;
Each Job can have many Quotes.
Quotes are based on prices from a number of suppliers and each quote can
include many products.
I have got the following table relationships:
1 Job => Many Quote
1 Quote => Many For 1 <= Product
1 Product => Supplier

1 Job => Many Cost
1 Cost => Many Product

I am now thinking that I don't need the Cost table.
In the Product table each item has a CostPrice and a DateofPrice.

What we had in the Cost table was a PriceCharged field - this would help us
identify if the supplier was charging differently to the cost price.
Similarly in the For table we have a Discount field so that the CostPrice
could be discounted. (Note: we would have a calculated field to generate a
margin adjusted quote price)

I would welcome any suggestions as to how I should be structuring this!

Thanks,

Graeme
 
G

Guest

Hi Roger,
thanks for replying.
I have subsequently changed my structure.
I now have:
1 Job => Many Quote
1 Quote => Many QuoteDetails
1 Product => Many QuoteDetails
1 Supplier => Many Product
1 Quote => Many CostDetails
1 Product => Many CostDetails

I hear where you are coming from with the Price/Date, but rather than
entering the price each time we want to be able to use a pre-entered price.
For this reason I thought that I could have the price and PriceDate in the
Product table. This way we could select the most recent price each time.
(There are only 3 or 4 suppliers)

Or does this still not make good sense?

Thanks,

Graeme
 
G

Guest

Hi Roger,
not sure if this thread is still active, but here goes.

I have changed relationships to look like this;

1 Client => Many Job
1 Job => Many Quote
1 Job => Many Payment
1 Quote => Many QuoteDetails (Includes the cost price and a date field)
1 Quote => Many CostDetails
1 Pricing => Many QuoteDetails
1 Pricing => Many CostDetails (The cost elements of the project will differ
to the quote)
1 Product => Many Pricing
1 Supplier => Many Pricing

It looks right but I battle to put forms together for a job quote with all
the products and their prices.
Am I on the right track?

Cheers,

Graeme
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top