A simple way is to structure the table, for example:
Categories
CategoryID
Parentid
Name
Your data will look like this:
1, 0, "Desktop and Mobile Applications"
2, 1, "Android Application"
3, 2, "Games"
4, 3, "Action"
5, 1, "Games"
6, 5, "Action"
7, 5, "Adventure"
8, 0, "Desktop Applications"
9, 8, "Games"
You would request it as: select * from Categories where ParentId = 1 , which will return Android applications and games. To get subcategories of games, you would do select * from Categories where ParentId = 5 , which will return the action and adventure.
Update To associate one item with several categories, you will need another table:
xref_CategoriesItems
CategoryId
ItemId
This will allow you to associate any single item with multiple categories. Let's say you have a desktop application that should appear in desktop applications> Games and desktop and mobile applications> Games.
Your table will have the following data for item 1:
3, 1
9, 1
When you see which items were in a particular category, you should do the following:
select I.* from items I inner join xref_CategoriesItems XCI on (XCI.ItemId = I.ItemID) WHERE (XCI.Category = @CategoryId)
To find out which categories a particular item belongs to:
select C.* from categories C inner join xref_CategoriesItems XCI on (XCI.CategoryId = C.CategoryId) where (XCI.ItemId = @ItemId)
Querying for all items in a particular category is a little more complicated if you need all the child records. Basically you need to do a recursive join to xref_categories with categories in order to get children. I do not remember how to express this in the MySQL version of sql; however, it may be useful to know the following: Using a MySQL query to move rows to create a recursive tree