Did you know that you can create a relationship from a TrackVia table to itself? Sounds wild, but it's true. TrackVia will allow you to create a self-referential relationship, allowing records on that table to choose another record on the same table as a parent.
This is a great feature when you need it, but the truth is, you rarely will. Let's talk about why.
Linking two records on one table to each other sounds incredibly useful at first blush, but there's a catch. Because they're on the same table, they each have the same relationship fields, including the self-reference at question. This means if Record A chooses Record B as a parent record, Record B can still go on to choose Record C as a parent, and so on. Alternately, Record A and Record B could both choose Record C as a parent, but that won't stop Record C from choosing Record A as a parent. Then, if there are any calculated fields or scripts which look to the parent or child records, those calculations might run in a loop and never finish, causing any view with those fields to fail to load. We're only looking at 3 records and things are already out of hand.
Risky but Useful
Where this comes up the most is on tables of employees. It's tempting to use this structure to simulate an org chart. Each employee record chooses a supervisor, and each supervisor, in turn, chooses their own supervisor, and so on. That way, any time an employee is shown on a form or a view, that employee's supervisor can be easily included.
That table can even look through the relationship with calculated or triggered fields and reproduce the supervisor's phone number on the employee record, or other simple pulldowns of that nature, without much risk.
However, as soon as you try to pull one of these calculations further down, maybe attempting to fetch the supervisor's supervisor, or using a calculated field to list everyone above or below the individual on the org chart, you'll find view performance dropping considerably. This is because each time someone loads a view of the employee table, those calculations run again, using data from other calculated fields which themselves run again. Then, because the parent record, grandparent records, and so on, are all located on the same table, each must load simultaneously each time any user opens the view. For each additional layer of calculation, the calculation-weight on that table might double, triple, or worse.
To avoid this, make sure any self-referential relationships are kept clean and simple. Try not to use calculations or scripts that rely on that relationship and instead simply include the relationship field on relevant forms and views to allow the user to click directly into the parent record and interact with it directly. This way, the relationship is more of a simple signpost, helping users navigate to relevant sibling records, but without the same baggage of a field that's part of a calculation.
So what do you do if you truly need those sorts of calculations?
Join Tables for Many to Many relationships
This is where Join Tables come in. A Join Table is a Child Table that joins two Parent Tables to create a safe and maintainable many-to-many relationship. In the example below, the "Team Assignments" table is a child to both Employees and Teams, meaning for each assignment of an employee to a team, there is one record in the Team Assignments table joining those two values. That way, the record for the team and the record for the employee can both include child views on their forms showing all of the employees on the team, or all of the teams an employee has been assigned to, respectively.
Also in this example, the supervisor of each team is still assigned via a direct relationship, meaning a supervisory connection to a team is handled with a completely different relationship, making it much safer and more performant to aggregate employee information on a supervisor's record using calculated and triggered fields. With this structure, it's completely safe to use child aggregate functions, like ChildConcatenateDistinct, or ChildCount, in calculated and triggered fields, to collect and display things like a list of all employees under a given supervisor's teams. Unlike the self-referential join, there are no worries here about showing large unfiltered views of any of these tables, because there's considerably less chance of a calculation running in a circle, or repeating an arbitrarily large number of times.
Other Circular Structures
Another type of circular relationship that's possible in TrackVia is a more traditional loop between tables, as in the example below.
In this example, each project has an employee assigned as an owner, but then each employee has a location as a home base, and each location is part of a single parent project. Each of these relationships makes sense and might fit your business's use case, it's incredibly easy to inadvertently run calculations which travel in a circle. If the location table has a calculated field which pulls down the name of the project owner, and the project owner already has the name of their home location included in their record ID, you might find yourself with some less than functional views without an obvious cause.
Join Tables at it again
The solution here is, you guessed it, a join table. Rather than an Employee being a Parent to the Project, in this version, the Employee is joined to the project using the Project Assignments table. Each Employee-Project pair has one record in Project Assignments. The Project Assignments table can now simply contain a dropdown to define the relationship between the employee and the project (which views can then be filtered by for even more value). The Projects table and the Employees table can both see into the Project Assignments table and retrieve data that was brought down from the other side of the join in an efficient and controlled manner, and without the risk of a calculation running in a circle. It's still possible to write inefficient calculations with this setup, but it's considerably harder to run into one unexpectedly, and unlike the old structure, this one is able to display nice-looking child views from the perspective of the project or the employee.
Join Tables are a great way to create many-to-many relationships, or simply to improve performance in complex data structures. Happy building!
Related Resources:
TrackVia University: Course 5- Tables
Building Performant Applications
Data Modeling Workshop- October 12th 2022 (Recording)
Comments
0 comments
Please sign in to leave a comment.