How to build the sqlite hierarchy and the algorithm for implementing the idea?

Question

SQLite

Hi everyone, I'm new to python. The idea is to write a telegram bot that will ask questions written in the database (sqlite). Questions will have their own hierarchy. For example, initially there will be 6 areas (sports, literature, etc.). When a person chooses a sport, questions about sports are already loaded from the database. And so on (such a vertical hierarchy that goes down). For example, when a person chooses football from the sport, questions about football will already be immersed. Can you please tell me how to implement this idea? (use global variables to know what topic we are in now (sport-football-RFPL championship)?; how to build a database?)

Answer 1

Forget about global variables.
Make two tables. The first is a tree.

id   | name           | parent_id
1    | Спорт          | null
2    | Футбол         | 1
3    | Чемпионат РФПЛ | 2

The second is data.

id   |   tree_id  | data
1    |   3        | Инфа о чемпионате РФПЛ

Show the user information from the "tree" and let's select a category, when he chooses - show the data corresponding to the category:
select * from data where tree_id=3

Answer 2

And how to quickly find (with a minimum of queries) a category / or its existence (for example: Hockey) in a tree, knowing the name of the parent (Sport: i.e. get its id) ? After all, the sub-category can be much deeper and the tree can be more branched.