Need Help Structuring

MattG

I've got a database which lists a load of products it works fine except when I have a product which fits into more than one category. I don't want to add multiple entries of the same product just with a different category every time as that would result in more duplicate records than products.

Can anyone suggest a way in which I can add a product to a database but have it fit multiple categories? I was thinking a standard text box and then explode the field in the record but I figure that there must be a better way than that.

Any help would be hugely appreciated

nicdp

Create:
- 1 table for your products
- 1 table for your categories
- 1 table for the relations between products and categories

Then use JOIN SINTAX in your select query.

MattG

OK, I already had seperate tables for products and categories, would the relations table be something like:

id - to link the records
cat1 - "y/n"
cat2 - "y/n"
cat3 - "y/n"
cat4 - "y/n"

and then use the JOIN SYNTAX function to get the results or should the relations table be set out differently?

nicdp

The relations table has to be like this:
- product_id
- category_id

Then if you want to get e.g. products of category 1...

$table_products = "tablename"
$table_relations = "tablename"
$query ="SELECT $table_products.* FROM $table_products LEFT JOIN $table_relations ON $table_products.id = $table_relations.product_id WHERE $table_relations.category_id=1";

nicdp

So.. if you want to put a product (let's say with id=15) in categories 1 and 5 you insert two rows in relations table:
1. product_id = 15 ; category_id = 1;
2. product_id = 15 ; category_id = 5;

MattG

Ah! Excellent! Thanks very much for your help.