Until native relationship properties are available, here’s a quick explainer about the use of ‘helper databases’ as an alternative
1 Like
Is it possible to get some explanation on how it works under the hood if not with a junction table? Thanks!
I don’t know the details, and I won’t try and convince our devs to come on here and explain.
1 Like
The Problem
Every once in a while I run into a limitation in the UI where I need 3 databases all working together.
I will give an example:
I have 3 databases Account, Contact, and Role (think of this as their title or position).
A Contact can be associated with multiple Accounts. An account can be associated with multiple Contacts. Every Contact has a role associated with it per account that it is associated with.
e.g.
Account 1: Random Company, INC; Contact: John Smith; Role: President.
Account 2: Another Company, LLC; Contact: John Smith; Role: Consultant.
In the UI the only way to display this is to have the Role Table be the table to display on the Account and Contact Entities. The problem with this is that when I am on the Account Entity I want to add a contact to the Account (not a role). Even though I would still like to add a role to the contact. Again, when I am on the Contact Entity, I would like to add the Contact to an Account. And specify the Role.
This is further desired because when I am on the Contact and Account Entity I often want to open the Contact or Account entity side by side. The Role is just extra data that is relevant but I don’t need an Entiy view for it.
A solution
This can be solved with multiple related fields. Meaning that within a many-to-many relationship, fields can be defined on an entity to be associated with that relationship.
Is this the same (similar?) to this?
Yes, I believe we are speaking to the same issue.
Basically the issue is that in a many-to-many relationship, we have fields that need to be multiple-response and associated with the Relationship field.
e.g.
When Entity 1 is linked to Entity 2 fields A, B, C can to be answered, but must be associated with Entity 2.
When Entity 1 is linked to Entity 3 fields A, B, C can to be answered, but must be associated with Entity 3.
I have run into this a few more times. I am currently building an inventory system and the number of databases and complexity could be improved with this feature. I’ll give a few use cases.
I currently have 12 databases in the inventory system. Three required databases:
- Inventory - the place the item is stored
- Item type - the class or group of related items
- Item - represents the item
One issue I have with these 3 databases is that I cannot relate them together because I need to set a minimum and maximum quantity of each item & item type per inventory. I also need the stock of an item per inventory. So that means that I now need 2 more databases:
- Inventory Item Type - A mirror of Item type with the added fields related to the linked inventory
- Inventory Item - A mirror of Item with the added fields related to the linked inventory
I then need to add suppliers to an item. Once again I need to a field unique to each supplier for a given item “Supplier SKU” to do this I must setup another database, Supplier Item that has 3 fields: Supplier, Item, Supplier SKU
Relation properties or Multiple (to-many) Relationship Fields would reduce the number of needed database in my inventory system by at least 25%
This would be really helpful for us! Currently we are using junction tables as a makeshift solution.
Database A
Database B
Database AB
→ Relation to A
→ Relation to B
all other required fields that would be needed to define the relationship between A and B
Fibery already supports relationship attributes, right?
In my very recent experience with Fibery, most of its relevant components already support relationship attributees, particularly when compared to other Online Work OS platforms where they seem to forget that relationships can have attributes within the context of a relationship.
In Fibery:
- the formula columns can access any related entity along the relation chain.
- the formula return data types can be entities or their attributes such as dates, date ranges, numbers, locations, etc.
- the formula columns are usable in filters, grouping, sorting and reporting.
- and relationship filters work on any field or entity in the relation chain, to allow subsets of a relationship to be associable in different columns.
I am relatively new to Fibery, but as someone with a normalisation obsession 
usually hovering between 3NF* and 4NF*, who cannot resist extending relationships with attributes on junction tables, and enriching simple values (i.e. options in a select) with extended attributes on reference/domain tables, it seems like Fibery covers the need, except for parts of the corresponding UX.
Please tell me if I am missing something. 
* 
Sorry for the jargon, I have linked them to their Wikipedia article, just in case.
If Fibery does support this, then is UX the issue?
The issue for me is more a matter of UX, where instead of setting up separate junction tables, relationship filters, formulas, etc., we could just set up the relationship and its attributes in an improved set of UI components, seriously advancing the UX of setting up and using these attributes.
An example of UI changes to improve the UX:
I think that is one way to view this.
At the moment I can model the data in as many databases as needed and specify the needed relationships to build just about any data model. But the UI has limitations in how it can be configured.
The current setup has its disadvantages. For example:
- The number of databases that are needed is increased, which increases complexity [Not a huge deal]
- Duplicate work for creating lookup fields to the related databases [Also not a huge deal]
- Lookup fields are not editable, so you are not able to use lookup fields if the field needs to be edited [ Editable Lookup Fields ]
- For editable fields you need to create automations that update the fields on all other entities [This is a lot of work]
- Navigation is limited. Depending on the relationship structure, clicking on an relationship within an entity does not always take you to desired entity.
- You could circumvent the navigation issue by allowing for fields to display different data depending on context. [ Formula fields that are filtered by the context they're viewed in ]
If you could do 3 and 6, then relationship properties might not be needed at all.
2 Likes
Great list!
Let’s take the following scenario:
-
Students and Courses have a many-to-many relationship.
-
To have relationship properties, we create an Enrolments table with two many-to-one relation fields (Student and Course), and other attributes like Status, Enrolment Date, Grade. For good measure, we can even pull in the Course.Period date range field via a lookup.
-
Now, if needed (for filtering, sorting, reporting, etc.), we could pull enrolment grade, enrolment status or even enrolled course period columns into lookup fields on the Students table.
As highlighted in the screenshot below (of the entity view, though it is also possible in list view), all fields of the junction table, and most fields of a lookup field (Courses pulled in from the junction table) are editable as long as they are in “relationship list view”.
Am I missing something something here, or is the issue only present when the relationship is many-to-one, and therefore the relationship list view is not available to expose the related entity’s fields in the UI and allow them to be edited?
In screenshot below, see how the red lookup field pulled in from the many-to-one Course entity (on the Enrolments table) is not editable, and it cannot be added as an editable field in the yellow part, since the many-to-one relation field does not use “relationship list view”. Ironically, the fields of the Course entity are exposes in “relationship list view” in the lookup field on the Student Entity, since each Student can have many Enrolments, and even though each Enrolment can have one Course, this means each Student can have many Courses through Enrolment.
@Chr1sG, perhaps first steps are the following ones since the functionality already exists in the *-to-many relation fields?
- to change the UI component for entity-returning formula and lookup fields to the same as *-to-many relation fields, where the entities can be shown in many different views, grouping, sorting, filtering, etc.
- to change the UI component for *-to-one relation fields to the same as the “relation list view” currently used for entity-returning formula and lookup fields, since it allows at least the addition of fields to the UI.
In your scenario the junction table works fine as the table displayed on the entity. Although no field that is a lookup field on the junction table is editable, as that feature doesn’t exist.
In some scenarios it’s not quite that simple. I’ll give one example. 3 Databases (DB) Accounts, Contacts, Roles (a junction table with 3 editable fields: Account, Contact, Title, & other lookup fields).
- Contacts and Accounts is many to many
- Contacts and Roles is many to one
- Accounts and Roles is many to one
In this scenario we have a navigation issue. I never want to open the Role. When I am on the Contact and I click on a Role I want it to open the associated Account. And when I am on the Account the Roles relation shows a directory of Contacts associated with the account. When I click on a Role I want it to open the Contact (not the Role).
We also have the editable lookup fields issue. If I want to display the email and phone number of the Contact, I can do so, but it either takes uneditable lookup fields or new duplicated fields and automation rules to make work. At least this issue has a work around.
We also have another issue of when I am on the account I want to create/ add a new contact with a title. I am essentially required to create a role and include all the contact fields that I want to show on the role.
Having some sort of context based formulas / fields would allow us to to use the Contacts instead of the Roles on the Account and would alleviate this issue in part.
Again I think you are spot on with this being a UI limitation rather than an issue on the DB side.
1 Like
Thanks for clarifying your scenario. I have recreated it in a test space to make sure I was not missing something. Below, I am going to go through each issue to see if I can address them.
I will base my answers on accessing Related Contacts on a given Account’s entity view, but everything applies in the reverse direction, too.
But, clicking on a role should never open an associated account, since they are different types of entities. What if you want to edit the role, for example, the period, the actual type of role, or even the contact assigned to the role? That should be possible to do, and is, as shown in the video.
I do understand that in most cases, you may need to quickly modify the fields of either end of this many-to-many relationship, i.e. an account’s fields when on a contact view, or a contact’s fields when an account view.
This is possible as shown in the video below, as long as you do two things:
- Establish the Contact-Account relationship by simply adding a lookup field on Contacts to look up Accounts through Roles.
- This enables you to set Contacts as a level in the table views of the Roles Relation Field on Accounts, and it correctly shows only Contacts that are related to to the Account being viewed.
This is how in the video I am able to edit Contact Names, Emails and Phone numbers right from within the Roles Relation Field on Accounts.
But there are a few problems, which make me think my ability to edit related Contacts on the Account Entity View, is more of an unexpected, accidental side-effect, than a feature.
- It only is possible if the corresponding lookup field is set up on Contacts, even though we do not use the lookup field on the Accounts table, and it does not establish a new relation.
- If we do not add the corresponding lookup field on Contacts, then All Contacts, regardless of relation to the current Account, show up in the Roles Relation field on the Account Entity View.
- When adding a contact in that view (presumably because it is NOT nested-under /connected-to a Role, it results in this error:
Cannot add collection items to readonly "Testdb/Roles Accounts" *(this is the lookup field we had set up)* field for "Testdb/Contacts" database.
- As I had mentioned earlier, when a relationship is many-to-one, such as the relationship between Role and Contacts, then we cannot set Contacts as the sub-level of Role in the table view. We can still set Contacts as the top-level entity, which is what I have done in the video, but I would rather be able to edit a Contact nested under its Role, rather than directly like I show in the video.
In reality you should just create a new role, and in its Contact relation field, you create a new Contact. Once created you can edit that Contact in the list above shown in the video. There is no need to expose the Contacts Fields on the Role itself. Though I may be missing something here in which your use case differs. Sorry if that is the case. 
I just tested to see how it would work if I change the junction table, Roles in this case, to have a many-to-many relationship with Contacts, instead of many-to-one, and except for the fact that this is not an architecturally sound relationship, and that it will incorrectly allow more than one Contact per Role, it solves the rest of the issues:
- It allows a nested level under Roles inside the Account Entity View.
- It allows the creation of a new Contact in that level by clicking on a Role and selecting “New Contact Inside”.
- It does NOT require the hack of having the “Role Accounts” lookup field on Contacts.
The two issues are:
- it is a many-to-many relationship between the junction table and the entity tables.
- And a bug: It allows the creation of a new Contact in the same “Contacts as Top Level” view of the Roles Field on the Account Entity View, instead of raising the error in my previous post, but assigns a random existing Role to that new Contact (the first Role it can).
To fix the first, I would replicate almost the entire functionality that is already there for the many-to-many relations, to the many-to-one relations, and then modify the UI to show the nested row’s columns on the same row as the junction fields, since there is no need or logic to nest it, given that it is a many-to-one relation.
Hope this helps.