Quoting Database Table's help

[roccoau]

Hi,
I am starting to design a Quoting database and would like some advice on how to set up my tables.
I would like to record all quotes received with all relating sales, purchasing and despatch/invoice details included.
I am thinking of having following tables – Am I on the right track ????

Customer Table - With all details relating to customer
Customer ID
Name
Address
Phone
Contact

Products Table – With all details for quoted products
Product ID
Part Nbr
Description
Price
UOI

Quote Table – With all details Specific to Quote
Quote ID
Customer ID
Qty
Sales Price
Product ID
Date Received
Date Required
Dated Quoted

Purchase Table – With all Purchase details
Purchase ID
Purchase Order
Supplier ID
Date Ordered
Date Received

Sales Table – With all final sales / Invoice details
Sales ID
Product ID
Customer Purchase Order
Po Date
Invoice date
Invoice No
Total Invoice Amount
Paid Date
Despatch Details
Payment Terms
Delivery Terms

Please let me know if I am on right track or any advice you could give would be appreciated.
Tks

 

[ColinEssex]

Just looking very quickly as I am on the way to a meeting

1) you have a supplier ID but no supplier table

2) in the purchase and sales - why have 2 tables? whats the difference?

3) there's no mention of customerID in purchase or sales tables

4) if a quote turns into a sale, shouldn't there be a link there? or you'll be entering the data twice

5) how do you plan to monitor stock levels and re-order dates etc

Col

 

[neileg]

As you update the price in the products table this will affect all previous quotes. Either you need some mechanism to track price changes over time, or you need to store the product price in the Quote table. I would choose the second, because it's easier (though not as 'pure').

Probably worth having a Contacts table separate from the Customer table so you can deal with multiple contacts for one customer.

 

[roccoau]

Thanks Neil & Col this is good info.
Rgds