One to Many Reference/Relation | Array Storage

Hello!

Hope you all are well! (basic question)

Ive been referring mainly to this forum post, but I just wanted to triple check I’m understanding correctly.

In bubble, you can create a relationship on a table, where one row, can reference multiple other rows, for example.
Invoice’ references an array of ‘invoice items’ (I assumed bubble was doing it like this, [uuid1, uuid2, uuid3 and so on]
But if I’m understanding correctly, its actually a junction table for one-to-many and many-to-many relationships, where there is two fk for tables, example the ‘invoice’ and ‘invoice item’ table.

So in wappler if I want a one-to-many, I should use the multi-reference in wappler.
Correct?

Few more sub-questions:

If I wanted example to remove a relation, (sometimes I have to do it manually in bubble), I was to remove a relation, example removing an invoice item from an invoice. I would need to find all rows in the junction table of the relationships I’m talking about, and then delete the relation I don’t want right? As the relations will be completely handled by the junction table and not on the tables directly.

Another question, regarding arrays, just for strings. I noticed there isn’t an option to say this should be an array or something. Guessing its similar above, where bubble was just displaying it in easier way to understand, where when storing the string/text, I should format it as a list like this/similar:
[value1, value2, value 3]
Then when I want to use it as a list, id need to format by delimiting or something?
Or using the json array data field? (that way its like auto formatted into a list when its queried, is that correct?)

Also finally, storing an array of dates, instead of using a date field, I would store it in list of strings or the json array? With the only downside to that is, I won’t be able to filter with the dates in that array. Where the date field is more specific for filtering/querying?

Sorry for these basic questions, I need to understand these sort of basic things before I can even move properly. I have a million small questions but trying my best to use what is available :slight_smile:

Cheers, Haydn

Okay im pretty sure i had the one to many completely wrong, I was thinking interms of linking from invoice table to the ‘invoice items table’, when instead I should query from ‘invoice items’ inv fk to the ‘invoice’ pk.

Looked at this video:

Still no one was able to help you?

We’re leaving bubble so I’m hoping that we could get similar questions actually answered in the future.

Did you ever figure it out?

Hey @Wappster

Sounds like you have live data you need to migrate and sadly cant start fresh?
Thats what ive had to do, and bubble.io doesnt make it easy because they dont have normal data structure compared to the standards of major databases.

There are people significantly smarter than me here regarding sql (language communicate with database).
With bubble i assumed how bubble.io structured their database (relationships in arrays), was the best way, which it is not. I recommend being prepared to kinda forget how you do some of the things in bubble.io, as its not good design and more just working within limits of bubble.io, not good practices at all.

Idk if you have any knowledge of databases. Happy to answer any questions.
But basically instead of an array, your going to take that array and create an new table with it, all this table does is hold relationship information (can do more but depends on db design).
Quick video explaining (im not best at this but hoepfuly will help)
EDIT: the syntax for array in excel, thats not correct. do dont pay attention to syntax.

The other sub questions i had.
Removing relations, many different ways to do this. But youll just need to find the row of data for relationships in the junction table and delete that row (make sure has nothing importnat your deleting)
Updating relationships, theres few different methods. Probs easiest to understand is you delete all current relationships for that object, and then just create all relationships you want again. (over simplification)

My question regarding array, honestly cant remmeber what im referring too and a bit confused haha

Storing dates in array, if talking about database, im just trying not to store like anything in array anymore. (i still do) but majority of use cases for list in bubble.io, youll want to separate in separate table and rows. Well atleast thats what ive done.

1 Like