Items from multiple categories best practices

Question

Hi,

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 slove 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
)

Answer 2

if an item can be in multiple categories, then these are not 'categories'. they're more like tags.

use many-to-many relationships between your items and your tags. if you want a hierarchy of tags, go for it; but it's usually overkill and very seldom add value.

Answer 3

Typically, the best solution I've found that works in most situations is simply using a parent_id field in your categories table. That way you will only need one categories table. This will support any number of levels or just one if you want (in which case, the ability to create a third level of category would need to be prevented in your code).

For example:
CREATE TABLE category ( id INT NOT NULL AUTO_INCREMENT, parent_id INT NOT NULL DEFAULT '0' name VARCHAR(255) NOT NULL ) It's simpler that way. Any category with a parent_id of 0 will be your top-most level. If you have a category whose id is 1 and you create a sub-category of that it's parent_id will be 1 etc.