The most important part of a relational database is (you guessed it) relational data. What that means is: connecting information is just as important as the information itself.
A quick example of that would be during a sale. To keep things simple, let’s imagine two blocks of information:
On one side, you have the client. The client may have a lot of different properties, such as a name, a phone number or an email.
On the other, you have a sale. A sale may contain information such as an ID, a date and a total amount.
Now, the sale by itself can be valuable information. For example, you can sum all the sales in a month to calculate revenue. Similarly, the client’s information can really help you when you need to get in contact.
However, if you connect those two, information will get even richer just by being related: by linking sales to a client, you can understand your best customers, the ones who always buy from you; and also the ones that you haven’t seen in a while.
Traditionally, you’d see this connection represented as this:
But, when it comes to connecting data, this representation does not convey the relationship in the best way possible. That’s because connections have a direction, and it matters.
When you think about it, a client could have bought many times from you. However, the opposite is generally not true: sales usually have only one client. In a way, a sale points to a singular client.
Why does this direction matter? Because setting a direction lets us pack the data more efficiently. Before showing you why that is, let’s talk about structure.
Introducing the Building Block
When we talk about data, we’re usually visualizing it as a table or a spreadsheet. That’s not a bad mechanism for getting a grasp on the information itself, but it can sometimes hinder the understanding of relationships between data.
Here on jestor, we prefer to think of data as building blocks. They provide a nifty way of looking at relationships, but first let’s go over the basics.
A building block is, simply put, the collection of information a record can contain. If we think of the previous example, client is a building block: every client is a combination of name, phone number and email. We like to think of each possible information as a box: a name goes on the “name” box, for example.
We could even write this block with boxes as something like this:
[client: name, phone number, email]
Similarly, a sale could be written as:
[sale: id, date, amount]
As you can see, building blocks are actually quite simple: you can even think of them as the header on a table or a spreadsheet.
Now, why doesn’t the bidirectional diagram we presented before accurately represent the relationship between a client and sales?
Well, imagine Steve, one of your most faithful clients. He’s purchased goods from you three times this month alone.
Steve could be represented by the following block:
[client: Steve, 111 222 3333, [email protected]]
And the sales could be represented as:
[sale: #001, 04/01/2021, $172,00]
[sale: #002, 04/13/2021, $321,50]
[sale: #003, 04/21/2021, $229,25]
Now, those blocks of information are really simple, but they aren’t connected yet. Right now, there’s no way to know any of those sales are related to Steve.
If we tried to connect them all in a bidirectional manner, we can imagine it would look something like this:
[client: Steve, 111 222 3333, [email protected], sale #001, sale #002, sale #003]
[sale: #001, 04/01/2021, $172,00, Steve]
[sale: #002, 04/13/2021, $321,50, Steve]
[sale: #003, 04/21/2021, $229,25, Steve]
At first, this might make sense: that is roughly what we would do if we tried to control it on a spreadsheet. However, a quick exercise in scale would show us this is a terrible way to link the information between the two blocks. What would happen if Steve had a hundred sales attached to him? His building block would become immense, practically unusable:
[client: Steve, 111 222 3333, [email protected], sale #001, sale #002, sale #003, sale #004, sale #005, sale #006, sale #007, sale #008, sale #009, sale #010…]
When you look at it, it doesn’t make sense to add the sales to Steve’s block. If you wanted Steve’s sales, you could just look at all the sales blocks that have Steve in it. You may, however, want to access Steve’s information while looking at a specific sale, so it’s important that the sale points to Steve’s block in some manner. That is: you should be able easily navigate to Steve’s information from a particular sale.
As you can see, the connection actually has a direction. A sale points to a client. In fact, many sales can point to the same client. So, instead of using this picture:
We prefer to use the building blocks, connecting almost as you would with a child’s toy to create a structure.
It may look like a funny way of representing scalable processes, but it works. You immediately know that a sale points to a client, and that a client may have many sales.
On our building block notation, we mark this connections with a ↑ sign, such as:
[sales: id, amount, date, ↑client]
Creating on jestor
It’s very easy to create a building block on jestor. It’s a table.
A table will have a name, such as “Clients”, and many fields such as “name”, “phone” or “email.” It’ll look something like this.
Similarly, a “Sales” table will have fields such as “ID”, “amount” and “date.” However, it’ll also have a field to point a sale to a client.
Can you see that the “Client” field is actually a link to a client record? In fact, if we click on it, the client’s information will be shown.
And if we click on the “Connected” icon on the upper right part, we’ll be able to see every sale connected to that client.
This is a much better way to connect information. Now the client’s record will always be neatly organized (only contact information), but I can have access to every sale with a couple of clicks, and access the client’s information from a sale just as easily.
All of this is done through the “Connect tables” field.
All you have to do is create a “Connect tables” field and point to the table you want to. It’s as simple as that: all the rest is automatically created by jestor.
To get a good grasp on how to structure your tables like building blocks, do the following steps:
- Create the “Clients” and “Sales” tables mentioned above on your account. Check the Connected area of the Client card and see how it works.
- Create a table named “Items Bought” and follow the structure below. You should be able to see items bought on a sale on the Connected area of the sale.
[Items Bought: name of the product, quantity, amount, ↑sale]
- Now for a more abstract exercise: say you have projects that you want to control on a kanban. You also want to control tasks that are directly related to specific projects. How would you do it? Create that on your jestor.
You can also check out how we solved these exercises on our Class One template in the store).