I have a table where I store user records where each user was indicated by another user, so I then maintain a ID column and a PARENT_ID column.
There are 2 ways to work with hierarchy in MySQL , they are:
I had chosen to use the Nested Set Model , because it performed better in queries, not needing to generate a QUERY for each node when I fetched all the hierarchical registers. In the Nested Set Model I saw only one disadvantage: you need to update the LEFT and RIGHT values of all entries when a new master record is performed.
Doubt:
What is the best alternative? To lose performance at the time of the user's registration, and I will need to give an UPDATE in each register ( +50000 ) using the Nested List Model or to lose performance at query time using the Adjacency List Model ?
In this case, should I use the Adjacency List Model since the records are dynamic?
Is the Nested List Model only useful when the hierarchy is static, such as product categories?