Here is my design dilemma:

I have a table that contains products for each supplier.

| supplierid | prodid | price_each | price_case |

This means that for every new supplier I add, I have to add records for all the products available to this table. I intend to have around 10 suppliers and probably around 500 products, so around 5000 records for option 1.

The other option is to have this table structure:

| productid | Supplier1_price_each | Supplier1_price_case | Supplier2_price_each | Supplier2_price_case | Supplier........ and so on till nth supplier.

This means 500 products but 2 columns for each supplier.

Which is better for both performance and design?

The first option has more overheads that have to be covered in my php code to produce the extra rows to accommadate the new supplier as it is added.

The second option only requires me to add 2 columns every time a new supplier is added.

Not sure which option is best.

please do not post the same question in multiple forums

http://www.dbforums.com/t1022844.html