What's the nicest and most convenient way to create a nested hierarchical structure in MySQL, well, the simplest example is a basic declaration directory. There are categories, they can be one in another (subcategories).
I am using a table with categories, which has a category id and a parent category id (0 for root). Is it pathetic? If yes, then tell me how to do it normally, otherwise tell me how, with such a system, you can humanly count the number of records from another table, "belonging" to a category (that is, not only purely to her, it is elementary, but everything nested).
Answer # 1
MySQL is a relational DBMS, it is impossible to store hierarchical data in a "nice" form, so your option is not only acceptable, but also quite correct. Queries to such a structure in its pure form are impossible in MySQL, but you can write a stored procedure that recursively pulls out the id-shniki of ads of the desired category and all its subcategories. Herethere is an article on this topic. Having pulled out the id-shniki, it will not be difficult to count them.
- What will be the relation between Employees and Players, given that some players are also Employees?
- Where to Start Learning The Idef1x Method of Model Notation?
- Non-product invoice items
- mysql : How can I better model the relationships in this project?
- mysql : Anonymous and Public User Table Design in twitter like application
- oracle : Designing User &its Sub User Database Diagram Problem [duplicate]
- postgresql : In Postgres Is There Any Real Drawback On Using A JSON Field Over Multiple Tables and One to Many Relationships?
- postgresql : How can I efficiently query products with many-to-many ingredients and nested products?
- Finding all possible minimal covers