Privacy rules and Data redundancy - which one is more important?

Need help Database
1

Hi all,
I am trying to setup privacy rules for my app and facing with couple of issues as every other bubble user. My database structured based on user roles and database tables are normalized to avoid redundancy meaning that I try to avoid writing same data to more than one row in the database.

For example:
There is two user roles: Company and Influencer
There are two table (thing): CompanyProfile and InfluencerProfile

So at the beginning, what I did was to have CompanyProfile (type: CompanyProfile) and InfluencerProfile (type: InfluencerProfile) fields in the Users table.
This allows me to create expressions like user’s companyprofile’s name, address etc.
This also allows me to create data privacy rules for Company.

However, after reading about database designs and understand how important to avoid repeating same data (less CRUD, more consistency in the database, better performance) I restructured all of my tables. Now I dont have a “link” field to the thing, e.g. User tables doesnt have CompanyProfile or InfluencerProfile.
I can still create expressions by using “do a search for…” and constraints and get users’ company’s name, address.etc.
but I cant create and data privacy rules when it comes to more complex (thing’s thing’s thing) data.

At this point, I either will create bunch of new fields in almost every database table to link to the user, or have database privacy rules that is not really secures the data.

I want to ask which route is better in terms of performance? Can data be secured in the actual page itself? For example; can I just create a rule that make data inaccessible in the page if the current user has no permission?

2

Data security is exclusively done at the database level in Bubble. Adding security on the UI doesn’t actually work - while it may look like it works it’s actually easy for a programmer to get around anything you set up on the UI. So, use database security for any/all data you want to keep secure.

Having flat tables often works better for performance in Bubble since you’re much less likely to need nested searches. That said, it usually means some of your data is duplicated in multiple tables, including having “links” that point both ways between tables. Duplicating data is considered bad practice for databases, but seems to generally be the lesser of two evils with database design in Bubble.

7 Likes
3

Thank you @sridharan.s
I think you are right. Also, I assume privacy rules affect the amount of data being sent to the page, since database dont send anything that the current user shouldn’t be seen. Hopefully this will help to the performance.

4

That’s correct. And, it does seem to help with performance.

5

Having flat tables often works better for performance in Bubble since you’re much less likely to need nested searches. That said, it usually means some of your data is duplicated in multiple tables, including having “links” that point both ways between tables. Duplicating data is considered bad practice for databases, but seems to generally be the lesser of two evils with database design in Bubble.

I have just finished normalising my database, and I’m still trying to wrap my head around database design and Bubble in general.

Can someone please explain to me what the term “flat table” means in this context?

Is the conclusion here (and in general when it comes to Bubble privacy rules) that we should be adding User fields in almost every database table so that Privacy Rules can be implemented?

The explanation by @sridharan.s seem succinct, but I need a dumbed down version to help me understand fully.