CODEWITHSUNDEEP
phpmysqlsql
jtheman
jtheman

Reputation: 7491

Most efficient hierarchical product table

I've built a few product databases but I'm never really satisfied with the effiency of the database. My best approach yet is to have one products table, one category table and then one relationship table ie:

Products table:

id    product_name     data
1     Some prod        More info...
2     Another prod     Even more...

Category table:

id    parent_id   cat_name
1     0           Main cat 1
2     1           Sub cat 1
3     1           Sub cat 2
4     3           Sub sub cat 1
5     0           Main cat 2

Relationship table:

id    prod_id    cat_id
1     1          2
2     1          4
3     2          5
etcetera...

This makes it fast and easy to retrieve the products and also easy to have one product assigned to more than one category.

However the structure for creating the category listing is not as simple as I'd like. First I need to loop the main category, then all the sublevels accordingly. I only like to show categories that has products assigned, but then of course I also need to show all the parent categories if a sublevel contains products. This results in plenty of queries, joins and conditional routines to present the current categories. I wonder if there might be a more efficient structural approach to this problem? You don't need to write my code I just wonder what kind of better principle there might be?

Upvotes: 0

Views: 230

Answers (2)

Mihai Stancu
Mihai Stancu

Reputation: 16117

The typical recursive approaches are the adjacency tables and the many-to-many tables.

The typical non-recursive approaches are many-to-many ancestor tables and nested sets.

Adjancency lists are those structures that contain a "parent_id" reference.

Many-to-many tables being read recursively are your approach.

Ancestor tables are many-to-many tables but they also contain child-grandfather connections and specify the level of each connection. They allow the most flexibility and the fastest read/write speed.

Nested sets are a very different approach, they only allow strict tree structures not graphs. They are also more costly on writes but very easy on reads.

Also regarding nested sets, maintaining the structures manually is pretty hard. And you need to implement several functions and choose when to use each, because inserting a node at the end of the child-list needs one function (appendNodeTo($parentNode)) while inserting a node at the middle of the child-list needs another. Moving nodes around depends on weather the node is a terminal nod (a leaf) or if it has subnodes (a branch) with specific functions for that too.

Upvotes: 2

This_is_me
This_is_me

Reputation: 918

this is not the most efficient way but this way you only need 1 sql request(query)

 public function get_menu_data()
    {
        $result = mysql_query(" 
            SELECT 
                id, parent, name 
            FROM 
                category 
            ORDER BY 
                parent, name 
        "); 
        //$cat = $this->db->get("category");
        $menuData = array( 
            'items' => array(), 
            'parents' => array() 
        ); 

        while ($menuItem = mysql_fetch_assoc($result)) 
        { 
           $menuData['items'][$menuItem['id']] = $menuItem; 
           $menuData['parents'][$menuItem['parent']][] = $menuItem['id']; 
        } 
        return $menuData;

    }

function buildMenu($parentId, $menuData)
{
    $html = '';
    if (isset($menuData['parents'][$parentId]))
    {
        $html = '<ul>';
        foreach ($menuData['parents'][$parentId] as $itemId)
        {
            $html .= '<li>' . $menuData['items'][$itemId]['name'];
            // find childitems recursively
            $html .= $this -> buildMenu($itemId, $menuData);
            $html .= '</li>';
        }
        $html .= '</ul>';
    }
    return $html;
}

call it like: show all categories:

buildMenu(0,get_menu_data());

show sub categories of categorie 1:

buildMenu(1,get_menu_data());

good luck and i hope this code helps you

Upvotes: 1

Related Questions