Database best practice & capabilities

mball · 2024-09-10T10:14:02.159Z

Hi everyone

I’m a Bubble newbie trying to decide if it’s the right platform for my organisation. Generally I love it but I’m starting to get serious concerns about the database side of things and like to get people’s thoughts and advice. I’m happy to accept my perceptions are wrong!!!

the database UI sucks BIG style. Table creation for anything approaching a more complex structure is extremely clunky and the amount of clicks to create a table is crazy. Added to the the data entry for data is even worse. If I want to add a bunch of rows why can’t I add them direct to a table?
How can I perform database queries that are then used in data updates?

e.g. a have 3 tables - user, document and user_documents
I need to create one row in user_documents for each document, for each user because each user needs to read and acknowledge the document.

What’s the best way to achieve that? I’m considering using Xano rather that Bubble’s own database but want to know if that’s the best approach

Thanls
Mike

georgecollier · 2024-09-10T10:17:18.214Z

mball:

the database UI sucks BIG style

Yes, not for the reasons you state though. It sucks for viewing / editing data from the database.

mball:

. Table creation for anything approaching a more complex structure is extremely clunky and the amount of clicks to create a table is crazy

Not really. A couple of clicks for each field. Create your data types first, then create the fields.

mball:

e.g. a have 3 tables - user, document and user_documents
I need to create one row in user_documents for each document, for each user because each user needs to read and acknowledge the document.

You only need a User data type and Document data type.

Have a User field on the Document data type. Then, you’d find a user’s Documents using Do a search for Documents with a constraint such that User = Current User. You don’t need a join table.

mball · 2024-09-10T10:33:33.658Z

Thanks for taking the time to answer @georgecollier

How would I then track that each user has acknowledged it. Can’t store that data against the user or the document, it is for each user for each document

georgecollier · 2024-09-10T10:35:09.780Z

Then have a table ‘User Document’ with fields like ‘readDate’ for when they opened it, ‘signedDate’ for when they signed it etc. Same as any other SQL database

mball · 2024-09-10T13:26:59.576Z

How would you update the link table?

georgecollier · 2024-09-10T13:38:05.852Z

What do you mean? Do a search for User Documents where User = Current User and Document = {the relevant document}: first item

mball · 2024-09-10T17:29:34.857Z

When I upload a new document how would I create a record for that document for each user?

If I have 3 users I’d want to create 3 rows in the User Document table so that I can record the status as the acknowledge it

In the UI (later) I’d log in a user and want to see the documents I have to approve . That would be a query on User Document table but also retrieving data from the document table too

georgecollier · 2024-09-10T17:48:00.356Z

mball:

When I upload a new document

mball:

create 3 rows in the User Document table

There’s your workflow, no? And create three things by schedule API workflow on a list of users. Just try it.

mball · 2024-09-10T17:50:13.322Z

sorry but that’s what I’m asking. None of this is obvious from any of the documentation I’ve read or videos I’ve watched

georgecollier · 2024-09-10T18:02:12.399Z

The best way to find out is to try it and ask here if you have a specific problem

When a user uploads to a file uploader, create a new Document. Set a file field on the document to the file uploader’s value.

If you want to invite users to the document by email, have an input for their email and a button, and when the button is clicked, email a link to the user and create a new User Document

mball · 2024-09-10T19:26:25.998Z

I am trying and that’s why I’m asking questions.

The user won’t upload the document someone else will, it then gets allocated to the users by adding to the link table. I can’t see how I can add a row to the link table for each user

tylerboodman · 2024-09-10T21:09:49.709Z

Here is the basic idea where when you upload a file, it will create a User Document per user user-docs | Bubble Editor

You would want to have a page where the user only sees documents assigned to them, then that would set the User Document's acknowledgeDate date field to Current date/time

Please note once the file is uploaded it schedules a backend workflow for each User, you will find that workflow here:

image814×455 24.9 KB

Backend workflows won’t work on a free plan so this example won’t actually work 100%

mball · 2024-09-11T14:26:44.767Z

Thanks @tylerboodman

system · 2024-11-19T10:14:09.066Z

This topic was automatically closed after 70 days. New replies are no longer allowed.