Showing users in tree structure?

Question

I have a database table called users

This table has two columns (that are important)

uuid, and parentUuid

Here are the rules for the table:

• If a user invites another user, the invited users parentUuid column is equal to the uuid of the inviter.
• If a user was not invited, their parentUuid column is null
• There can be infinite levels of users.

What I want to do is, create a function function counter($levels, $uuid){}

When counter is called (lets assume $levels = 3)

I want the function to return an array that looks like

array(0 => 200, 1 => 600, 2 => 1800);

So the basic idea is I want it to count down, for $levels levels how many users are in the tree under the user.

What is the best way to do this?

Answer 1

I'd extend your users table to include a "level" column, that indicates the depth in level from the root that the user is. That way, when a new user gets added, their level just gets set to the parent's level + 1.

I know this is a bit different of a solution than what you're asking for, but the traversal process for getting the data you want from a table that doesn't store user levels is tricky at best, and the execution time is potentially very long. This seems to be a good example of a situation where the best solution is a slight modification to your schema.