MYSQL n elements matrix

Question

I am creating system with price calculator depending on n parameteres with n possible values.

It means that product can have:

• color: black or white
• size: large or small

so admin has got a matrix:

color size price
black small 10
black large 20
white small 15
white large 30
and so on.

But he should be able to add new dimension to the matrix, let's say: frame round or square.

So matrix would be:

black small square 10
black small round 50
black large square 20
black large round
white small square 15
white small round 30
And so on:)

All in database, not hardcoded.

I came to idea with three tables:

• Attributes (color, size, price etc.) with id and name columns
• Parameters (with attributes values, e.g. black, small etc.) with attribute id and value

And finally combinations table

CREATE TABLE IF NOT EXISTS `combination` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `combination_id` int(11) NOT NULL,
  `attr_id` int(11) NOT NULL,
  `parameteres_id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `attr_id` (`attr_id`),
  KEY `parameteres_id` (`parameteres_id`)
)

What I need is to get solution for finding price of specific combination, as I would have with fixed number of attributes, e.g.:

Combination_id size color shape price

With such example I just query for size=small AND color=black to get price of black small product.

Does anyone have any idea how to do this or maybe how to achieve it efficently on application side (PHP)? Or perhaps a better idea how to solve above problem with n elements?

Thank you in advance for any ideas.

Answer 1

This problem may be best approached with a structured text approach. For each product specification have a record including a 'Specification' column and a price column (as well as any other columns you need like part number, weight, packaging cost, skew number, serial number etc,). Specification could be populated with a structured 'description' e.g.

;BLACK;LARGE;ROUND;

You would need to ensure that the correct selection of criteria was then fed to the query retrieving data, but it could look something like this :-

SELECT Price FROM Products WHERE Specification LIKE '%;BLACK;%' AND Specification LIKE '%;LARGE;%' AND Specification LIKE '%;ROUND;%'

Notice that the Specification has an initial semi-colon as well as one after each attribute - That way each comparison expression simply has to start with a wildcard and a semi-colon, and end with a semi-colon and a wild card. It means you can specify any criteria in any order and it won't matter as long as you use the terms consistently, and you won't need to have lots of different tables for all the possible specification types that may or may not apply to each product. It also allows for improbable combinations of specification to be omitted without complications, e.g. you might well have ;NAVYBLUE;WOOL;SOCKS; but you are unlikely to have ;GREEN;STEEL;SOCKS;