Items from multiple categories best practices

Question

I'm making list of items in categories, problem is that item can be in multiple categories. What is your best practice to store items in categories and how list all items within category and its child categories? I am using Zend Framework and MySQL to solve this issue.

Thanks for your replies.

Sorry for my English :)

Answer 1

So, you have a hierarchy in the categories, yes? Is it one level (category and child category) or any number (children can have children, etc)? That will impact what the solution is.

Generally, you'd model something like this with a many-to-many relationship, like:

CREATE TABLE Item(
  item_id INT NOT NULL, 
  item_name VARCHAR(255) NOT NULL
)
CREATE TABLE Category(
  category_id INT NOT NULL, 
  category_name VARCHAR(255) NOT NULL
)
CREATE TABLE Item_Category(
  item_id INT NOT NULL REFERENCES Item(item_id), 
  category_id INT NOT NULL REFERENCES Category(category_id)
 )

A record in "Item_Category" means that the listed item is in the listed category. You can then do joins between 2 (or 3) of those tables to list which items are in which categories:

SELECT I.* 
  FROM Item I 
    INNER JOIN Item_Category IC ON I.item_id = IC.item_id 
    INNER JOIN Category C on IC.category_id = C.category_id 
  WHERE 
    C.category_name = 'MyCategory'

Or which categories an item has:

SELECT C.*
  FROM Category C
    INNER JOIN Item_Category IC.category_id = C.category_id 
    INNER JOIN Item I on IC.item_id = I.item_id
  WHERE 
    I.item_name = 'MyItem'

If there's a hierarchy in the categories, that could be expressed using a recursive relationship in the category table, such as:

CREATE TABLE Category(
  category_id INT NOT NULL, 
  category_name VARCHAR(255) NOT NULL,
  parent_category_id INT NOT NULL REFERENCES Category(category_id)
)

That makes things more complicated because you have to use recursive queries to get all the records for a category and its children. A simpler way, if you have only two levels of categories, it to simply make it a second field on the category table, like:

CREATE TABLE Category(
  category_id INT NOT NULL, 
  category_name VARCHAR(255) NOT NULL,
  subcategory_name VARCHAR(255) NULL
)