how would you store itemized orders?

[dadamssg]

I'm trying to figure out how i should be storing itemized order information. My orders require a down payment and has multiple taxes, each with their own rate. What should my database look like? I know i should be storing the base price. Then, should i store the tax rates for each order or tax amounts with each item in a row? Then for the transactions(deposit and remainder)...should i store the exact amount of the base prices and taxes each transaction covers? or just the total amount of the transaction to keep it simple?

 

I'd like to be able to edit as much information as possible if need be, base price, tax rates/amounts, etc.  And i'd also like to generate reports on the data(how much of product be was sold, the tax amounts owed, average price each product sold for, etc.)

 

How should i organize all this in my database?

[DavidAM]

The database structure should match your real-world setup as much as possible. In Texas, USA, Sales Tax is based on the TOTAL price of all Taxable items on the order (some items may not be taxable). Also, in Texas, all taxable items on the order are subject to the same Sales Tax (and therefore the same rates) -- although certain sales are subject to other taxes (such as cars, hotel rooms, car rentals, etc). And finally (not that there is ever a final word when talking about taxes) some Customers are exempt from Sales Tax. In this scenario, I would have a Order Header table with the order specific data, and a Order Lines table with an entry for each line on the order. I would not store the order total or line total or tax total or any other calculated field; these can all be calculated on the fly.

 

OrderHeader: ID, Customer, IsTaxable, TaxRate -- TaxGroupID (see below)

OrderLines: ID, OrderID, Item, Quantity, UnitPrice, IsTaxable

 

and possibly

 

OrderPayments: ID, OrderID, AmountPaid

 

 

In most applications I have worked on, instead of the TaxRate in the header (or in addition to it), there is a TaxGroupID which is a foreign key reference to a table indicating what tax rates are applied. That table (usually) has a child table listing the individual jurisdictions and their rates. (In this case, I would probably store the aggregate tax rate:

 

TaxGroups: ID, Description, TotalRate

TaxGroupJurisdictions: ID, TaxGroupID, TaxJurisdictionID

TaxJurisdictions: ID, Description, TaxRate

 

 

One final note: Since tax rates are subject to change, I would (likely) store the rate used for a particular order in the order header AND I would make sure to store the date of the order so I could prove that I charged the rate that was in effect on the date the order was taken.

 

 

 

Note: The above database structure is overly simplified to answer the specific question at hand, there will be many other fields, and (most likely) foreign key references to other data in the final structure.

[dadamssg]

this is great. Thank you so much! I'm in Texas as well!

[Mannylints]

This is awesome! totally helps me out as well! thanks