What do I need to keep in a database to record the history of orders?

Question

I want to keep customers' order history.

I am thinking of keeping names of product, number of product ordered, price of product, date of order, name, address etc

Table can be order_history and there will be field for id, date, cutomer_id,..

Then a question regarding names and number of products came in my mind.

How should I keep them in the database?

A customer may order more than one product with different numbers.

Should I keep it in one field in array, like {product1, 2, product2, 1, product3,2 etc}

Or should I keep name and number separately?

Or any other way?

What is the common practice?

Can you suggest an appropriate database structure, please?

Answer 1

I would have a look at table structures something like this

TABLE Products
    ProductID INT
    ProductName VARCHAR(50)
    ProductDescription VARCHAR(100)
    ProductCategory INT

TABLE ProductCategories
    ProductCategoryID INT
    ProductCategory VARCHAR(50)
    ProductCategoryDescription VARCHAR(100)

TABLE Customers
    CustomerID INT
    CustomerLastname VARCHAR(50)
    CustomerFirstName VARCHAR(50)
    PhoneNumber VARCHAR(20)
    FaxNumber VARCHAR(20)
    CellNumber VARCHAR(20)
    Email VARCHAR(50)

TABLE Orders
    OrderID INT
    CustomerID INT
    OrderDate DATETIME
    DeliveryDate DATETIME
    PaymentDate DATETIME

TABLE OrderItems
    OrderItemID INT
    OrderID INT
    ProductID INT
    Quantity FLOAT
    Price FLOAT
    Discount FLOAT

You can add more detail to the structures as given here, maybe adding more details to the Products table giving specific descriptions or item numbers. Same goes for the ProductCategories or any of the other tables.

Answer 2

Your database structure could be some thing like this:

customer: id,and all information about the customer.

product: id,barcode,and all information about the product

factor: id,customer_id,date,delivery-date,ship-date,payment_type...

orders: factor_id,product_id,count,description

REMEMBER: when designing a database , NO String have to be repeated in your tables, in such a situation you have to create a new table and store string in that table, then use the new table id's in your table instead of the string.

Answer 3

You should definitely be putting the names of products in the order items table - because over time, products will get renamed and ultimately deleted - you will want to remember what they were called at the time they were ordered. Any other similar details (Manufacturers code, SKU, etc) need to be kept in there too (along with the price you sold it at of course)

Answer 4

SQL CDC is a great tool/framework on SQL server 2008 to keep history and audit of data without much manual work - please take a look http://stackoverflow.com/questions/349524/sql-server-history-table-populate-through-sp-or-trigger/349546#349546