Structuring Infinite Parent->Child schema

Scriptcase 8 Discussion
#1

I need to create a procedure which will allow to build a Parent/Child schema. To simplify the question I will post an example here:

  1. Say we have a distributor who sells products (i.e Tom). Tom UserID=1
  2. Tom has 3 people signed under his name: Christina (with UserID=5), Jerry(with userID=12) and George(with UserID=45)
  3. Christina signed 2 people under her name. Adam(UserID=34) and Mark(UserID=36)
  4. Mark signed 3 other people and Adam signed 6 other people,…etc.etc…

I hope you see where am I going…

I need to create a table struvture to implement this procedure so at any given time the App can show the whole structure (like a tree)
For example I will show

Tom-1 ----------------
Christina-5-----------------------
Adam-34
Mark-35-------------------
Person-100
Person-103
Person-106
George-45------------------------------------------------------------------

I have some ideas how ti design the database for this but I haven’t done something like this before so need some opinions from those who did
I wonder if anybody has implemented this structure and what is the best practice ?

here is an image showing the graphical chart

ART

[ATTACH=CONFIG]n71333[/ATTACH]

tree.jpg

#2

Arthur-

I am seeking the same wisdom. I have found several articles on the topic (I’m sure you have seen some of them as well), but here they are:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

http://moinne.com/blog/ronald/mysql/manage-hierarchical-data-with-mysql-stored-procedures

http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html

https://www.simple-talk.com/sql/performance/the-performance-of-traversing-a-sql-hierarchy/

https://www.simple-talk.com/sql/t-sql-programming/binary-trees-in-sql/

There are others as well, but one of the important considerations (I am doing this out of memory from reading them a while back) has to do with how often the hierarchy needs to be modified (write) versus reporting (read). One method tends to be better than the other, depending on which is more critical to your application.

I’ll let you know when I have some useful experience on this.

Brad