Using databases to create relationships with attributes is quite a common use case, so I made a quick explainer video
Enjoy!
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? 
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.
I was worried that I had missed something with the new pinning and views and all those awesome improvements. 
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 
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. 
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.
3 Likes
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
donāt exist yet 
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.
8 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 
a 
and a 
!!!
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.