MYSQL table organization

[Msword]

What would be the best way to go about having categories in mysql. I would like to have 1 table, and each item will have an id number. I want the items to go into a main catagory, and one subcatagory. How could this be achieved easily?

 

Example Item: Ice Breath

Main catagory: Attacks

SubCatagory: Elemental

 

(I just came up with this, and has nothing to do with my project, so don't say anything specific to this please )

[Crew-Portal]

Everything on a website can be displayed in one database. Each table holds diffrent data such a on table could hold users. this users table could hold id, username, password, ip, date signed up, ect... another could hold a forum such as display topics, display name, display id ect... This is the most default way of running mySQL and in my opinion the best way! Hope that helps?

[Philip]

With just one table?

 

uniqueID | catID | subcatID | itemID | title | description | etc...

 

Then some examples:

1 | 1 | 0 | 0 | Attacks | attack thy enemy! | ... (category)

2 | 1 | 1 | 0 | Elemental | an element feature | ... (sub-cat)

3 | 1 | 1 | 1 | Ice Breath | Icy cold | ... (item)

4 | 2 | 0 | 0 | Defense | stand true soldier | ... (category)

5 | 2 | 1 | 0 | Physical | get involved | .. (sub-cat)

6 | 2 | 1 | 1 | Blockage | dont get hit | ... (item)

7 | 2 | 1 | 2 | Duck | miss that swing | ... (item)

8 | 2 | 2 | 0 | Verbal | yell at him | ... (sub-cat)

9 | 2 | 2 | 1 | Intimidate | make him fear | ... (item)

 

 

There's probably a better way, but that's just a suggestion.

 

edit: I guess it would be better to explain it a little too:

You could easily run it to where if(subID == 0), then it's a category, if((subID != 0) && (itemID == 0)) then its a sub category, if(itemID != 0) its an item. Then finding the sub categories for each category would run a query like ("SELECT * FROM `tablename` WHERE `catID`='".$catID."' and `subID`!='0'") etc.