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.
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.
Last Comment
ASKER
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?
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
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.
ASKER
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?
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?
ASKER
Thanks very much!
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
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 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.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
AW