I am developing a web application where I need to store some data about suppliers.

For each supplier, I need to store a matrix of true/false objects - product types down the side, and product grades along the top, so that for any product type and grade that the company supplies, a tick can be placed in a box to represent the product.

The product types and product grades are kept in other tables in the database.

I thought about having a separate field for each combination of product type and grade, however this would mean having nearly 150 fields and is not particularly expandable - ie if new product types or grades were added to the database then someone would have to go and create new fields in this table to accommodate them.

Any suggestions as to the best structure to use here would be most appreciated.

values (value)
tableA (name, value)

Example:

values
-------
1
2
3
4

tableA
--------

name1 1
name1 2
name1 3
name2 1
name2 2
etc.

Okay, for future reference, I've come up with this:

Have a supplier table with a numerical ID, and details of the suppliers.
Have a product form table with a numerical ID, and details of each type of product.
Have a grades table with a numerical ID, and details of each product grade available.

Then, have a Supply table with a supplyID, supplierID, productID, and gradeID. A row in this table would describe the position of ticks in that "matrix" I described while having its own unique ID number.

Thanks anyway.

In my opinion, a matrix-style table structure is not the way to do it. (I know that you don't plan on storing it that way... I'm not addressing you.)

The information should be stored in tables of simple lists: {this_product, this_grade, is_sold_by_this_supplier , for_this_price, etc...}

The information is then presented in a matrix of check boxes, and the user's inputs are then parsed and processed by procedural code. There is code to build the visual matrix, and more code to process the returned data when the user presses "OK."

Don't try to slough-off the work to the DBMS by means of a grid-structured table.

Totally agree. I knew this was bad and knew to avoid it.

A grid matrix violates first normal form: Eliminate repeating groups.

The structure that I posted, was not a grid matrix structure. Instead, it was a simple many-many.