Build a Better Instance Part One: Configurable Joins Fundamentals

On February 2, nearly 450 members of the Slate user community joined team members Abraham Noel, Domenick Rozzi, John Michael Cuccia and Megan Miller for the first webinar in our new series, Build a Better Instance with RHB: Configurable Joins. In “Setting the Foundation: Configurable Joins Concepts and Basics”, the team walked through the fundamentals that will allow Slate users to begin building an effective working knowledge of this powerful querying tool.

Over the course of an hour, our RHB experts discussed Slate’s database structure, Configurable Joins terminology, and the methodology for creating effective CJ queries. The session provided attendees with a wealth of knowledge centered around these three themes.

To understand Configurable Joins, we need to understand how Slate is built.

Slate is a relational database, which means that our records have data that exist in various parts of the system. Much like we don’t keep everything we own in one room of our house, Slate doesn’t store all the data in one space, and in a relational database, we call these spaces tables. Instead, Slate houses our records using a structure designed to maximize functionality—think of this as the metaphorical equivalent of keeping your blender in the kitchen instead of storing it in your bedroom.

And much like a house has halls and entryways to connect our rooms together, relational databases like Slate have a way of getting us from one table to another. The Slate database has more than 200 different tables, or “rooms”, for everything from person information, to applications, to addresses, to relationships; if Slate didn’t utilize tables, each record (or item in our house, to keep using the metaphor) would have thousands of columns, significantly inhibiting system functionality.

Technolutions has built paths in Slate so that we can get exactly the data we need from the correct area of the database. We just need to know which halls connect us to which rooms so that we can make our way around the house, just in case we need to go grab that blender from the kitchen at some point. And what do we call those halls? Well, in a relational database, those are known as joins.

We’ve been joining all along. Maybe we just didn’t know it.

Even those of us who have never written a line of code have already been creating SQL statements that leverage joins whenever we’ve used Slate’s query builder, but it’s been happening behind the scenes. Whenever we selected the Prospects and Applicants base from the Slate Template Library, or exported first and last name in a query using the Applications base, or even filtered out all of our Test records for a recipient list in Deliver, Slate has been joining from the query base we selected to other related tables.

We’re still doing the same basic thing we’ve always done with queries: identifying what types of records we’re looking for when we select the base, articulating which of those we want to access with our filters, and defining what we information want to know about them with our exports. So while Configurable Joins essentially operate the same way we already have been (albeit unaware), the real difference with this new form of querying comes down to asking our questions in a new way: “What is the pathway that leads me from these records to the information that I want to know about them?”

Technolutions provides us with tools to help make sense of the data structure.

Like we noted, there are over 200 tables in Slate, and Technolutions understands that we can’t all be experts on database architecture. And so, instead of leaving us stumbling around in the dark trying to find a light switch, we’ve been given tools to better make sense of how everything is built. First up is the Slate Data Dictionary, which lives in the Standard Query Library in the Database module of Slate. Here, we’re able to see what all of the tables that exist in Slate, as well as these tables’ column headers.

But even better, we have the Configurable Joins Base Explorer, also in the Database, to help us clearly see how everything ties together. With this tool, we’re able to see what tables connect together and how. With both a list view and an incredibly useful drag-and-drop diagram view, this tool allows us to explore all the paths that we can take from a base so that we can more clearly see how we connect point A to point B (or point Z, for that matter). As we work to better wrap our heads around the structure of Configurable Joins, we’ll be well-served by referring to this tool along the way.

Ready to learn more? Dig deeper by watching the full webinar, reading the transcript, or downloading the slides below, then register to join us on March 2 for part two of the series, “Building Up: Intermediate Configurable Joins Skills and Use Cases”.

  • Spread the word
Megan Miller

Megan is a Senior Integration Consultant at RHB.