Matrix Manipulation for MySQL

I'm currently working on a quote retrieval system for my employer's website using mysql/php/html. They currently use a pricing matrix in the form of a two way table with height and width along each axis in a spreadsheet that lets you look up price.

My question is what's the best way of manipulating this matrix into a database table usable by mysql? Since the pricing 开发者_如何转开发grids they use are up to 19x17 I realise that I would need a 3 column table (width, height, price) with 323 (19x17) rows for every permutation of the available heights and widths and it's corresponding price, but is there a less longwinded way to achieve this without manually entering each possible width and height combination?

Thanks

EDIT: an example of the grid is on the left and the format I need it in is on the right http://img163.imageshack.us/img163/406/82853790.jpg

---

First you need the data from the old matrix, by SELECT column[0], column[1] from oldMatrix and copy the result to a spreadsheet..

I think you have a mess on the first table, the second table ok [based on the image you posted]

Try SELECT column[0] where column[0]=500 AND column[1]=700 after that copy the result to a spreadsheet and create the newmatrix (table).

Good Luck.

---

You can use -

Width table - details of width (id, desc, etc)

Height table - details of height (id, desc, etc)

Price table - Foreign keys to both Width and Height table keys and corresponding price.

Now, to insert your data into the database directly without manually entering each width and height combination by importing data from an excel sheet to a temporary table and writing appropriate sql scripts to insert data to height, width and price tables.

---

why the ID on your new width_height_price table ?? it's a redundant surrogate key when a composite key would better enforce integrity.

drop table width_height_price;
create table width_height_price
(
 width smallint unsigned not null,
 height smallint unsigned not null,
 price decimal(8,2) not null,
 primary key (width, height) -- clustered composite PK 
)
engine=innodb;

what stops you adding (700,500) and (700,500) again as you'll just generate a new row with a new ID !!

Let the DB do the work :P