Solved

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

Posted on 2014-02-11
9
619 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 60

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 60

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 60

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 60

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Why is my $_POST not going to results page 10 68
MySQL stored procedure returning null values 4 36
Error in sql query statment. 21 70
Code not executing correctly. 3 43
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Creating and Managing Databases with phpMyAdmin in cPanel.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

737 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