Hi All,

As I'm re-designing my site, I thought now would be a good time to filter out and change all the 'hacks' I used in the old site. One of these I'm having difficulty finding a 'clean' way around.

I have the usual product DB, ID, Title, Cost, Description, Category, etc. Category is a lookup coloum to a simple list of categories.

This all works well, but a couple of categories contain items that are nearly all the same, but one field changes (for example, Chemicals - Strontium Carbonate 500g and Strontium Carbonate 1Kg are the same product and should be displayed on the same page, but the Weight should be variable. As with Mesh - 10 Mesh 4x4", 10 Mesh 6x6" and 10 Mesh 12x12" are the same)

If you're lost already, please see my site (http://www.inoxia.co.uk) and look at either chemicals or mesh.

Currently, I have the products listed seperatly in the DB, and make sure all other info (Title, Description, etc) is the same. To list the products, I use SELECT DISTINCT so the items don't repeat in the category listing, then pass in the title of the product to the detail page so I can use SELECT * FROM tbl_Producst WHERE itm_Title = Blah.

This works fine, but is a real hack and a bit of a mess - seeing as loads of info is repeted in the database, and I have to have 2 product listing pages and 2 product detail pages, etc, etc.

Can anybody think of a better way to do this? It's been driving me crazy for days...

What about adding a new table say, product_sizes(int pid, varchar(n) text) which would contain records of the form:

+---+----------------+
|pid|text            |
+---+----------------+
| 11| 500 g
| 11| 1Kg
| 11| 1.5Kg
| 24| 4x4"
| 24| 6x6"
| 24| 12x12"
+---+----------------+

That way you have products in one table, so you can easily produce your category listing, then once the product is selected you can lookup the different sizes it can have in the product_sizes table.

I have to have 2 product listing pages and 2 product detail pages

Why do you need 2 of each?