Modelling a price matrix table in MySQL

Question

Hello good people of stackoverflow,

I am trying to create an ecommerce product that takes orders for print jobs. I would like to create tables such as the ones here on solopress for example http://www.solopress.com/leaflet-printing/leaflets.html. I am just struggling a little with the logical steps of modelling this in a database.

Firstly how would I go about creating a table like that where the column options could be configured differently per product in a database without having to create an actual database table each time.

Secondly then what would be the best way to work it so that the prices change as in the example above, when you change between Unfolded and Folded - would this need to be two independent tables that get switched?

Thank you for any advice you can offer to kick my little brain into gear.

Answer 1

For storing the prices I'd structure the table like this:

size enum('A3','A4','A5','A6','DL'),
min_qty INT,
sides ENUM('1','2'),
folded BOOL,
price DECIMAL(6,2)

with your primary key being size+min_qty+sides+folded. Generating the webpage will involve PHP or similar and updating the price as options are chosen will involve javascript. If you have specific questions about those they should probably be asked separately.