Database Modeling: Product with 1 or more categories

I'm working on a database design that allows for a product to be in 1 or more categories. A category can be in a parent-child relationship. Here's what I have so far:

==================
product
==================
product_id
name

==================
category
==================
category_id
parent_category_id
level
name

==================
product_category
==================
product_id
category_id (leaf node only)

Questions

1. Does the design look good?
2. Am I right to only be concerned with the leaf node in product_category->category_id? (I suppose I can work my way from the leaf node up to the root node with this information)
3. Given a product, how would I get the category tree(s) that product belongs to?
4. Given a category (any level in the category tree), how would 开发者_Python百科I get the number of products categorized under it?
5. Any other queries I need to look into?

What you have presented looks like a correct design for a tree in the database. However, your queries may become complicated and you can find yourself requiring many queries for some tasks, like one for every level when traversing the depth.

However, there also exists a totally different approach for tree implementation in databases. It's kind of counterintuitive, but when you look at the benefits of the query simplicity, it becomes obvious that there is a big set of advanages.

Have a read: http://articles.sitepoint.com/article/hierarchical-data-database

Also, you could be better off if you use an ORM like Doctrine to implement the tree for you, along with the operations.

Your design is ok, as the others have said already. Just an idea:
It might be possible, depending on the business you are modelling, to represent the hierarchical structure of the categories in a simpler way. E.g:

CategoryId    Name
==========    ==========
1             Food
11            Chocolate
12            Dairy
121           Yoghurt
2             Grocery
etc...

If this is usable in your domain, I think it could make your design easier.

1. Yes - a many to many intersection table is correct (a product can be in more than one category, a category has more than one product).

2. I would agree on only having the leaf here as well. I'm expecting that primarilly you want to display products in their specific category, but would have subqueries that roll up to the parent, at which point you're probably already hitting the category table anyway for the name, etc. - Rule of thumb, start with denormalization and normalize as needed for performance.

3. An inner join on the product_category table (let me know if you want to see the SQL).

4. Given a category (any level in the category tree), how would I get the number of products categorized under it? (Easily accomplished with a join and a count - same note as above).

5. Likely you'll uncover more queries as you move forward, but that's the beauty of emergent design :)