Avatar of gw5434

asked on 

E-Commerce Database Design - Product Price Structure

I am building an e-commerce system for a client, and currently trying to structure the database schema with regard to product pricing.

My problem is this:

Each product may have many different prices. These may be one of the following types:

1. Single flat-rate price
2. Quantity-dependent "bracketed" price (e.g. 1-3, 5-10, 10+)
3. Sale Price

The first price is straightforward, just one price per product.

The second price is a bit more complicated, as there are lower bounds and possibly upper bounds (1-3 has an upper bound of 3, but 10+ has no upper bound). The single price above could be classed as 1+.

The sale price is a bit more involved as - it could be specified as an absolute discount or as a percentage. Furthermore, however it is specified, the sale price needs to be applied to all prices for the product.

Does anyone have any best-practice advice for structuring the database? Currently there is a products table with the usual info in (ID, Name, Description etc.) and I assume I will need a second Product Price table to allow for multiple prices per product.
Microsoft SQL ServerDatabasesMySQL Server

Avatar of undefined
Last Comment
Avatar of Arthur_Wood
Flag of United States of America image

Just to add to the confusion, you also need to keep a Price History - the Price(s) can change over time, and you may need to know the price as it/they was/were at the time the purchase was made, for auditing purposes.

Avatar of gw5434


I had planned to record this as part of the customer invoice structure. Broadly speaking, there is an Invoice/Order master table and an Order Lines table with a record per product ordered. I assume this is the most logical place to record the price at the time of purchase?
Avatar of Ruscal
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of gw5434


That sounds like the best solution, I hadn't really thought of 0% discounts - a bit stupid of me really.

Assuming the prices were split into a separate table then this would allow for applying a sale percentage globally or by individual price option, which is exactly what I need.

On a slightly separate note, if you had a product which had sub-options (e.g. a shirt in different sizes) is it better to record each product as a completely separate item, or by having a child table with the sizes in?

I am concerned that if you wanted to have different prices for different options (e.g. something in XXXL is more expensive than something in XS) then the user would need to enter, and the database store, prices for each option - not ideal but possibly unavoidable?
Avatar of gw5434


Thanks very much!
Avatar of Ruscal
Flag of United States of America image

Thanks for the grade and comment.  Like i said, I've tried a few different ways on this and the 0% discount always ended up being the easiest on the coder and normally easy for the client / store manager too.

I always enter different products as multiple items in inventory (a master product table with the wordy description and images, and a child table with the specific description (red, black, XXLT, XS, etc) and the price points)  The child table reflects the inventory while the parent table is more about the advertising.  But I think of things from the logistics point of view, and that makes keeping up with inventory much easier.

[product] -- [min qty] -- [max qty] -- [normal price] -- [discount %]
XXS (black) -- 1 -- 5 -- $10.00 -- 0%
XXS (black) -- 6 -- 15 -- $7.00 -- 0%
XXS (red) -- 1 -- 5 -- $10.00 -- 0%
XXS (red) -- 6 -- 15 -- $7.00 -- 0%
XL (puke green) -- 1 -- 9999 -- $10.00 -- 80%

(I've also seen negative discounts on special item... the XXXLT is -5% discount to tack on an additional 5% to the price, not my favorite, but workable
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo