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