General advice needed - database design - Is this a bad way to design a pricing table?

Question

General advice needed - database design - Is this a bad way to design a pricing table?

Rob

Ten Centuries

Points: 1035
More actions
July 3, 2017 at 8:14 am

#337916

Suppose we are selling bottles of water that can be bought in sizes of 1 to 10 litres (1, 2, 3, etc...) . In this example the customer can buy up to 4 bottles of a single size (1, 2, 3 or 4 bottles).
Ignoring the fact that a formula can be used, a colleague and I have proposed 2 different ways of storing the pricing but cannot decide if one is better than the other, or why you would implement 1 or the other. (or if these are both poor design, what's the alternative?)
Method 1 - create a single table with a column for each of the available quantities
With this I would have run a query like "select [Quantity 1] from tblPrice WHERE [Bottle Size] = 1" to get the price.
Method 2 - create 3 tables, one each for bottle size, quantity ordered, and one that links the 2 based on ID
With this I would run a query like "select [Cost] FROM [Price] WHERE [Bottle Size ID]=1 AND [Quantity ID]=1"
The reason I am asking is because we have a much larger set of variables than this - bottle size may go up to 100, and Quantity Orders may go up to 5000. So that is 100 * 5000 = 500,000 price look ups! I'd love to do it as a formula, but the pricing won't always be calculated in the same way, there may be ranges (i.e. the higher the quantity the more the discount), or there may be one off prices that don't follow the rules at all.
How do you generally handle calculating the price for something?
Thanks
July 5, 2017 at 7:26 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Method 1 is terrible. It's a violation of first normal form, and it's a pain to work with. What happens if there's a decision to allow up to 5 bottles, or limit it to 3, or allow 5 for smaller bottles, 3 for larger and only 1 for the largest.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rob Ten Centuries Points: 1035 More actions · Answer 2

GilaMonster - Monday, July 3, 2017 8:32 AM
Method 1 is terrible. It's a violation of first normal form, and it's a pain to work with. What happens if there's a decision to allow up to 5 bottles, or limit it to 3, or allow 5 for smaller bottles, 3 for larger and only 1 for the largest.

Thanks for your input 🙂 - I would not have chosen method 1 myself, but I also would not have been able to explain why. In answer to your points though, I'd have suggested that if up to 5 bottles were to be ordered, you could just add another column and fill in the 10 prices, or if the quantity ordered needed to be restricted to 3, delete the columns representing quantities greater than 3.

Regarding your point about limiting quantities ordered based on the size of the bottle - couldn't this be handled with NULLs? I.e. if a price lookup returns NULL, it can be excluded from the results set?

Just had a quick read about this "First Normal Form" which you referred to - does it violate 1NF because there because we would have to use NULLS when a bottle cannot be ordered in a certain quantity?

sgmunson SSC Guru Points: 110640 More actions · Answer 3

Most of the data tables I've seen with pricing information are first based on a specific item. In most cases, the database will have an ITEM table, that has a SKU, along with pertinent information like Size, Shipping Weight, Case Quantity, Description, Height, Length, Depth, etc., and then price as a fixed value. Discounting to pricing is almost never done in that table, and is almost always volume-based, and is handled either at the invoice level, or at the customer level (e.g. a customer record contains a discount level). If using the customer level, the ITEM table may have one or more flags to indicate an exemption from a particular discount type. I've never seen anyone handle discounting to pricing in the ITEM table, except for quoting a wholesale price and a retail price, and in many of those cases, neither is usually applicable, but is there more for reference than anything else.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 4

Another thing you could do is have a calculation table. Presuming that there is some correlation between size and quantity in some cases.
That way if you have some correlation between quantity and size, you can have your price table auto-populated. Then for the one-off ones, you can add things in manually.

In my opinion, I would say both methods are not all that great. I think that method 2 but only using table 3 is a better option. Having the extra tables when bottle ID 10 corresponds to 10 litres, there is not much benefit in having those ID tables.

I would do it with 1 table that has 3 columns: Litres, Quantity, price. And then have a second table that is a discount calculation table with columns like: Litres, Quantity, Calculation. Calculation would be a varchar and you'd use it in dynamic SQL for generating the price column. An example of the calculation table could be something like:
Litres Quantity Calculation
1 1 SELECT 10
1 2 SELECT (Calculation * 1.9) AS Calculation FROM calculation_table WHERE Litres = 1 and Quantity = 1
1 3 SELECT (Calculation * 2.8) AS Calculation FROM calculation_table WHERE Litres = 1 and Quantity = 1

For example. 10% discount for buying 2, 20% discount buying 3 and so on. Then build up a stored procedure or SSIS package that would take those calculations and store them in the main table. Storing them in the main table is for faster lookups. And the calculation table can be used to generate reports for price changes.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

what consideration have you given to when to wish alter a price?
are you intending to keep a history of price changes per item?
If you have a start/end datetime per item you could upload new prices in advance of them becoming active (also useful for shorterm promotions)

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Rob Ten Centuries Points: 1035 More actions · Answer 6

J Livingston SQL - Monday, July 3, 2017 9:42 AM
what consideration have you given to when to wish alter a price?
are you intending to keep a history of price changes per item?
If you have a start/end datetime per item you could upload new prices in advance of them becoming active (also useful for shorterm promotions)

We won't be keeping any historical details of price changes, as the table(s) I am referring to above would be used to drive the price shown to the website user. Basically the user selects the bottle size and quantity required, and then I'll be looking up the price based on those 2 parameters.

With regards to updating the pricing, the prices would be supplied to me on an excel spreadsheet from the costing team (which will look like the image for method 1). So for each column in the spreadsheet I'd have to write an update statement to overwrite / update the existing prices in the database. This is a very time consuming process with over 100 columns.

sgmunson SSC Guru Points: 110640 More actions · Answer 7

r.gall - Monday, July 3, 2017 9:48 AM
J Livingston SQL - Monday, July 3, 2017 9:42 AM
what consideration have you given to when to wish alter a price?
are you intending to keep a history of price changes per item?
If you have a start/end datetime per item you could upload new prices in advance of them becoming active (also useful for shorterm promotions)
We won't be keeping any historical details of price changes, as the table(s) I am referring to above would be used to drive the price shown to the website user. Basically the user selects the bottle size and quantity required, and then I'll be looking up the price based on those 2 parameters.
With regards to updating the pricing, the prices would be supplied to me on an excel spreadsheet from the costing team (which will look like the image for method 1). So for each column in the spreadsheet I'd have to write an update statement to overwrite / update the existing prices in the database. This is a very time consuming process with over 100 columns.

Honestly, that whole scheme sounds like a tremendous waste of resources. I'd much rather just have one SKU for each possible bottle size, and compute the discount based on bottle size and quantity with a fixed formula that sits in one and only one place in your overall website set up, whether in a scalar function in the database or in some kind of C# function. That way, you only have to fix one thing to change the pricing formula.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

from your example data it appears that you are applying the same discount to all sizes.
buy get 5% off, buy 3 get 10% off, buy 4 get 15% off.

is this a correct assumption?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ScottPletcher SSC Guru Points: 101085 More actions · Answer 9

In the real world, I've seen the method (1) style used quite a bit. Yes, it does violate 1NF, but it's pragmatic for the task at hand. Bottle size would of course be changed to a code instead though, as in method (2), and you'd need to add a discount % or code for each qty break.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 10

r.gall - Monday, July 3, 2017 9:48 AM
J Livingston SQL - Monday, July 3, 2017 9:42 AM
what consideration have you given to when to wish alter a price?
are you intending to keep a history of price changes per item?
If you have a start/end datetime per item you could upload new prices in advance of them becoming active (also useful for shorterm promotions)
We won't be keeping any historical details of price changes, as the table(s) I am referring to above would be used to drive the price shown to the website user. Basically the user selects the bottle size and quantity required, and then I'll be looking up the price based on those 2 parameters.
With regards to updating the pricing, the prices would be supplied to me on an excel spreadsheet from the costing team (which will look like the image for method 1). So for each column in the spreadsheet I'd have to write an update statement to overwrite / update the existing prices in the database. This is a very time consuming process with over 100 columns.

If this is basically a data-dump from an excel file, I would use SSIS to pull the data in instead of using an update statement to update existing prices. I would dump the data from Excel to a table then swap the partitions at the point of price change. You could even schedule a job to do the partition swap so you have minimal downtime and to reduce the chance of human error.
A good read on this is available here: https://littlekendra.com/2017/01/19/why-you-should-switch-in-staging-tables-instead-of-renaming/

As long as you know the number of columns in the exel file AND you have some sort of control over it to ensure the columns don't change without your knowledge which would cause the SSIS package to fail, you should be good to go.
Steve makes a good point about using SKU's though. I agree with him about using SKU's for the bottle sizes. What if in the future you have 2 different 1 liter bottles (for example a red one and a blue one)? A SKU based tracking system gets around that problem.
So I would use method 2 that you had indicated above, but have the 3 tables broken into 2 (as the quantity mapping table is just a waste). Have an "item" table which would contain an ID column, a SKU column, a size column and a description column. The description could be presented on the web site as well as the size and the SKU. All pulled from 1 table.
Then have a pricing table that is potentially 3 columns: ItemID, quantity, price. These columns are pulled in using SSIS and the excel file provided to you.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 11

just a thought......

CREATE TABLE #ProductPrice( ProdId VARCHAR(6) NOT NULL ,BasePrice INTEGER NOT NULL ,Pricegroup VARCHAR(3) NOT NULL ); INSERT INTO #ProductPrice(ProdId,BasePrice,Pricegroup) VALUES ('BW001',10,'PG1'),('BW002',20,'PG1'),('BW003',30,'PG1') ,('BW004',40,'PG1'),('JLS001',100,'PG2'),('JLS002',120,'PG2');

CREATE TABLE #PriceGroup( PG VARCHAR(3) NOT NULL ,MinQty INTEGER NOT NULL ,MaxQty INTEGER NOT NULL ,Discount_factor NUMERIC(5,2) NOT NULL ); INSERT INTO #PriceGroup(PG,MinQty,MaxQty,Discount_factor) VALUES ('PG1',1,1,1),('PG1',2,2,0.95),('PG1',3,3,0.9),('PG1',4,10,0.85) ,('PG2',1,1,1),('PG2',2,5,0.8),('PG2',6,10,0.7);

CREATE TABLE #Sales( ProdId VARCHAR(6) NOT NULL ,SalesQty INTEGER NOT NULL ); INSERT INTO #Sales(ProdId,SalesQty) VALUES ('BW001',1),('BW002',1),('BW003',1),('BW004',1),('JLS001',1) ,('JLS002',1),('BW001',2),('BW002',2),('BW003',2),('BW004',2) ,('JLS001',2),('JLS002',2),('BW001',3),('BW002',3),('BW003',3) ,('BW004',3),('JLS001',3),('JLS002',4),('BW001',4),('BW002',4) ,('BW003',4),('BW004',4),('JLS001',7),('JLS002',8),('JLS002',20);

SELECT s.ProdId, s.SalesQty, pp.BasePrice, pp.Pricegroup, pg.Discount_factor, pp.BasePrice * pg.Discount_factor AS salesprice, s.SalesQty * pp.BasePrice * pg.Discount_factor AS TotalLineValue FROM #Sales AS s LEFT OUTER JOIN #ProductPrice AS pp ON s.ProdId = pp.ProdId LEFT OUTER JOIN #PriceGroup AS pg ON pp.Pricegroup = pg.PG AND s.SalesQty >= pg.MinQty AND s.SalesQty <= pg.MaxQty ORDER BY s.ProdId, s.SalesQty;

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Sergiy SSC Guru Points: 110208 More actions · Answer 12

There might be more factors than just a number of bottles which could define a price.
Not to mention - price must not be defined "per bottle", but "per pack".
If you try to record a "per bottle price" for $2.00 per 6 bottles than you'll always have rounding errors.

So, solution must be:
ProductID,
PackageID,
PricePerPackage

_____________
Code for TallyGenerator

sgmunson SSC Guru Points: 110640 More actions · Answer 13

Sergiy - Wednesday, July 5, 2017 5:42 AM
There might be more factors than just a number of bottles which could define a price.
Not to mention - price must not be defined "per bottle", but "per pack".
If you try to record a "per bottle price" for $2.00 per 6 bottles than you'll always have rounding errors.
So, solution must be:
ProductID,
PackageID,
PricePerPackage

Exactly why I previously suggested that each distinct unit that's available for sale have a SKU, so that a price can be associated with the SKU, and discounts of any kind can instead be handled at the invoice level and potentially based on historical customer purchase volume or just current order quantity.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)