MySQL float ids for nested list?

Question

My database looks like below:

• id
• email
• message

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.

Answer 1

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?