Solved

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

Posted on 2014-02-11
9
560 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
  • 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now