Rubrication problem
Dear Colleagues! Please, Help me! (Excuse for my english)
As a result of developing an information news portal there will appear the algorithmic problem of interaction with MySQL.
The rubrication organize next way (sql table: important fields):
TABLE rubric (rubrics): rubric_id, rubric_parent (parent rubric's id)
TABLE rubric_article (table of links of rubrics with articles): rubric_article_id, rubric_id (rubric's id), article_id (article's id)
TABLE article (articles): article_id, article_type (type(section) of articles)
TABLE type (type(section) of articles): type_id
So the hierarchy of the rubrics arranged "linear": every rubric knows her parent only (by rubric_parent). Root rubric has parent 0.
It is necessarry entry the articles' section (responsible for section - type_id) to output the rubrics (by hierarchy!), which have the articles from this section (by article_type).
There is need to output this by step (not as tree): -> section -> rubrics of level 0 (with parent 0) -> rubrics of level 1 (with choosed parent) and so on. So they can go down for arbitrary depth.
The problem. Some sections can do not contain articles directly, but contain them in their subrubrics. For all that to recognize all rubric of adjusted level (i.e. on adjusted parent) that contain somewhere in their progeny-subrubrics the articles from the given section is not a trivial task.
The site is planning to be actively visited, so a lot of queries to DB by every user (and this number will rise greatly with every new rubrics) is inadmissible.
What do you think of it, colleagues?
Beforehand Thanks for any support!😕
