MySQL - Multiple level product database

Question

I am currently building a store based module for one of my customers. The items have many different types of categories that they can be entered into. An example I will use is a radio.

It can be a handheld or mobile type of radio. We have the radios listed under the following path:

store -> radios -> brand -> mobile / handheld -> item page

This is pretty simple to store if that was the same layout for everything, but alas it is not that easy. They may have an accessory that works for all the radios and brands, in which case it would be stored under like this:

store -> radios -> accessory -> item page

This is a complete category below the radios so writing the select would have to be different for every item group which is less than desirable. The items also should be accessible from the shared parent category, like if I were to visit store -> radios I should see all of the items under those sub categories. Here is the current database structure I have planned, which is where I am having the most issues I believe (P.S: the ID category is unique, primary, and auto increment):

+===================================================+
| ID | Category Name  | Path            | Parent ID |
+===================================================+
| 1  | radios         | radios          | 0         |
| 2  | accessory      | misc            | 1         |
| 3  | motorola       | motorola        | 1         |
| 4  | handheld       | handheld        | 3         |
| 5  | mobile         | mobile          | 3         |
+===================================================+

I then set the category_id on the product to say.. 5 for my mobile radio that I add to the products page. If I visit the product page, I need to get the path for each of the parent categories to display on the page, such as

store/radios/motorola/mobile/product-page-here.html

I would have to build that path from the above database using the path field and joining for ALL of the parent classes to get a correct path. Right now, I am doing many select * FROM tblname WHERE id = parent and then in the while loop of that command, doing another select the same way until I get back to the parent table. There has to be a better way to do that, especially since if I only went 3 levels deep, and the customer adds a sub category that is 4 levels deep, it would never be in the right path.

The final problem is, using the example above, if I instead went to store/radios/motorola, that would try and fetch any items listed under the category_id for 3 instead of 5, and the item would not be shown under the general tab of motorola since it's instead a child of the mobile table, an indirect child of the motorola table. By giving them more than 1 sub category option, its made planning this whole system a royal pain.

Anyways, thanks for reading, leave your comments or suggestions below. Btw, this is all being stored in MySQL and called from a PHP script.

Answer 1

You are severely limited by your current choice of schema. (indeed, I was tempted to close your question as a duplicate of that I've linked here, however given your problem description, I propose a variation on the materialised path:

Instead of starting with a hierarchy of descriptive attributes, treat them as a word cloud associated with each item and consider every possible path composed of one or more or of the words. E.g.

 radios/sku1
 motorola/sku1
 mobile/sku1
 radios/Motorola/sku1
 radios/mobile/sku1
 motorola/radios/sku1
 motorola/mobile/sku1
 mobile/radios/sku1
 mobile/Motorola/sku1
 radios/Motorola/mobile/sku1
 radios/mobile/motorola/sku1
 (etc)

Whether you store this path is up to you - as can be seen above, the number of entries can quickly escalate. But you could just generate it on the fly. e.g.

Create table userpath (
   request int, 
   word varchar(30),
   Primary key (request, word)
);
Create table product (
   SKU int auto-increment,
   ...make, model, price, description...
);
Create table labels (
   SKU int,
   Word varchar(20),
   Primary key (SKU, word),
   Uniques key (word, SKU)
);

To resolve the items in a path, split it into words, put the words into userpath then:

Select p.*
From products p
Join labels l
On p.sku=l.sku
Join userpath u
On l.word=u.word
Where request = ?

And you can get the sub categories of the current path from:

Select l2.word, count(*)
From labels L2
join products p
on l2.sku=p.sku
Join labels l
On p.sku=l.sku
Join userpath u
On l.word=u.word
Left join userpath U2
On l2.word=u2.word
Where u2.word is null
And request=?
Order by count(*) desc

(This could be enhanced via weightings and metadata)

Answer 2

As @symcbean mentioned, this schema is fairly limiting, and storing your brands as categories is probably not what you want to do. However, working with what you've got, here are some suggestions for how to solve your problems.

Unless you can set a hard limit on the depth of nested categories, these are problems that are probably easier to solve in app code than SQL. It's easy to write a query that can pull up to a given depth of nested categories from a table like that. Something like:

SELECT t1.name, t2.name, t3.name FROM accessory_categories AS t1
LEFT JOIN accessory_categories AS t2 ON t2.parent_id=t1.id
LEFT JOIN accessory_categories AS t3 ON t3.parent_id=t2.id
WHERE t1.parent_id!=1
AND t1.id!=1;

But as you say, as soon as someone creates a fourth level, you're in trouble.

As long as this will be a small store with a reasonable number of categories, your best bet is probably going to involve creating a couple of data structs to map your categories to their parents and vice versa, then stuffing them in a cache like Redis or Memcache so you don't have to pull the whole category table for every request.

<?php
//Mock category records, would come from the DB in the real world
$categoryRecords = [
    ['id' => 1, 'title' => 'Radios', 'slug'=>'radios', 'parent_id' => 0],
    ['id' => 2, 'title' => 'Accessories', 'slug'=>'misc', 'parent_id' => 1],
    ['id' => 3, 'title' => 'Motorola', 'slug'=>'motorola', 'parent_id' => 1],
    ['id' => 4, 'title' => 'Handheld', 'slug'=>'handheld', 'parent_id' => 3],
    ['id' => 5, 'title' => 'Mobile', 'slug'=>'mobile', 'parent_id' => 3],
    ['id' => 6, 'title' => 'Level 3', 'slug'=>'level-3', 'parent_id' => 5],
    ['id' => 7, 'title' => 'Level 4', 'slug'=>'level-4', 'parent_id' => 6]
];

//Create an array that maps parent IDs to primary keys
$idToParentIdMap = [];
$parentIdToIdMap = [];

foreach($categoryRecords as $currRecord)
{
    $idToParentIdMap[$currRecord['id']] = $currRecord['parent_id'];
    $parentIdToIdMap[$currRecord['parent_id']][] = $currRecord['id'];
}

/*
 * Now would be a good time to cache these maps in something like Redis or Memcache so you don't have to pull
 * the whole category table during every request.
 */


/**
 * This function will traverse the category tree and return an array of IDs belonging to all categories below
 * the category identified by the $parentId
 * @param int $parentId Primary key of category to find children for
 * @param array $childIds Pass previously found IDs for recursion
 * @return array
 */
function findChildIds($parentId, $childIds=[])
{
    global $parentIdToIdMap;

    if(array_key_exists($parentId, $parentIdToIdMap))
    {
        $immediateChildIds = $parentIdToIdMap[$parentId];

        foreach($immediateChildIds as $currChildId)
        {
            $childIds[] = $currChildId;

            $childIds = findChildIds($currChildId, $childIds);
        }
    }

    return $childIds;
}

/**
 * Return an array of parent IDs for a given category ID
 * @param int $categoryId
 * @return array
 */
function findParentIds($categoryId)
{
    global $idToParentIdMap;

    $parentIds=[];

    while(array_key_exists($categoryId, $idToParentIdMap))
    {
        $currParentId = $idToParentIdMap[$categoryId];
        $parentIds[] = $currParentId;

        $categoryId = $currParentId;
    }

    $parentIds = array_reverse($parentIds);

    return $parentIds;
}

//ID of category requested
$requestedId = 3;

/*
 * Now you can use these IDs to find all of the sub categories and products under the requested category
 */
$categoryIds = findChildIds($requestedId);
$categoryIds[] = $requestedId;

/*
 * Now you can use these IDs to get your parent categories
 */
$parentIds = findParentIds($requestedId);