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?
Thanks!
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! 
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".)