Price in OrderDetails

Getting back into database modeling after being away for many years.

I am modeling a classic many-to-many setup for an order database.

In the PRODUCT table, I have "unit_price".

I have forgotten the standard convention for what you would call the price field in the ORDER_DETAILS table.

Is it...

1.) "sale_price"?
2.) "order_price"?
3.) "item_price"?
4.) "purchase_price"?
5.) Other?


It could be all of the above
You can purchase it for $5
Sell it for $7.50
Could be on sale for $7

The (implied) question was, "Which one sounds the best/most descriptive?"

(I'm not capturing a sale price, I am capturing the price when the item is sold. And it escapes me what name is usually used...)

I would go with PriceSoldAt, that is just me

Here are my first impressions...

1.) "sale_price"? ===> Might be confused with a "Sale"?!

2.) "order_price"? ===> Might be confused with the total price of the Order

3.) "item_price"? ===> Okay

4.) "purchase_price"? ===> Maybe the best since it stresses this was the price you paid at the time of purchase.

5.) Other? ===> Seems like the name I used to use is still escaping me?!

I worked in an order system for years, both as developer and as DBA.

item_price is fine. price_paid is also used.
item_cost is usually the company's cost, part of the cost-of-goods-sold metric.

You should capture a sale date, but you should also capture and store the specific price paid. Even if you have a standard item price list, it may have been sold at a different price for whatever reason, and on the credit side you never want to refund based on more than the actual price paid.

@ScottPletcher, that was my question! :wink:

In my PRODUCT table, "unit_price" represent the current price of the product.

In my ORDER_DETAILS table, field represents the price the Customer paid.

Today is June 20, and maybe the "unit_price" of a pair of jeans is $20.
On June 20, a Customer buys a pair of jeans, and thus the "purchase_price" is $20.

On July 1, the price of jeans goes up, and so the "unit_price" of a pair of jeans is now $30.
However, all orders prior to this would have a "purchase_price" of whatever the "unit_price" was at the time or purchase (e.g. the "unit_price" was $20 up to July 1).

That should work as well.

Stick for "unit_price" being the current selling price, with or without any discounts, depending on how your company uses those.

Use "item_price" for the price charged a customer for a product in an order. Or "purchase_price". I guess it's still technically a "purchase" even if it's a free item (such as a brochure) or a comp'ed item (such as non-charged item to make up for an earlier overcharge/mistake).

Just got back from my trip.

I am leaning towards "purchase_price" as that makes it clear that is the price at the time of purchase. ("item_price" could be viewed as the "unit_price".)