help understanding sub-categories

jObE

Good day everyone,

I am trying to manage the downloads on my website by creating categories and storing them in mysql.
I have a basic understanding of relational tables but I can't wrap my brain around how to get the tables working.

I had the first table holding the main category names:

CREATE TABLE download_cats (
id tinyint(4) NOT NULL auto_increment,
name varchar(20) default NULL,
description text,
PRIMARY KEY (id)
) TYPE=MyISAM;

then a table with sub category names:

CREATE TABLE download_sub_cats (
id tinyint(4) NOT NULL auto_increment,
cat_id tinyint(4) default NULL,
name varchar(20) default NULL,
description text,
tablename varchar(20) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;

I need to make it so i can have subs of the subs :-)
I couldn't figure out how to create it because not all will have a multiple subs ( or some more then others ) so I was just gonna link to a tablename and display everything from there.
If someone can just explain how to organize the tables I would be for ever in your debt.

Some sample of info:
MAPS - > COUNTERSTRIKE -> BOMB/DEFUSE

CONFIG FILES -> CLAN MEMBER CONFIGS

PROGRAMS -> HL RELATE

Thank You to anyone who can help.

planetsim

From what i see this would be more efficient in the one table.

Depending on how many levels you will have e.g.

Category
->Sub 1
-> Sub Sub 1
-> Sub Sub Sub 1

As you can see thats 4 levels i try to limit to 3levels 4 is too many.

So something like this would work

id|parentid|subparentid|name|description

parentid being the sub1 category id subparentid being the sub1 category id.

From there its basically simple logic, working out what is a category, sub category or sub sub category. To do that also you will need an identified so e.g you have a category but want to make sure its identified as a category and not a sub or sub sub.

might look like so
1|-1|-1|category|this is a category

As you can see -1 is the identified which singles that its a category a sub sub may look like this

3|2|1|sub sub category|this is a sub sub category

Roger_Ramjet

The process wherby you analyse your data and define what tables are needed is called NORMALIZATION. It is very easy to do once you understand it, and table designs follow automatically from the analysis. Here is a link to get you started:

http://www.databasejournal.com/sqletc/article.php/1428511

Be aware it is also very easy to loose data in a relational database if you do not normalize your data properly and design tables accordingly, and even easier to get the wrong results when you query the database. Do not be put off breaking your data out into multiple tables because of the joins that will be needed to access it. That is what the indexes/keys columns are for.

jObE

Thanks for the help guys. I'll checkout maybe putting it all in one table and read that article and see if I can't get a grasp on this.

Thanks again guys.