TL;DR – Trying to create tree structure user relationships with single parent, unlimited children, unlimited levels of depth in which we can pull organizational stats from any user node and their children in scalable way.My challenge is to create a user table(s) with a tree relationship structure. Here’s my outcome, which at first glance seems straightforward.Each user node can have only one parent, as a user must be invited by an existing user.Each user node can have multiple children user nodes. We could limit it to be practical, to zero to 500 children per user.There are no limitations to the depth of relationships. Theoretically, a user could add a user, which adds a user, and this could repeat a few hundred times.This would all be fine, except the primary purpose is for reporting on productivity stats organizationally, so a traditional parent/child relationship would result in exponentially long reads for aggregate data for any given branch.Let’s say we have an Action table like the following:(pk) actionId (Int)(fk) userId (User)createdAt (DateTime)actionType (Enum)minutesActive (Int)We would want to be able to show the user a dashboard showing:Bar graph showing total # of minutes worked each day for a specified action type, mapped over the last week.Same stat showing weekly totals mapped over the quarter.Again, that’s no problem for that specific user, but we also want each user to be able to see aggregated stats for each child branch. Let’s say the first user they added has then added users themselves and so on to where that first branch includes 250 users. Then their second branch includes 150, the third 100, and so on for 20 different branches, and we want their dashboard to be able to show Action statistics mapped over time per branch.At the same time, any other user of the system could be pulling up a dashboard of their branches.This would implode on itself pretty quickly when a single query requires a few thousand read operations.I’ve been trying to study up on the best way to structure user relationships for such a use case, and I’m not finding great examples. It’s challenging when there aren’t clearly defined tiers/levels.I have a few crazy ideas on approaches, but I thought it might save a ton of headache leaning on someone much more experienced.If it matters, my backend stack is node.js/express.js for server/business logic, sequelize.js/postgresql for ORM/DB, Apollo-server / graphql for API, and then my frontend go-to is react.js, using Apollo-Client for async – so all javascript based. I’ve got more experience with Django/Python/Graphene backend, but since I’m so buried in Javascript for frontend anyway, I figured now’s a good time to start doing everything in one language.So, if you’ve read this far, I’m SUPER grateful. I wasn’t sure the best way to communicate my challenge. If you’ve got a learning resource you’d recommend, I’m happy to dig deep on it and do my homework. If you have a solution in mind you could explain, I would love that as well. If it’s asinine to endeavor to achieve this without google/facebook-level resources, I get that too.Any insight would be much appreciated!
Submitted April 10, 2018 at 07:34PM by gtderEvan
No comments:
Post a Comment