[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 683
  • Last Modified:

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

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
QUESTOMNI
Asked:
QUESTOMNI
  • 5
  • 4
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
QUESTOMNIAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
QUESTOMNIAuthor Commented:
"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
 
QUESTOMNIAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
QUESTOMNIAuthor Commented:
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
 
QUESTOMNIAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now