Need guidance on how to implement this feature with the database structure - suggestions and visual included

In summary, I have an app which records lots of project details across 8 databases (each database is split into its own specialist area of the project). However, an important point to note is that each of these 8 databases have a secondary ‘storage’ database which contains all of the previously saved entries by the user whenever they hit the ‘save’ button on the form. The main reason is to allow restoration of previous entries.

Here is an example visually.

Now, when this project is ‘submitted’ for approval to a third party is needs to ideally stay as it is as users need to see what was originally submitted. Documents are auto-generated on the back of a submission.

However, if amendments need to be made to the project in future (usually to update information or if the project details have changed), users will naturally need to go back to the Main Database as shown above to change these values.

This is where I need help:

Initially I was thinking that I might need to copy all the contents of the main database and create a secondary database called Main Database - Amendment 1 where all the original contents are saved from the original Main Database but in this Amendment 1 DB they can be updated and saved separately whilst still maintaining the original set of form data.

So maybe something like this:

What is the best way to efficient achieve this? I did think about create a new project within the Main DB, but the problem is the project name is linked across each other, and creating another row in the db with the same db would just conflict with that?

Hi, some things to think about below. For simplicity I’m just talking about 1 of the 8 databases.

  • You can have multiple entries with the same name. Bubble assigns a unique ID to each entry to differentiate it.

  • Simplest way is to store all versions of an entry in the same database. And you can “lock” an entry if it’s been submitted by creating a field to note that.

  • However, you may need to split the database (one database of latest entries, and one database of all prior versions) if: a) you anticipate a large number of entries, and b) your filtering ends up being client side (vs server side).

Because of quirks in how Bubble searches and displays data, you should figure out how you are searching and displaying info before you finalize your database structure.

Also, I’m not sure if each entry is data heavy or light. If they are data heavy (requiring a lot of text, files or images) and you anticipate a lot of entries and versions, you may need to rethink things. Bubble is rethinking its pricing structure and it’s possible they start slapping limits on the number of database entries and/or size.

1 Like

Thanks for your reply.

The multiple entries of the same thing is actually being pulled from one central source, or so that’s the way I’ve intended it. This is the project name (because this remains a constant throughout all databases). So the relationship is linked both ways between a central database and the other 8.

Yes, essentially creating a new row in the database with another field to identify it might be the solution, but as you’ll know with this, I need to try and futurerpoof it and have the foresight to know it will work structurally and with existing and new features in the near future.

The databases are already split - so the 8 databases have another ‘mirror’ which simply contain all the saved entries. So the main database which saves the current version at all times will just have one row of the current rendition of that field.

Yes, I’ve heard on the pricing and the storage databases will be quite heavy spanning hundreds, maybe thousands of entries per database (but won’t be in regular use). The next within each cell can easily span multiple paragraphs. If Bubble decides to finally implement this approach on restricting data in databases I’ll need to move the storage database elsewhere and connect via an API instead.