How do you design a database with sub topics, Data comes like this:
Topic 1
- sub-topic 1.1
- sub-topic 1.2
- sub-topic 1.2.1
- sub-topic 1.3
Topic 2
- sub-topic 2.1
- sub-topic 2.1.1
- sub-topic 2.1.1.1
- sub-topic 2.1.1
- sub-topic 2.1
These topic could be nested. I created a 2 options.
- First Option I could create a table for each topic and make it as 1 to many relationship, then query it with its parent topic_id.
Select
*
From
topics as T
JOINS " join on sub topics of sub topics here"
Where
T.id = 1;
but then I will have a bunch of table of topics of sub-topics, and every time there's an additional sub-topic then the query will be updated. Then with this option I have to create a table on the "fly" if users want to create a sub-topic that's not created yet (not sure if its good idea to create a table via code?)
- Second Option I could create a single table for all topics whether its the main topic of sub-topics of sub-topics, with the column parent_topic_id.
table will look like:
| id | title | parent_topic_id |
|---|---|---|
| 1 | Topic 1 | null |
| 2 | Topic 1.1 | 1 |
| 3 | Topic 1.1.1 | 2 |
| 4 | Topic 2 | null |
| 5 | Topic 2.1 | 4 |
I'm still figuring out the query for this table. kinda confusing. Wanted to use this but i cant figure out the query for it.
This will be use internally, about 10K users, As of right now only 4 people can write/edit, 10k approximately will read the data.
Database used is mysql.
