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 (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`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?
