Mysql – A “Steps” table with parent_id

database-designMySQL

I'm making a table which has "steps", The thing is

Some steps may have multiple steps involved. So while looping over the steps I should be able to show this.

What I'm thinking of

+----+----------+-------------+-------+----------------+
| id | guide_id | description | order | parent_step_id |
+----+----------+-------------+-------+----------------+
|  1 |        1 | Step 1      |     1 | null           |
|  2 |        1 | Step 2      |     2 | null           |
|  3 |        1 | Step 2.1    |     3 | 2              |
|  4 |        1 | Step 2.2    |     4 | 2              |
+----+----------+-------------+-------+----------------+

This seems like the only logical way of doing this? But i'm hoping i'm wrong.

  • Should I create another table?

Looking forward to your responses.
Thank you!

Best Answer

Mysql is not really my usual dbms, but looking at your sample I would suggest you another schema by adding [chapter], and use it like a hierarchyid field of MS Sql.

+----+----------+-------------+-----------+
| id | guide_id | description | chapter   |
+----+----------+-------------+-----------+
|  1 |        1 | Step 1      | /1        |
|  2 |        1 | Step 2      | /1/2      |
|  3 |        1 | Step 2.1    | /1/2/1    |
|  4 |        1 | Step 2.2    | /1/2/2    |
+----+----------+-------------+-----------+

Then indexing by guide_id + chapter you can fetch the whole tree, or you can get one chapter using:

WHERE chapter LIKE '/1/2%' ORDER BY chapter
Related Question