What is suggested database schema for order/invoice information?

Question

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

Answer 1

You can find several fairly standard database designs for common problems at Database Answers. Click here for their data models page.

There are several sample models having to do with invoicing.

Answer 2

I'd do a mixed approach, having both:

ORDER_LINE_ITEM
    OrderLineItemID
    ProductID
    Qty
    Price
    Shipping
    Handling      
    SalesTax
    Adjustment
    Extras

ORDER_LINE_ITEM_ENTRIES
    OrderLineItemEntryID
    OrderLineItemID
    EntryType
    Value    

And then doing

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

Then you can work most of the time with a single table, but if you need to search for the details of an item (or add new things that affect the price), you can do it (using the extra field).

In another topic, I'd consider if all those fields should really be on ORDER_LINE_ITEM. I don't know your business, but in the ones I know, the Shipping, Handling and even price is calculated over the whole order, not on just one item (And it's not always possible to split it into separated items). It's also pretty common to have an "Admin" user or password, who can come and sell whatever he wants, inputting arbitrary fields for everything, and ignoring all normal business rules. So you might consider doing something like:

ORDER_LINE_ITEM
    OrderLineItemID
    OrderId
    ProductID
    Qty

ORDER
    OrderId
    ItemsTotalPrice
    Shipping
    Handling
    SalesTaxes
    Adjusment
    FinalPrince

You could create a details table to specify how the values of the order (taxes, shipping, etc..) were created...

In general I've found that the best approach is to have an entity which has the final information of all the order (or operation), and then additional sub-entities that specify how the "total" values were calculated.