graeme_p - 5:19 am on Sep 7, 2011 (gmt 0)

Firstly, you should not have problems with a 50,000 row database unless you have VERY high performance requirements. Is there really a problem?

You also need to be careful not to lose important functionality. The current database probably has a unique index on the product ID column to prevent duplicates, you meed a size type field and a unique index on both columns. If all the dimensions are required for each product then that will be harder to enforce.

Duplicating information is bad design, and should only be done when necessary ("denormalisation").

What you could try is five tables (a product table and four dimensions table, each with a foreign key on product) or two tables (product table and dimensions, each row has a foreign key on product and a dimension type of small/large/etc.), or two tables with a single product row in each.

The best option will depend on:

1) What RDBMS you use: for example MySQL with MyIASM tables does not enforce foreign keys
2) Whether the database is read heavy or has a lot of writes: adding indexes make reading faster and writing slower.
3) Whether you need to be able to query by all rows, or whether some are just looked up as necessary.