I’m considering restructuring some core functionality of my app because it’s fairly slow and DB intensive, and I think JSON might be a good solution. I hoping for some guidance to make good decisions and understand limitations up front.
The function I’m refactoring is essentially storing and displaying table data, where the number of rows and columns are both variable/unknows before display. This is done for Products, and each will have a different number of rows/columns (these are determined by the site admin and will change, though not often). Right now, this is how it looks when a User is editing:
You can see that there are headers, and columns are of varying types that require varying inputs, for example multiselect, dropdown, text, etc. Right now I’m not using any table plugin, this is just 2 nested RGs - a RG of rows, each row containing an RG of fields. Within a table, the rows will never have different numbers of cells.
Right now I have tables for:
- Product Group - corresponds to the overall grouping and has some other data
- Product Instances - corresponds to rows
- Questions - corresponds to columns types and includes info of field and options
- Answers - corresponds to the actual input in each cell
I also need to track all revisions. That doesn’t mean every field change, but the User will click “New Rev”, make a set of edits, then click Save, and that updated data becomes the most recent rev. I need to be able to still display all previous save points as revisions by number.
If you’ve read this far thank you!
Now, here’s what I’m considering:
A JSON object to hold everything for each revision. Something like:
Two JSON objects with each revision, one defining the input data and one defining the column data. Something like:
Right now it seems like Option 2, even though it’s using 2 different objects (or perhaps because it is) would be the simpler and more robust option.
My questions are:
Any drawbacks or advantages I should consider with either of these options?
Is there another way to do this I’m not considering?
Is it possible / performant to look for fields within this JSON if it’s stored as a string in Bubble’s DB?
Am I crazy?
Thank you so much for reading!