SQL table design advice

Question

Hey i want to create a list like this. The pic below isnt my list just an example of what i want to create.

My category table is currently just:

category( id int, title varchar(20) )

Should i just keep everything in one table or add a section table to deal with each category section?

Answer 1

From what I see (Indoor and Outdoor subcategories in Sports category), you'll also need parent_category_id in your table.

As for the original question, it depends. I'd go solely with categories, without sections: this will make things more uniform.

Answer 2

Perhaps you should add a field 'subcategory_of' pointing to the main category, and if it's empty you can use it as a main category

Answer 3

I would add a Parent_ID to the table which links to the same table's ID field, but is NULL-able. Then, when you run through each category on the PHP side, look up sub-categories where the Parent_ID = $current_id. In order to get the root categories (uppermost), look up those where the Parent_ID = NULL.

This way you can make a recursive loop on the PHP side.

Here's a rough idea on how you can make a recursive function.

function showCategories($parent_id = 0, $level = 0) {
    // If $parent_id == 0, look up categories with Parent_ID = NULL, otherwise by $parent_id.
    // For each category, show link and run showCategories() with the category's ID as $parent_id.
    // Throw in ++$level as the level, and do --$level at the end of each iteration.
    // Use $level to indicate how deep down the tree you are.
}

NOTE: The Parent_ID field must be NULL-able because otherwise you cannot indicate a top-most category, since the Parent_ID must exist as an ID (assuming you properly link your tables).

Hope it helps!

Answer 4

I think the answer is: it depends.

If it's likely that you will grow the amount of subcategories over time, you would make one table, with a parent_id as suggested, and probably cache the tree structure display generation, as you need to either get all out categories and build the correct tree in your code, or do some recursion with sql calls.

If you know it will never grow more than x amount of subcategories, you could "hard code" to x amount of tables representing root_categories, sub_categories, sub_sub_Categories (each with parent_id).

You could of course also pre-generate the tree-structures when altering the categories (if you have some way of moving them around etc.) so that you have only one flat table with categories, and fetch the pre-generated trees when needed to display a tree or breadcrumb trail style navigation.

This issue is similar to solving threaded forum issues, so you might look into people solving that in a good way btw.

Answer 5

Something like this

category( id int, title varchar(20) )
subcategory (id int, id_category int, title varchar(20) ) 

id_category will hold value of category.id

or a hierarchy table that will have id of parent and his child, this way you will have infinite levels.

 category( id int, title varchar(20) )
 category_hierarchy(id_parent int, id_child int)