How should you deal with data for multiple prices for a single product?

Question

Each product in my database can have at least three and sometimes four or five different prices, and which one is displayed is based on several factors. How should I attempt to tackle this scenario?

Each product can have the following prices:

• List Price (MSRP)
• Cost (what we pay for it)
• Retail Price - shown on "main" site
• Government price (our primary customer is the U.S. Government) - only shown on our government subdmain
• Sale Price (valid for 1 month, but extended every month) - only shown on our government subdomain
• BPA Price (special price for BPAs) - not shown but loaded onto government sites

The list price is provided in the vendor's quarterly data file and kept in the Products table, since it's a finite part of a product entity. How should I handle the other prices though?

I'm thinking the best (only?) course of action is to have a separate table with the different price structure as well as SaleStartDate and SaleEndDate columns that I can check to see whether or not to display the sale price. In fact, I'm not sure of any other way to handle this.

Also, I will need to have a duplicate of this (different prices, but same cost) as our company does the processing (all the work, really) for a different company that's in the same business as us; the products are identical but customers/orders/specific prices are different. The current implementation has a duplicate database with everything repeated (same with the code) and I want to avoid this like the plague so I'm trying to come up with a way to abstract the common things out.

EDIT (2/17/2009 @ 12:57PM): Except for the list price, the prices are all calculated based off the cost, however it's not always that easy; sometimes items need to have their prices manually changed independently of the margin, however this is handled manually via Excel and only once per quarter (3 months); when the data is loaded into the database the price is finite and isn't changed until the following quarter; the "sale" price is on perpetually forever, but it expires (not on our site, but on several government sites like GSA Advantage) and needs to be extended; it doesn't change though when it's extended. The sale price is just to have our own site in sync with GSA Advantage, so if a government customer visits our site they'll see identical pricing as they would on Advantage.

RE: The other company, that might be a way to go, although it would be insanely annoying to have to do everything multiple times; the products are exactly the same, the cost is exactly the same (we basically do everything for them, it's another company in name only and the fact they have different customers than we do), but their prices use a different markup than ours. Everything else is identical, which is why I was hoping to keep stuff like the product information in one table, and then keep the differences in separate tables. The current system is a mess largely because everything (database, code files, images, everything) is duplicated, so refactoring the code in our website requires an identical change made to the other website.

EDIT (2/17/2009 @ 4:52PM): Some very good ideas so far. One of the issues is that, apart from reporting purposes and reference, only one of the price columns is ever the "real" one. For example, the government price is always shown to government customers, but the sale price is shown instead if the current date falls during the sale date ("sale" pricing is specific to government customers). If the customer is ordering for a BPA (this will be determined by our order processing system and/or manual override by a user), the price is automatically the BPA price. The list price might be shown to display the discount you are getting, and it's required for reports, but nothing else.

Also, I'm unsure of how badly normalizing things will make updates. Right now, I'm just handed a spreadsheet with new pricing and told to update the database (since they're all in the products table); I do this based on the product's SKU since the ID is internal only and all of the related tables are linked via the SKU. With a separate price table it becomes even more unwieldy, although I could still use the SKU as the reference (it's going to be a unique index for a product) but a lot more would have to be done manually.

Answer 1

In MS SQL or Oracle:

Create a table prices:

CREATE TABLE prices (product, pricetype, price, startdate, enddate)

Query:

SELECT product, price, pricetype
FROM (
  SELECT products.* ,
         prices.*
         ROW_NUMBER() OVER (PARTITION BY product, pricetype ORDER BY startdate) rn
  FROM products, prices
  WHERE prices.product = product.id
     AND startdate <= @date
 )
WHERE rn = 1
AND enddate >= @date

This will give you all prices of all types that are actual for given @date

Answer 2

• My first question is, can all of the prices be calculated from one of the prices? If yes and the rules are the same across products, we're in a somewhat easier place.

• Do any of the prices change independently of other prices? You have hinted that the Sale Price is valid for 1 month and then extended - does the price remain the same or does it change? If it stays the same, this is easier than if it changes.

You're on the right lines with having a start and end date set for product prices as this will be fundamental for historical price reporting.

EDIT:

I was thinking that you could use persisted computed columns for the other prices based on the cost price, but since the prices can be manually adjusted this isn't an option (I don't believe you can override p.c.c. values). You could write a stored procedure to insert the initial prices into a PRICE table based on cost price.

Based on the info so far, I think you're best option would be to have a separate PRICE table from your PRODUCT table and a product_id foreign key in your PRICE table referencing a primary key id of the PRODUCT table -

PRODUCT table

id | name | image | description | etc...

PRICE table

id | product_id | list_price | cost_price | retail_price | gov_price | sale_price | bpa_price | start_date | end_date

It is prudent to have the start and end date fields on your pricing because not only can you historically report prices, but also populate the table with future prices.

With the above structure, now when you have a price change, you would need to insert a new record in the PRICE table for the product. Set up indexes on start and end dates, then you would query products and prices as follows

SELECT 
product.name,
price.list_price,
price.cost_price,
price.retail_price /*, ETC... */
FROM
product
INNER JOIN
price
ON product.id = price.product_id
WHERE
price.start_date <= @date
AND price.end_date >= @date

You could normalize this design further and also have a PRICE_TYPE table. The thing to bear in mind with taking this approach however, is that if you want to get the full set of prices for a product, then the WHERE clause is applied to 6 records for each product.

To handle the other company situation, provided the proper permissions and restrictions are put in place, I see few * problems with storing their prices in the same database. You say they use different markup - are you referring to their prices? If so, you could handle this with a company_id within the price table. Access to data could be controlled through stored procedures and updating prices through transactions.

*it depends on how closely coupled the work is between companies. Is it permissible to share resources?

(N.B. I've made the assumption that your target database is SQL Server, but I would imagine the logic to be similar for other platforms).

Answer 3

From my perspective I would have an Item table: with List Price(MSRP), Cost, Retail Price, Government Price, and BPA price

And

Sales table: with Sale Price, Start Date, End Date and obviously a link to the item table.

As far as the other company stuff, I would think you would want that to be a separate database all together. I would not want to mix my data for my site with another sites. Even if they are duplicates. I know it is a pain to always have to do the same thing twice, but it seems like they are separate systems, and should be treated as such.

Just my two cents.