Awesome q2a theme

The storage of category structures in relational databases?

0 like 0 dislike
Task — organize the storage of a directory with a sufficiently branched structure (tree) — let it be a product catalog of the online store. To search for an item only available URI "/category/subcategory/another-category/and-one-more-category". The maximum nesting of 10.

Categories: frequently, rarely change, the total number of categories can be of the order of 100 thousand

As required by the nimble generation of "bread crumbs". Moreover, the reference to the category ("and-one-more-category") may be different from its title ("another category"), which is used to output on the page.

I have one possible solution — "head" — in the footsteps of Materialized path:

table categories has the following structure

CREATE TABLE categories (


`title` VARCHAR(50) NOT NULL,

`link` VARCHAR(50) NOT NULL,

`path` VARCHAR(1000) NOT NULL,

`title_path` VARCHAR(1000) NOT NULL


CREATE INDEX path_indx ON categories (`path`);

`title` — the title of the category ("another category"),

`link` — link categories ("and-one-more-category"),

`path` — path to category (the"category/subcategory/another-category/and-one-more-category"),

`title_path` — same as `path`, only contains the headers of the respective categories to generate "bread crumbs"

— Attracted by the fact that the search for a category you do not need any effort — just SELECT... WHERE path LIKE...

Don't even need to rebuild paths in the case of moving/renaming nodes.

— Scares the redundancy approach and the likely size of the table with a large number of categories. How will this affect the speed?

— Also mind the fact that as a key for search is a long string into `path` (although I very much doubt that it will ever go beyond 100 characters)

Can make a `path` and `title_path` in a separate table? So anyway the path and the breadcrumbs for the category you want almost always, so I have joint...

Looking in the direction of the Full hierarchy, but again, confused by possible redundancy in the hierarchy table, especially considering the potential number of categories and levels of nesting.

How more optimally to solve the problem?
by | 49 views

4 Answers

0 like 0 dislike
It may be worth it all just cache into MemcacheDB and rebuilding to change the entry in the cache?
Key path
Inside the array with the category row from DB + array for the breadcrumbs
0 like 0 dislike
do I need to provide the ability to change the parent category, for example when editing a sub-category (category/subcategory/... category/subcategory-1/)? if not, then maybe Your option is suitable. If it is necessary to provide this capability, and generally have a more flexible structure — suggest you Google nested sets
0 like 0 dislike
100,000 is 5 characters in node in the path. 10*5 — maximum travel on the category.
Total a maximum of 5 megabytes of data. Really — at times less.
Not the same size, which is to survive.
Regarding the CRC32 and md5
First, md5 is a hex representation of the 32 character hash in Base64 representation total less.
But compared to a maximum of 50 characters, some questionable winnings, to save on the cents.
Second, how are then going to use LIKE to search for?
md5("abc") — it's not like concat(md5("ab"), '%')
Conclusion: do not worry about the size.
0 like 0 dislike
I can advise to use a hybrid approach as DaBase. Cm. implementation.
I.e. it is operated as Nested Sets principle (which speeds up the fetching of child elements) and each node are the place to be parameters parent_id and level.
110,608 questions
257,187 answers
40,796 users