data structure & query for volume pricing

[aunquarra]

It has been a few years since I was here last, but you guys were always so helpful, and now I'm stuck again. It's a bit more of a complex bit of logic than I used to have... Oh, and this is MySQL 5.0.77 (Rackspace Cloud hosting, currently). On to the problem.

 

It's not built, yet. Or at least the new version isn't. The old version was junk, hence the rebuild. At any rate, that means I don't actually have table structures, queries, or anything.

 

The idea is that I've got a set of specific products sold by a variety of sellers whose price is determined by a variety of factors. For the most part, I've got that covered already, even the seller-specific stuff. The one issue I keep coming back to is the fact that each seller can set up their own volume pricing.

 

So if someone is looking to buy 1 of this product, they fall into the first volume pricing tier for each seller, but if they want to buy 100, it might be the fifth tier (90-100) for Seller A but the second tier (50+) for Seller Z, and each would have their own adjustments to the price according to the actual volume break point. Standard stuff, but I've always done the legwork in PHP. For a variety of reasons, I need to try to keep this in MySQL.

 

I know this is vague and conceptual, but I'm at the project design point here, so that's all I have. The last time we built it, we did it in a very "slapped-together" fashion, forced to use a lot of external, unoptimized data, and we used a set of subselects to do the work (plus, I didn't actually do it; it was another guy who has since left). The EXPLAIN on that made me shudder. As I said before, "hence the rebuild."

 

Any suggestions on how I might structure/query the database to be best-optimized for this? Conceptual and pseudocode is more than sufficient for me. I really just need a starting point.

 

Thanks, all.

[kickstart]

Hi

 

Quick play and I would have a table of suppliers, a table of products and a table that lists all the product prices for all the suppliers with a high and low number of items for that price.

 

Then you can join the tables together and check that the number required is BETWEEN the lower and upper items limits.

 

For example, after a quick play:-

 

CREATE TABLE IF NOT EXISTS `items` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `ItemName` varchar(50) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

CREATE TABLE IF NOT EXISTS `prices` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `SupplierId` int(11) NOT NULL,
  `ItemId` int(11) NOT NULL,
  `LowPriceLimit` int(11) NOT NULL,
  `HighPriceLimit` int(11) NOT NULL,
  `Price` int(11) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=62 ;

CREATE TABLE IF NOT EXISTS `suppliers` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `SupplierName` varchar(50) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

 

Then to get all the prices of all the items from all the suppliers where 5 are wanted:-

 

SELECT *
FROM suppliers a
INNER JOIN prices b ON a.Id = b.SupplierId
INNER JOIN items c ON b.ItemId = c.Id
WHERE 5 BETWEEN b.LowPriceLimit AND b.HighPriceLimit

 

All the best

 

Keith

[aunquarra]

Interesting. The only issue I see is that this wouldn't support a many-to-many scenario because the suppliers are the initial point rather than the products.

 

The end result I'd be going for would be a list of all products/suppliers combinations (with some WHERE filtering, but that's irrelevant at this stage) with the price reflecting the user supplied volume. So, the same supplier might be on the list multiple times (if they sell 4 different products), and one product might be on the list multiple times (if it is sold by multiple suppliers).

 

Go figure that I didn't do due diligence in the initial explanation.

[kickstart]

Hi

 

As it is inner joins it wouldn't matter. The SQL above could easily bring back the same supplier multiple times and the same product back once for each supplier.

 

Only real issue is ensuring that the quantity ranges do not overlap for any single product for a single supplier.

 

All the best

 

Keith