Are one-to-one relationships possible?

[Grayda]

I'm having a look at Budibase to potentially replace a custom video game collection tracker I wrote in Laravel last year, and so far it ticks most of the boxes I'm after, but I'm wondering if one-to-one relationships are possible at all.

In my existing tracker, I have two tables called Purchase and Sale. When I buy or sell an item, My tracker creates a new row in the relevant table which says when I bought / sold the item, for how much, and to whom, as well as the ID of a row in the Items table

Logically, there should only ever be one purchase and one sale for any one item, because I can't sell one Super Nintendo to two people, nor can I buy one Super Nintendo from two people, so I'd like a one-to-one relationship, but it seems like Budibase (nor Nocodb, nor Baserow) don't have one-to-one available.

Is there a reason for this? Should I put in a request to have the feature added?

A workaround would be for me to just add the columns from the Purchase and Sale tables to the Items table, but a one-to-one relationship would be nice.

[erakastrati]

Hi @Grayda, I had the same problem once. I think that one potential way would be:

• go to the Purchase table and create a 'one to many relationship' with Sale
• go to the Sale table and create a 'one to many relationship' with Purchase.
  After you finish those two steps, go to the design section and delete the automatic shown fields. Thus you can have a one-to-one relationship.
  p.s not sure if it works best, but I think we don't have any other potential option.

[mjashanks]

Hey @Grayda - the reason for us not having this relationship type is because it can be accomplished by having everything in one table. I can see why you would want to separate them, but ultimately it's a "Nice to have". Therefore, other features take priority, when we choose what to build.

@erakastrati thanks for your suggestion :)

[Grayda]

2. Re-select in the order that you want them to appear

Ah. I tried that, but I added an auto column (specifically, Created At), and that always appeared as the last column, which had me a bit confused.

Thanks for getting back to me regarding all this, I really appreciate it, and it's looking more and more like Budibase is the right fit for my needs.

[mjashanks]

Ah - I see, yes the Auto Columns always come at the end. I'd call that a bug!

Great to hear David, keep the feedback coming!

[JMLX42]

the reason for us not having this relationship type is because it can be accomplished by having everything in one table.

@mjashanks Since it's impossible to properly update a single row using bindings in lots of cases (cf #3344) then having everything in one table is an issue.

To circumvent that I wanted to have an Automation that:

• When row R1 is Row Created in table A
• Create Row R2 connected to R1 in table B

But since the one-to-one relationship does not exist, it is impossible too.

[mjashanks]

We do need to fix that automation issue, that should be a priority.

In the meantime - how "row R1" being saved? Is it happening via a Button Click? If so, could you use the column overrides on the "Save Row" action to set more fields on table A..

In the example above, I am setting "Region" to always be "Europe", when the row is saved.

[JMLX42]

In the example above, I am setting "Region" to always be "Europe", when the row is saved.

@mjashanks thank you for the update/ But I need something way more complex since my goal is to generate an RSA key pair and store it in the DB.

[tluyben]

Not sure if this is related; but with a relationship column, if you sort, it sorts on 'something' but not what you expect (namely, the display field that is showing from the related table); 1-1 can do that, for other relations that might be harder. But then maybe it is better to remove the sort header for relationship fields totally because now it just seems to sort on something invisible (the id? or?). Or maybe I'm doing something wrong.

[mjashanks]

Yea, I see that - it's probably sorting by the relationship array, which will be pretty random. Honestly, I am not sure this is technically possible for BB internal tables, although would be ok with SQL.

We should disallow this for now.

Logged this here: #3182

[rienheuver]

Is it just me that got thrown off by this documentation? It surely implies there's one-to-one relations right?
https://docs.budibase.com/docs/relationships

[WilliamDEdwards]

No, and it does...

[melohagan]

I've since removed the One-One section. The relationship modal also explicitly states "One to many" or "Many to Many" now as well.