Quote Creator - Best way to use SQL

Question

Hi,

I'm doing a quote creator tool. There is a table that will store all the customer info. Then there is a quote table which will share the customer_id key and store all details of the quote.

I would like to store all details of the products quoted in the quote table. Now with a shopping cart you would have a products table with all the product details in it and cross reference, but with this there is no set amount of products as the products that will be quoted for is almost infinate and most will only be quoted for once and at all different prices.

So when in the future the quote is pulled up I would like to know all the products and their coresponding quantites and prices in the original quote.

Now the only way I can think of doing this would be to create a new table for every quote created and prefix the table name with the unique quote_id but this could be very messy with lots of table - surely there is a better - please help if you can think of a better way.

Thanks and hope I explained myself OK.

Answer 1

You can cross-reference the products table, but copy any information that might change. This means you have both the information as it was when the quote was created, and also a link back to each product's current data.

e.g. QuoteProduct table (Quotes would have a many-many relationship with Product via QuoteProduct):

---------------         ------------------        ---------------
| PRODUCT     |  1:many |  QUOTEPRODUCT  | many:1 | QUOTE       |
| ProductId   |--------<|  QuoteId       |>-------| QuoteId     |
| Price       |         |  ProductId     |        | DateCreated |
| Description |         |  Price         |        | Description |
---------------         |  Description   |        ---------------
                        -----------------

You can give QuoteProduct its own primary key or you can make it ProductId, QuoteId. If you want to have the same product in a single quote multiple times but with a different price etc. you probably need to give it its own primary key.

Answer 2

Create one new table, call it quote_items. Basically, this table contains copies of all of the products in a quote with their prices as they were at the time the quote was made. This quote_items table has the quote ID as a foreign key.

To create a quote, create a new entry in your quotes table, then copy all of the items in your quote into the quote_items table, with the quote ID of the quote just created.