Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a form to generate quotes online. The form has many products with their respective prices and options.

The users will generate the quote online and I need to store the quotes of the users when they submit the form.

My question is how I do a design a database to store this information?

This is an example of my form:

Description        amount        price x un      total
product1             1             15000         15000
product 2            3             10000         30000
Total                                            45000

I have thought the next options:

id_quote        descriptions          total
1              product1 1 15000       45000
               product2 3 45000      

Other option is by date:

id    date                      description     amount         price          total
1    15/03/2012 3_45:45         product 1       1             15000          45000
2    15/03/2012 3_45:45         product 2       3             10000          30000
share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

Your second option is better, though I'm confused: is that a Quotes table? If so, are those two records the same quote or different quotes? Also, there's no need to store total, you can calculate that when you need it. I'd generally say that calculated values should only be stored if the calculations are very complex and long-running.

Here's a simple table structure for the quotes that might help get you started:

Quotes
------
  quote_id (primary key)
  client_id - so you know who the quote belongs to.
  date - when the quote was generated


Quote_Items
-----------
  quote_item_id (primary key)
  quote_id (foreign key to Quotes.id)
  product_id (foreign key to you products)
  single_item_cost  (copy from products so that if the product price changes the quote won't change)
  quantity (multiply by single_item_cost to get total cost for this quote_item)
share|improve this answer
    
Thanks for your answer. The users are not registered for fill the form. All people anonymmous can to quote products with the form. When the users send the form the data of quotes must to stored in the database. I don'´t know the id of the client. Excuse me for my poor english. –  cabita May 23 '12 at 15:49
    
@cabita: Ok, it doesn't have to be an actual client ID, but you need to have a connection to whatever information you use to identify the anonymous people who request quotes. Do they have to give an email address? You could use for client_id when there's no other information. Or maybe you don't even need to identify the person who requested the quote. It was only a suggestion because that's how I've done it in the past (actually, what I've done in the past is closer to HLGEM's answer, but I tried to simplify it to the most essential parts). –  FrustratedWithFormsDesigner May 23 '12 at 15:59
    
Yes, you are right. Thanks for your help. –  cabita May 23 '12 at 19:23
add comment

I worked on an application many years ago that produced complex sales quotes for RFPs. It can get fairly complicated. One of the biggest things I see missing from your tables is a way to do discounts. Another is a way to separate the quote into parts, for instance you might havea quote for a house renovation that would include a kitchen quote and a bathroom quote, etc. That way the customer can see each chunk and make independent decisions based on the parts. You may not need this, but it is something to consider. YOu may also need to consider ifyou need to quote labor as well as parts/

So table structure (this is just a starting place)

Quote
QuoteId (PK)
Quotename
Customerid (FK)
DatePrepared
DateDubmitted (if you need to track both)

QuoteParts
QuotePartId (PK)
Quoteid (FK)

QuotePartDetail 
QuoteDetailID (PK)
QuotePartId (FK)
Partnumber(FK)
Price
Discount

QuoteLaborDetail (you may or may not need this depending on the kind of quote and it may need to specify the type of person whose labor you are quoting, electrician, carpenter, etc.)
QuoteDetailID (PK)
QuotePartId (FK)
QuoteLabortaskId (FK)
LaborDescription
HourlyRate
NumberofHours
Discount

And of cousre you would want associated tables for the customer, Customer address, partslookup, labor tasks lookup, etc.

You might also want to provide a place on your form to add a new part or labor task to the appropriate lookup table. Sometimes you may need to quote something that is not already in the system and the best place to do that is while you are creating the quote.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.