Dealing with variable category hierarchies

Question

The issue I have is as follows: My company's supplier gives us an Access database (which I import into SQL Server) containing their product information (the alternative is to use XML), and I'm trying to massage this into a more usable format for use in an e-commerce website.

The problem I run into, and maybe I'm just not thinking clearly, is that their category information can be anywhere from 3-6 subcategories deep; there are always at least 2 categories (a top-level Parent category and a more specific subcategory) but there can be up to 6 depending on the item.

Their data is provided to me in the following table structure:

CREATE TABLE [dbo].[ECDB2_HIERARCHY](
    [SEQ_ID] [int] NOT NULL,
    [PFX_NUM] [nvarchar](3) NOT NULL,
    [STK_NUM] [nvarchar](12) NOT NULL,
    [ECDB2_LVL_1] [nvarchar](max)  NULL,
    [ECDB2_LVL_1_ID] [int] NULL,
    [ECDB2_LVL_2] [nvarchar](max) NULL,
    [ECDB2_LVL_2_ID] [int] NULL,
    [ECDB2_LVL_3] [nvarchar](max) NULL,
    [ECDB2_LVL_3_ID] [int] NULL,
    [ECDB2_LVL_4] [nvarchar](max) NULL,
    [ECDB2_LVL_4_ID] [int] NULL,
    [ECDB2_LVL_5] [nvarchar](max) NULL,
    [ECDB2_LVL_5_ID] [int] NULL,
    [ECDB2_LVL_6] [nvarchar](max) NULL,
    [ECDB2_LVL_6_ID] [int] NULL

For the most part I can ignore SEQ_ID as it's not used; PFX_NUM and STK_NUM get concatenated together to form the product's SKU, but that's not the issue. I need to be able to dynamically traverse categories from the site. For instance, given the following row:

SEQ_ID: 364867 (ignored)

PFX_NUM: AMP

STK_NUM: 73121

ECDB2_LVL_1: Office Supplies

ECDB2_LVL_1_ID 11

ECDB2_LVL_2: Envelopes, Mailers & Shipping Supplies

ECBD2_LVL_2_ID: 26

ECDB2_LVL_3: Envelopes

ECDB2_LVL_3_ID: 195

ECDB2_LVL_4: Business Letter Envelopes

ECDB2_LVL_4_ID: 795

ECDB2_LVL_5: (empty)

ECDB2_LVL_5_ID: 0

ECDB2_LVL_6: (empty)

ECDB2_LVL_6_ID: 0

The user should be able to navigate through the levels, but what throws me off is the sample website provided with the data (see below) displays all items under a subcategory at random intervals... it looks like it's at the 3rd level (ecdb2_lvl_3) but for items that don't have the 3rd level it displays starting at the 2nd. As you can see from the schema, they have it all together in one table that lists the products AND all of the categories they belong to, instead of something like a self-referencing categories table and then a joining products table.

The problem is that some items only have 2 levels, some like this one have up to 4, and there are a few that have all 6 - the vendor's sample website, available at http://www.biggestbook.com does a good job of what I want, but I don't have access to their code so I'm left scratching my head as to how exactly they are pulling back categories and traversing them. I'm assuming they have some kind of global flag to indicate what level you're currently at (e.g. 1 for Office Supplies, 2 for Envelopes et all, and so on) so they can keep track of what your current depth is, and then check each sub-level to see if there are more subcategories to show, but I'm drawing a blank when I think of how to handle this efficiently. Their naming scheme leaves a lot to be desired, as well, but that's something I can tackle later if need be.

Anyone have advice in how to tackle this issue? I'm planning the store in C#/ASP.NET (possibly MVC, possibly not) so C# examples would be most useful, but I can follow most languages easy enough to figure it out.

Answer 1

If you don't mind using recursive functions to traverse a self-referencing category table, definitely redesign the database to go that route. One would think a recursive function in SQL might be performance suicide, but with proper indexes set up it can complete extremely fast.

As for the data set your are working with, you can see from the sample web site that they are storing current category in the URL query:

Office Supplies > Envelopes, Mailers & Shipping Supplies > Envelopes
?N=4294858589&...

Office Supplies > Envelopes, Mailers & Shipping Supplies > Envelopes > Booklet & Catalog Envelopes
?N=4294858588&...

Where N is the current category. I would think that their database has a lookup table to see what level N belongs to. Alternatively, they could be just doing a big WHERE/ORDER BY clause like:

WHERE (ECDB2_LVL_1_ID == @N) OR (ECDB2_LVL_2_ID == @N) OR (ECDB2_LVL_3_ID == @N) ...
ORDER BY ECDB2_LVL_1_ID, ECDB2_LVL_2_ID, ECDB2_LVL_3_ID...

If N is a 2nd level category, products that don't have a 3rd level category will appear first since null comes to the top when sorted.

On a side note, they keep track of what category you traversed to reach a product in the session. Follow a category down to a product till the URL says something like ?R=12345. The breadcrumbs will show the category used to find that product. Clear your cookies and refresh the page, the breadcrumbs will turn into Biggest Book > Product Detail. That isn't terribly useful for people who come upon the page from a search engine since they then cannot select a category easily to see what similar products are available.