Solved

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

Posted on 2014-02-11
9
573 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
duplicate data not insert in mysql 46 51
Install MySQL 5.6 and PHP on Centos Linux 6 101
PHP loop not working 4 55
join tables 4 51
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
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, f…

920 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

13 Experts available now in Live!

Get 1:1 Help Now