Should I use a nested set for the users table?

[woodjs]

The user can have an unlimited number of referrals. And also you need to often get the structure of trees and work with them.
The number of users can reach up to 10,000.
I am also considering an option: https://github.com/overlookmotel/sequelize-hierarchy. Can you suggest which solution is best for my task?

[fremail]

@woodjs I don't think using a nested set for the users table is a good idea.

Nested set is fast when you read the tree, but is slow when you add/remove data. Each inserting/removing of a row produces updates of all other entries in the tree. Imagine, you have 9k users. A new user is signing up. You need to update 9k rows. It will be slow for every new user.

I think a relation of user_id and parent_id is enough for your use case:

user_id	parent_id	email
1	null	root@email.com
2	1	u1@email.com
3	1	u2@email.com
4	2	u1-1@email.com
5	4	u1-1-1@email.com

sequelize-hierarchy.js uses parent_id and level -- similar to what I showed above. It suits better to your use case than a nested set.

[woodjs]

Thank you for the tip.
As it turned out, that package, when adding a new record to users, it then creates the users_ancestors table, where it adds all the ancestors, that is, if there are 3,000 ancestors, it will create 3000 records referring to them. It seems to me that this is not a very good solution. And I came to the conclusion that it would be faster to use recursive queries.