My database looks like below:
I can list the rows with a query like this:
SELECT * FROM my_table
However, I want to list them nested, like below:
my_name
my_message
another_name
another_message
new_name
new_message
some_name
some_message
With normal ids it would look like below:
1
3
4
2
The SQL is not aware of the nesting. I could use a parent_id which is quite common. However, it's quite tricky to get right with pagination etc.
An idea I have is to use float on the ids instead. Then it would look like below:
1
1.1
1.2
2
2.1
2.2
The SQL will make the order correct without hassle and a pagination would be easy as well.
Is there any pitfalls with the float approach? I've never seen anyone use it.
By the way, using DECIMAL would be a way of doing this without risk of rounding anomalies as mentioned in the comment from Akina above.
The pitfall is that you can only simulate two level with a scaled number like this. What if you want a third level like 1.1.3? How would you make that different from 1.13, the thirteenth direct child under 1.?
It's better to use the parent_id solution if you want to support hierarchies in a normalized way, without limiting yourself to two levels.
You could also use a variety of other solutions to simulate hierarchies. The comment about using a node path in a string is one option: 1/1/3. There are other solutions. You might like my answer to What is the most efficient/elegant way to parse a flat table into a tree?