1

User Groups:

user 1 creates users 2,3,4.

user 2 creates users 5,6,7.

user 6 creates users 8,9,10,...etc

finally, user 1 can manage all users. and 6 can manage 8,9,10.

I am planned to store like this,

user - created_by

2   -        1

3    -      1

4    -      1

5    -      2

is this correct way of store data in MySQL database? anybody can help me to improve this database structure. Thank you.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
vijay
  • 244
  • 4
  • 16

1 Answers1

1

it's probably not the best solution, but you can create an attribute hierarchy which represents the parents tree.

for example, user 1 creates user2 and user2 creates user3, the value of that attribute for the user3 would be: "1,2"

so the table will look like this:

| id | hierarchy |
|----|-----------|
| 1  |  NULL     |
| 2  | "1"       |
| 3  | "1,2"     |
| 4  | "1,2"     |

if we want to retrieve all users that are managed by user1:

SELECT * FROM users WHERE FIND_IN_SET("1",hierarchy) > 0

if we want to retrieve all users that manages user3:

SELECT * FROM users WHERE FIND_IN_SET (id, SELECT hierarchy FROM users WHERE id = 3) > 0

of course the solution is limited especially if you want to do complex queries, what I advise you is to pick an XML approach. it's better when your data looks like a tree

Djellal Mohamed Aniss
  • 1,723
  • 11
  • 24