category and products

[searain]

For example, in my product catalog database. I will have two tables, category and products.

the simple and efficient design is that

1. category is parent table.
2. product is child table.
3. category_id will be the key for category and will be the foreign key in product.

here is my problem.

i may have same product assinged to two or more categories.

so in the above design, i will have to have the same product appears twice or more in the product table, one belongs to each category it is assigned to. and the product should be unique in the table. so for the same product, in order to following the rules, i may have to assign the same product with different product_id or different product_code.

that is not good practice.

so here comes another design.

1. category is parent table.
2. product is parent table.
3. category_and_product_link is child table, has the category_id and product_id as the foreign keys.

this way, when i want to assign the product to two or more categories, i just need to create two or more pairs of (category_id, product_id) in the category_and_product_link table.

this design solves the problem. but this design is adding complexity into the whole thing. specially, if you have more layes such as category->sub-category->sub-sub-cateogry->products. and more relations with other tables,

My questions are:

1. what is your suggestion? use the second design or use the first design and elimiate the possibility that product could be assigned to different categories (if we have to, create another product record.)

which design will be general accept design?

2. any other better solution?

[ahundiak]

I'd go with the second design. Using an separate table to implement many-to-many relations is more or less standard for SQL databases. And it's really not that hard.

[jsim]

Surely second desing.
It's design followed by database normalization rules.
There is much more headache you will get with the first one.
Another table contains relations between categories and products is not too hard to use as you think. You just need not to forget delete entries from 2 tables instead 1 if you delete some product.

[searain]

thanks!

--

You just need not to forget delete entries from 2 tables instead 1 if you delete some product.

my next question, should i enforce this data integrity issue by the database (if the database has the tools to do so) or by php application pages? what is your prefer?

[jsim]

I think it is more db-independent way is to delete it from php code.
MySQL does not have feature to delete from several tables at one query (as far as I know). Additionally, "delete from table where some_condition" is slow query itself (comparing to "delete from table"), so you do not get much benefit using db tools to do this...
So I reccomend you to execute 2 queries from php rather than using db-specific features - it's more portable.