How to organize this in a mysql table?

I have some items, each item has a category and a sub-category. What is the best way to organ开发者_Python百科ize this in mysql ?

Create a table

catID 
CategoryName 
ParentCategory ....any other fields

Parent Category column will store the catID of Parent category if any. or

CategoryTable
catID
CategoryName

CategoryRel Table
----------------------------------
ID
catID
SubcatID

where catID and subCatID are the IDs from category table.

First, you have a big decision to make. Will you design your database so that there are only two levels of category? Or will you allow for more than two levels in the future?

If you are willing to accept a permanent limit of category and sub-category, you can add two tables. Categories will list the allowed categories. Subcategories will list the allowed Subcategories and what Category they belong to. And your data table will include only the Subcategory. (If there is no information about a category other than its name, you could dispense with the Categories table and just include the category name in the Subcategories table.)

If you want to allow any number of levels, you will have only a single Categories table. Each row will have the category name and another column with the parent category. Your data table will still include a single category column.

categories
-----------
id
title
description

subcategories
-------------
id
title
description
category_id

items
-----------
id (*i had misspelled id as 'if'. corrected it here)
title
description
subcategory_id