How to best design a Database

Hi,

it’s been several months i’m working with Bubble. I have a heavy background (25+ years) in programming and DB design.

i’ve struggled many times against unconscious habits in SQL DB designing … i’ve found, tested, used many ways to design DB in Bubble, but i still can’t figure out what’s the best design for a DB with the equivalent of nested tables. By nested tables i mean the following :
Workshop -> Groups -> Users->IndividualTypedDatas
Each left-to-right table link is a (0,n)-(1,1) link.
Let’s say each user has a field containing its job.

i’ve tried to create a stat displaying per workshop each job and the number of users with this job.

AFAICS, it’s not possible to do this stat if i keep this DB design in Bubble.

So my question is : Correct me if i’m wrong, but with Bubble, do i have to put a reference of the workshop to all subtables, a reference of the group in each Groups subtables, and so-on PLUS each table containg a list of each of its subtables (workshop containing a list of Groups, Users, IndividualTypedDatas, …) ?

Thanks for your answers :slight_smile:

Best regards,

1 Like

For the sake of ease, adding the left references to the right will make it easier for doing searches to calculate your stat. You could do a search for the user and then filter on the workshop or group as needed.

The other option could be creating a list of Groups and List of Users field on the workshop and adding to those as you create the things in the database. Then when calculating the stat, you could filter on the list with the constraints you want. This works, but you will eventually run into performance issues if the list gets too big.

One more option for calculating the stat might be to do a search for users, then add “filtered” from the list and use the Advanced constraint. You can then say This user’s group’s workshop is “the workshop you are calculating the stat for”.

When I build my db tables, I always add the left table reference to the right tables all the way down to where I will need to calculate items. I also add a list field to the left element for one level down just so I have multiple options available to me.

2 Likes

Agree with this.

Thanks for your answer @jdiaz

Ok. it’s a bit tricky all the nested tricks to perform “Do Search Conditions” for real “nocode people” :slight_smile:

Si that’s it : the (0,n)<->(1,1) means that i put a “parent reference” in each child (ie User will have a Workshop & Group reference) and each parent will have a Child List field for each child table immediately below (ie Workshop will have a Group List Field but not User List field, and Groups will have a User List Field).

Am i right ?

That’s how I do it, but you are not limited by that. You can have a list on the left table for every right table if you want.

But doing it the way listed above will enable you to do the calculations you were asking about.