How to design a database to store quotes online

database-design

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

Best Answer

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)

Related Solutions

How to design a relationship database table to store friendship relationship

I'd create a table a lot like the one that you have. I'm using SQL Server data types and syntax, you may need to tweak depending on your platform.

CREATE TABLE FriendStatus
(FriendStatusId BIGINT PRIMARY KEY IDENTITY(1,1),
FromUserId BIGINT,
ToUserId BIGINT,
StatusId TINYINT,
SentTime DATETIME2,
ResponseTime DATETIME2);

Indexing of the table will be critical as the table grows to the tens and hundreds of millions.

SQL Server – Database Design for an Online Baby Shop and Toy Shop

Your design commonly looks good except few mistakes.

1) You had used a wrong field in order to establish a relation between ProductCategory and Product. You have to use ProductCategoryID in Product table instead of using ProductCategoryName. Howeever, some products may belong to more than one product category.
If this is a valid case for your project, you should add additional table something like "ProductVsProductCategory" which will allow you to establish a "many to many" relation between Product and ProductCategory tables if you intend to add some products into multiple categories.

Basic structure of ProductVsProductCategory might look like following:

ProductVsProductCategoryID (primary key)

ProductID (refers to Product table)

ProductCategoryID (refers to ProductCategory table)

2) OrderProductName field in the OrderDetail table is unnecessary. You have a relation to Produt table and you can access ProductName from there anyway. You can cancel it.

3) OrderNumber in the Order table is unnecessary if it is not a special value rather than unique identity number. You can use OrderID field as an identity of the order.

4) Similary, OrderQuantity and OrderTotalPrice fields might be unnecessary if you have not special reason to place them there. You can calculate order quantity and total price values by means of using basic sql statements that sums related OrderDetail rows.

There are many additional tables can be added depending on your project requirements. I also recommend you to follow MSDN articles and specially CodeProject in order to improve your abstraction skills.

Best Answer

Related Solutions

How to design a relationship database table to store friendship relationship

SQL Server – Database Design for an Online Baby Shop and Toy Shop

Related Question