What is suggested database schema for order/invoice information?

Viewed 21717

I have to make some changes to an e-commerce system to add some additional information and wanted to take the opportunity to possibly make some improvements and make it more flexible. When a customer places an order, we have to store several items of information with each item ordered; for example, the product price, the shipping price, the tax collected, any adjustments that were made.

I am debating if these fields should be stored discretely, such as (simplified example):

ORDER_LINE_ITEM
    OrderLineItemID
    ProductID
    Qty
    Price
    Shipping
    Handling      
    SalesTax
    Adjustment

For example, I could then calculate the total price the customer paid:

SELECT Qty*(Price+Shipping+Handling+SalesTax) As TotalCollected FROM ORDER_LINE_ITEM

Or if I should use a more indirect structure:

ORDER_LINE_ITEM
    OrderLineItemID
    ProductID
    Qty

ORDER_LINE_ITEM_ENTRIES
    OrderLineItemEntryID
    OrderLineItemID
    EntryType
    Value

For example:

1 | 1 | Price      | $10
2 | 1 | Shipping   | $5
3 | 1 | Handling   | $1
4 | 1 | SalesTax   | $1
5 | 1 | Adjustment | -$3.50

The advantage with this is that I can store additional information later on without altering the table schema. However retrieving the information and running reports becomes more complicated and slower.

Is there a best practice for storing this information in order/invoice databases?

Thanks in advance,

Dan

3 Answers
Related