How to implement items on sale in a webshop

Question

I am trying to build a webshop and I am having a bit of a discussion with myself about how to implement items on sale.

Is it better to have a ItemsOnSale-table containing references to items on sale, or a OnSale-field in the Items-table.

I have convinced myself that the first solution is more correct, in that listing items on sales requires less of traversing the Items-tree, and also because if information about the sale changes, it does not have to change the Items-table just the ItemsOnSale which seems more "orthogonal".

However, I am no expert so what do you guys think?

Edit:

[products]
Id
Name
Price

[items_on_sale]
ProductId
NewPrice

[orders]
ProductId
UserId

This raises the question of how to select products such that the price from items_on_sale overrides the products price.

Answer 1

If I was to implement a store my entities would initially exist like so:

Products
id, name, ...

Prices
id, price, date, is_sale, ...

Orders
id, product_id, customer_id, date, ...

This way, you are able to keep a record of the prices throughout the products history. If the new price (or discount) is anything other than a standard price drop, flat is_sale that way your view can reflect that. This will also enable you to be able to do a was/now if the change happened recently.