Helper databases

Using databases to create relationships with attributes is quite a common use case, so I made a quick explainer video

Enjoy!

5 Likes

Thanks for sharing this, @Chr1sG!

I had some of these databases set up before, like for entity ordinality and data formats. However, it seemed to me that I always have to try to mask that extra hop over that helper database and that there was no way to mark the helper database as a ā€œhelperā€ā€¦ did anything in the current views change that now allows for this to work better?

I just felt that the extra hop confused people more than anything at least before.

Also, any plans to add native rich properties onto relationships? :slight_smile:

Thanks!

2 Likes

No recent changes related to this, but not everyone discovers the ability to change the relation field view from list to table, and then hide the name field, and only show the relevant fields on the helper database. I feel like this alone makes it much easier to hop between the task and user (and vice versa).

2 Likes

Cool! Thanks for clarifying. :slight_smile:
I was worried that I had missed something with the new pinning and views and all those awesome improvements. :wink:

I thought it was less clear than it should be in the video what you were doing and why. Either itā€™s for an audience that is already familiar with the concept and this is more of a ā€œhereā€™s a Fibery trick that can make this less clunkyā€ (in which case itā€™s not really approached that way in the rest of the video), or itā€™s for a more general audience that is unfamiliar with DB admin concepts like ā€œjunction tablesā€ in which case the video isnā€™t clear enough overall to actually help them understand it fully. I know these are supposed to be short intros to concepts and methods, but I think this one needs a bit more in-depth explanation, as well as to at least make one reference to the more classical terms for this kind of approach.

1 Like

Chris, this video is fantastic and Iā€™m hoping your entire team adopts your approach. Why?

The little no audio screen thingies (theyā€™re not presentations) are awful - at least for me. When I communicate, I do so with both audio & video, and as a Fibery fan I have never before come across the ā€œscreen movement-onlyā€ way you all communicate in your various help pages and tutorialsā€¦ well, basically everythingā€¦ with no audioā€¦ and with no means to control it ala Youtube (maybe this is another non-mac thing).

I applaud you for including your voice - my brain can understand what youā€™re communicating to me and even better I do not have to pause/play over and over again to (perhaps) understand it - while the ā€œscreen thingyā€ itself does everything it can (perhaps because itā€™s an animated gif using circa-1970ā€™s technology) to ensure that I donā€™t (it must be my issues; is it this browser, this setting, my mouse?).

While my brain is certainly not Fiberyā€™s intended audience, this is just basic stuff.

I hope today can be heralded as the day Fibery chooses to stop communicating with a 1950ā€™s television set showing an etch-a-sketch with the volume off.

3 Likes

Actually, the video series Iā€™m doing is internally referred to as ā€œtipsā€™nā€™tricksā€ and is targeted at existing users who know the Fibery basics, but might get an ā€˜aha, didnā€™t think of thatā€™ moment.
Some people will say ā€œyeah, so what? I already knew all thatā€ and maybe some will say ā€œhang on, what is he on about, and how did he add these fields?ā€
Cā€™est la vie :man_shrugging:

1 Like

Oh I see. Hmm. Well hopefully there will also be a series for people who donā€™t already understand this stuff and arenā€™t existing users. :sweat_smile:

What is ā€˜this stuffā€™?

In this helper databases video, it would have really helped me if you visually showed a ā€œbefore stateā€ example, where you contextually (in the form of a screenshot or screen recording) describe what the desire or limitation was before you decided that creating a helper database was the required solution.

You tried to do that with some text on the screen before diving into the ā€œso this is how to do itā€ part and then you show the ā€œafter stateā€ but I struggled to follow along because I didnā€™t understand the use case well enough. In other words, you showed the answer and the work you did to solve it, but didnā€™t really share the original problem. :slight_smile:

1 Like

In this case specifically the setup and use of ā€œhelper databasesā€. How the benefits you outlined in your video are actually achieved. I get that it might seem obvious/intuitive to you and others, but I actually donā€™t use any databases in this way partly because Iā€™ve never felt a good grasp on how to set them up and their strengths and weaknesses. It does sound out of the scope of what youā€™re doing here though, and maybe is in the documentation already and I just havenā€™t focused on and looked for it yet. Itā€™s not an urgent need for me, I just feel like Iā€™m probably/possibly missing some benefit (or donā€™t understand the approach well enough to realize the trade-offs arenā€™t worth it for me with confidence).

Also very much this:

I guess the on screen text wasnā€™t enough.

The example use case is that merely being able to say that a User is assigned to a Task might not be enough - you might want to say that a User is assigned to a Task for 50% from 1st Sep til 30th October, and is taking the role of ā€˜ownerā€™.

Well, to be clear for myself, I did understand that part. What I missed was how exactly it works and what the limitations are compared to ā€œrelation propertiesā€. Which I get was beyond the scope of the video.

Well, relation properties donā€™t exist, so it has no limitations in comparison(!)

Instead of a single many-to-many relation (with no attributes)

Task ā†” User

you have an intermediate helper db with two many-to-one relations

Task ā†’ helper ā† User

Maybe that explanation part of the video was too rushed :man_shrugging:

donā€™t exist yet :crossed_fingers: :upside_down_face:

1 Like

Of courseā€¦ But that function exists elsewhere and there are generally understood capabilities or expectations people have of it. Details will vary, but the core functional concept vs. this actual implementation is what I was curious to see compared. It seems possible to me if one has a good knowledge of this implementation and the concept of ā€œrelation propertiesā€, or a specific example of it as expressed in another app (which one could invoke for comparison). But get not wanting to do that comparison.

Yes, I understood this part. But what specifically does this help you do and why? How is this a replacement for ā€œrelation propertiesā€? I think the part youā€™re missing is e.g. "then you create a bunch of the ā€˜helperā€™ DB entities for the properties you want the Tasks/Users to have, and then you create Lookup Fields to show those things on the Entity where you want that ā€œrelation propertyā€ā€¦ Or something. Thatā€™s the best explanation I can give as to what I think is intended, but I may be totally off and thatā€™s my point. Iā€™m not confident that I get it. Itā€™s not the how but the effect and use of it in practice that was not, IMHO, clearly demonstrated/communicated.

If anyone is looking for an alternative example: my most common use case for Helper databases is for managing events.
You can link a contact directly to an event - like the Google Calendar Integration does.

However when managing multiple events and a CRM full of people/organizations, you often need to track whether a person was invited or whether they completed registration, whether they attended, or whether they cancelled. If you link a contact to an event then you would need to track attendance statuses on the contact record - which would crowd the contact record.

Instead an Attendance database can function as a helper table that allows you to create properties for each attendance. The core data model is to have an ID for the attendance record, a link to the contact, a link to the event, and a state field for tracking the progression of a contactā€™s attendance.

Helper tables are almost always the best solution when you need to build charts or reports about information with more than one connection.

4 Likes

I find value in learning how to think about Fibery structures, and for that, examples are great, but as a means, not an end.

Iā€™d love to hear more about how you, @ChrisG think through database structures, particularly where there is a lot of abstraction required. So rather than seeing the result of your thinking, giving examples of the process of thought itself.

Personally, I find the progression into and out of whatā€™s being referred to as helper databases, or what Iā€™d previously thought of as junction tables, to be a natural next step when my thinking hits a dead end.

I start with whatā€™s clear and see how far it can take me.

When I hit a dead end, Iā€™ll extend, creating a new database. I donā€™t really care whether the helper database is a stepping stone into something else or an end in itself. That will naturally reveal itself. I find I can transition through helper databases into more complex structures or just end with them.

As a basic example, thinking about customers buying productsā€¦

For me, I start with whatā€™s clear.

A CONTACT database to house the people and a PRODUCTS database to house the products. Thatā€™s simple enough.

A CONTACT can obviously buy many PRODUCTS and a PRODUCT can be purchased by many CONTACTS.

So I have a many-to-many relationship between the CONTACT and PRODUCT databases. That seems clear.

Next, where can I store all of the different dates and times CONTACTS have purchased PRODUCTS?

It doesnā€™t seem intuitive do it in the PRODUCT database, so perhaps I should do it on the CONTACT database. That seems to makes more sense.

So I can add a date field to the CONTACT database. That works.

But hold on a minute. What if they buy on two separate occasions? Perhaps I add two separate date fields to the CONTACT databaseā€¦ or a whole lot of date fields.

Hold onā€¦ this is starting to feel wrong. Iā€™m hitting a dead end.

I need help.

I need an explicit way to define the relationship between CONTACTS and PRODUCTS so I can record the date. To me, this starts as a simple helper database.

The obvious solution might be to create a DATE database that sits between CONTACT and PRODUCT, which would allow me to record the date of each purchase interaction between CONTACT and PRODUCT.

Prior to creating the new database, I ask myself the question, what else do I need to record other than the date?

It then becomes clear that itā€™s not just the date. I also need to record the quantity of products ordered, the price, whether it has been paid, any tax etc.

Okay, glad I asked myself that question. Iā€™m not going to call this database DATE. I need a new name.

I might call it ORDER instead.

That now helps me record when my CONTACTS buy PRODUCTS.

Now itā€™s all fitting together. I can put the date field on the ORDER database to record when CONTACTS purchase PRODUCTS.

At this stage, ORDERS is like my helper database that sits between CONTACTS and PRODUCTS. I can store in it everything about that relationship.

So now, instead of a CONTACT having many PRODUCTS, a CONTACT has many ORDERS and an ORDER belongs to only one CONTACT, and an ORDER has many PRODUCTS and a PRODUCT can be ordered many times.

This is perfect. Iā€™m unstuck and I feel like the recording of the data is more organized.

Now I also need to store the quantity of PRODUCTS the CONTACT ordered.

Obviously I store that in the ORDER database. Thatā€™s what itā€™s for, right?

So I create a field named Quantity on the ORDER table.

So when a CONTACT places an ORDER I record the quantity of the product in the Quantity field in the ORDER database.

But hold on a minuteā€¦ what if they order two different PRODUCTS at the same time and a different quantity of each in the same ORDER?

I need to add two different Quantity fields to the ORDER database. Actually, I need to add a long list of Quantity fields to record the quantities each PRODUCT.

This feels familiar.

Iā€™m at a dead end again.

I need help.

What to do at a dead end? Create a new database to explicitly define the relationship.

Now Iā€™m dealing with the relationship between ORDERS and PRODUCTS.

I have a bright idea. I need a QUANTITY database.

Before I create it, I ask myself the same question again, what else do I need to record other than the quantity?

Well, I also need to record the price each product sold for.

I think to myself, perhaps I can just record the price of the PRODUCT in the PRODUCTS database and then access it straight from there. That makes sense.

Hmmā€¦ but can the same PRODUCT sell for different prices at different times (date is already in the ORDER database)? And can the same PRODUCT be sold to different CONTACTS for different prices (remember the interactions between CONTACTS and PRODUCTS is defined already through the ORDER database)?

The answer to both is yes. So I decide price will be defined along with quantity in the new database.

Great, Iā€™m back on track.

So the relationship PRODUCT has with ORDER, needs both Quantity and Price defined in it.

I create a new database to define that relationship and call it LINE ITEM.

So an ORDER has many LINE ITEMS and a LINE ITEM belongs to a single ORDER.

I record the Quantity and Price in the LINE ITEM database.

And so this cycle continues on and on.

This is just my process of thinking about it, but Iā€™d love to learn better ways for more complex problems.

Iā€™d love to see any videos which go more deeply into the thought process behind a solution, rather than just the solution itself, particularly where there is a lot of abstraction.

Like a deeper walk through of your ground up thinking that went into creating your older inventory management video, @ChrisG. Or your entire production line and warehouse management system.

I see how youā€™re explaining the thinking in the videos youā€™ve been doing, but Iā€™d love to see a deeper dive into more layers of abstraction.

6 Likes

Well, as it happens, my thought process is pretty close to what you have described above!
I wish I could give your post a :clap: a :+1: and a :heart: !!!
Itā€™s a brilliant explanation of the process.

Maybe the only difference is that I tend to do my initial thinking ā€˜offlineā€™ (with pen and paper) to save myself from creating dbs and relations which I might end up having to modify/delete.
But ultimately, every complex workspace I have ever set up has started out in much the same way.

My recent video about how to set up a workspace was an attempt to explain the initial process in a fairly simple way, and this video on helper dbs was hoped to go some way to build on that initial concept, so that people who got into Fibery would end up reaching the type of thinking you described above.

As it is, I know that people learn in different ways, so I deliberately kept each of the videos relatively short, to avoid making them to boring.

3 Likes

The example I shared with customers buying products is fairly straightforward.

When I look at your older, simplified inventory management video, thatā€™s less straightforward. There are only four database types and when explained, it all makes sense.

BUTā€¦ not so simple to sit down with a blank piece of paper and come up with from scratch.

I donā€™t get how your thinking came up with it.

In it you have databases for:

ITEM (pretty straightforward)
STEP
EFFECT
OCCURRENCE

These are far less obvious databases when compared to CONTACT, PRODUCT, ORDER and LINE ITEM.

In your inventory system you are setting up a predefined structure with the step and effect databases and then instantiating that structure through the occurrence database, which results in stock levels on the item database.

Thatā€™s fundamentally different from the example I gave. Itā€™s the result of deeper, more abstract thinking. I canā€™t see how the thinking I shared would result in that solution.

It always makes sense after the fact. Seeing it explained after youā€™ve defined the solution makes it appear like common sense. Yet sitting with a blank sheet of paper and conceptualising it from scratch is a different matter.

I would have started with a PRODUCT database (to store the cheese sandwiches and any other sellable products) and a COMPONENT database (to store the cheese, bread etc.), having a PRODUCT made up of many COMPONENTS.

Then Iā€™d be thinking about quantities and so on.

So right from the beginning my thinking was in a different direction. I probably would have come to the point of having an EVENT database for recording sales, purchases or creating a product from components.

However, I canā€™t see the line of thinking that got it all linked and working the way you did. It was a very elegant solution.

I have other systems in Fibery that instantiate predefined structures, yet Iā€™m not entirely clear on thought rules to apply for when that should or shouldnā€™t be used. Iā€™ve just created them intuitively through testing other ways.

A key insight in your inventory setup is that a stock level of any given item is just the accumulated total of all previously instantiated occurrences of steps and the changes associated with them.

Did you start with that insight? Or did that arise as a consequence of prior thought?

Do you see what Iā€™m getting at?

What was the path from zero to done? How did the logic build? How was it all conceptualised?

Iā€™d love to understand. I find it fascinating and Iā€™d like to apply it to other things.

BTW, for anyone reading, the video Iā€™m referring to was taken down off YouTube as itā€™s an older video when Fibery looked different. If anyoneā€™s interested in seeing it Iā€™m sure Polina still has a copy lying around somewhere.

Hope that helps to clarify what I was getting at.