Solved

In a database that tracks a downline, is each member organized in a separate table?

Posted on 2014-02-11
9
614 Views
Last Modified: 2014-02-12
In a database that tracks a downline, is each member organized in a separate table?
How can members in a downline be connected? How do I run a query to make a report of each members downline?
0
Comment
Question by:QUESTOMNI
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39850587
Is this an academic assignment?  
What is the intention entity-wise?  i.e., can a downline affect multiple members?  Similarly, can one member belong to multiple downlines?

In general you can have separate tables for each entity and associative tables between the entities to establish many-to-many (one downline to many members; one member to many downlines) relationships.
0
 

Author Comment

by:QUESTOMNI
ID: 39850669
Each database has members who are able to build a downline by making sells to others who join. They sponsor people into the program. Those people sponsor people into the program. Continuing the process several levels down. It's call a downline.

I'm trying to find out how it works. I'm considering affiliate software.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39850701
In some systems there is one member table with a self-referencing column, representing the parent member.  Hence, the downline hierarchy builds up as follows:

ID          parentID    Name
----------- ----------- ------
1           NULL        Clint
2           1           Jessie
3           1           James
4           3           Lebron

Open in new window


In the above example, Jessie and James are both downline members of Clint.  Lebron is a downline member of James.

Another way to do this is to have a members table and then a table that takes two member IDs to associate a member to downline members.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:QUESTOMNI
ID: 39850744
"Another way to do this is to have a members table and then a table that takes two member IDs to associate a member to downline members."

That seems the most practical to me. So, I could use the associations to report a downline or upline for each member with a query, right?
0
 

Author Comment

by:QUESTOMNI
ID: 39850828
You know, it sounds like if each member has a separate table with his personal ID and the ID of the guy who sponsored him, I could use those associations to report a downline or upline for each member with a query, right?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39850829
Yes.  It is just a matter of perspective on the joins.  To assist you in query readability, you can name the two columns uplineMember (or uplineID) and downlineMember (or downlineID).  Then these mean the same as they are read.

SELECT a.downlineMember
FROM memberAssociations a
JOIN members m ON m.ID = a.uplineMember
;

Open in new window


SELECT a.uplineMember
FROM memberAssociations a
JOIN members m ON m.ID = a.downlineMember
;

Open in new window


I hope that makes sense!
0
 

Author Comment

by:QUESTOMNI
ID: 39850894
You know what, I think you've given me plenty of info to approach what I'm after. Seems you're working with the right database design from a pro perspective. I may arrive at it sooner or later. I need to refresh on the subject. From a novice view, that may or may not cause problems, I see separate tables for each member and two IDs on each, as you mentioned, will allow me to get the results I'm after with the right query. Wish me luck and thanks a bunch.
0
 

Author Comment

by:QUESTOMNI
ID: 39851697
Hey Kevin, I see what you mean. That would be a lot of tables I'd have to make. It would slow things down considerably. Thanks.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39853013
You are most welcome!  I was hoping you would see that sooner or later.  Good luck with the project, and I am glad I could help.

Best regards,

Kevin
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question