ECommerce, products in category and category browsing

I am building an EC website for a customer and the project manager came with some strange ideas and I am struggling to actually implement what he sold to the client.

Here comes my main issue and a quick summary how the system is setup: product are inside categories, categories could be children of an another category. So the category is presented as a tree on the left sidebar of the website.

The user can browse any category, even non "leaf" category, if the user click on non leaf category a listing like that should b开发者_JAVA百科e presented for exemple on a level 1 category (same apply to level 2 categories):

big category 1
 category level ( 3 or 2 )
  product 1
  product 2
  product 3
 category level ( 3 or 2 ) 

The things should also have some paging and present on 5 product on each page. Plus the category should be ordered in same fashion they appear in the menu on left side ... my DB scheme is like this:

+-------------+    +-------------+
+ category    +    + product     +
+-------------+    +-------------+
+ category_id +    + product_id  +
+ parent_id   +    + category_id + 
+-------------+    +-------------+

I cannot really figure out how I should code the SQL to make sure the product appear in order they should(like ordering product and categories has menu).

Also I am concerned about the performance of the whole setup, if the user select a non "leaf" category I would have to search all the child category and make a big category IN ( id1, id2, id3 ) and I know by experience long IN statement don't perform well.

If someone have encountered same design/issue and have some advice how to make it I would be grateful.

You could use the Materialized Path design. A directory path is an example of materialized path. That is, a series of ancestor values, concatenated together, with some character ("/" or "," are common) separating them.

So you might have categories:

+---------------------------------------------+
| cat_id | Name            | cat_path | depth |
+---------------------------------------------+
|    1   | Electronics     | 1/       |   1   |
|    2   | Digital cameras | 1/2/     |   2   |
|    3   | SLR cameras     | 1/2/3/   |   3   |
|    4   | Audio           | 1/4/     |   2   |
|    5   | Speakers        | 1/4/5/   |   3   |
|    6   | Wall Satellites | 1/4/5/6/ |   4   |
|    7   | Computers       | 1/7/     |   2   |
+---------------------------------------------+

Now if you want all products that are under Audio, you can do a query like:

SELECT p.*, pc.*
FROM Products p JOIN Categories pc ON (p.cat_id = pc.cat_id)
JOIN Categories c ON (pc.cat_path LIKE c.cat_path||'%')
WHERE c.name = 'Audio';

For example, '1/4/5/6' LIKE '1/4/%' is true, therefore Wall Satellites are included. And same for any other subcategory of Audio.

Re your question about menu rendering: I assume you'd want the menu to render: - All ancestors of the chosen category - All siblings of the ancestors of the chosen category

So if you choose 'Speakers', you'd see:

• Electronics
  • Audio
    • Speakers
  • Computers
  • Digital Cameras

But you don't want descendants of Computers or Digital Cameras (i.e. "cousins" of Speakers).

SELECT uncle.name, uncle.depth
FROM Categories chosen
JOIN Categories ancestor ON (chosen.cat_path LIKE ancestor.cat_path||'%')
JOIN Categories uncle ON (ancestor.depth = uncle.depth
  AND SUBSTRING(REVERSE(ancestor.cat_path), 3, 100) = SUBSTRING(REVERSE(uncle.cat_path), 3, 100))
WHERE chosen.name = 'Speakers'
ORDER BY uncle.depth, uncle.name;

I'm using a trick to detect uncles: compare the paths, after stripping the last element. To do this, reverse the string and then strip the first element. This should work at least in MySQL and MS SQL Server, but REVERSE() isn't standard and might not be portable to other brands of RDBMS.

Note that you should probably allow for more than one digit for each element in the cat_path, in which case the substring offset should also increase.

From a performance perspective this is a bad design. If a customer accidentally clicks on the toppermost category you would execute a query of your entire inventory. This will probably take an unacceptable amount of time. In web terms this translates to the customer losing patience, clicking over to your rival's site and never visiting your site again.

Of course, premature optimization is the root of all evil and all that, but it is a good idea to avoid doing completely dumb things.

I would also take issue with the whole idea of tree navigation as an approach. It smacks a bit too much of asking your customers to play a game of "Guess how we inventory our stock". Apart from anything else, in many spheres a product can belong to more than one category, so fitting them in a hierarchy is an arbitrary process. At the very least you probably ought to have a data model which supports assigning a product to multiple leaf categories. (This may depend on the nature of what you're selling and the granularity of your categories).

If your boss insists on their way then you still have some options to improve the performance of the query. For instance you could have a table which includes all the products joined by all their parent categories...

cat1 product1
cat1 product2
cat1 product3
cat1 product4
cat1 cat1.1 product1
cat1 cat1.1 product2
cat1 cat1.2 product3
cat1 cat1.2 product4
cat1 cat1.1 cat1.1.1 product1
cat1 cat1.1 cat1.1.2 product2
cat1 cat1.2 cat1.2.1 product3
cat1 cat1.2 cat1.2.2 product4

You would have to maintain this, through triggers or as a materialized view or through some other mechanism (depending on what your database flavour offers). But the overhead of maintaining it would be neglible compared to the performance benefits of not having to re-assemble the product hierarchy for every customer query. Besides it is unlikely you have that much volatility in your inventory.