Best-Practices for relations in SQL and editing

[gryere25]

Hello everyone

I have a complex table structure. So for many "designs" it is needed to present joined data as well. Because I don't really understand how to do it, I use pre-joined data. That means, I create a SQL query and use it as a data source.

But this data of course is not editable. Budibase can't know, that there is a SQL backend behind and how to fill the tables.

What is the best practice to work with data of a relational model, where you have present in the "design" some parts of the joined tables? I know, that working with a label exist. But usually I need several columns of the joined tables to be shown.

Is pre-joining and manually sorting data back the only "right" way?

[ConorWebb96]

Hey @gryere25,

It’s honestly completely up to you! Writing a custom query can sometimes be easier, especially if you have a complex data structure and must display relational data more than one level deep.

If your database isn't too complex, you can map your relationships directly in Budibase. Then, you can use the view column feature to pull in related values:

Even when using custom queries, you can still implement CRUD functionality, but the Budibase table component doesn’t support this out of the box with custom queries.

Instead, consider storing the unique record ID in App State, either using the update state action or as a URL binding when navigating with the navigate to action. You can then pass this ID into an update form that uses your base tables schema to modify the selected record.

You can even pass multiple values to a new page with multiple forms, enabling edits to various parts of your data structure.

Hope this helps!