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 otion is best.

the first, hands down

example 1: what happens in each design if you stop doing business with supplier 127?

example 2: how do you count in each design how many suppliers supply product X?

Agreed with Rudy 100% (of course). You should get yourself a book on relational database design and look up "repeating groups".

But I'm intrigued: how could adding 2 new columns to the table for a new supplier not have any impact on your php code?