Database Architecture Advice

[Billionairebum]

Hello

I have been thrown a task which I am not sure how to handle at all and I would really appreciate some advice on how to tackle it.
I need to come up with a semi decent logical SQL dB design for a multi-tenant app with 3 levels and varying roles, possible cross-schema queries etc.
If you have long hair, walk on water, turn water into wine, and have some decent SQL knowledge that could assist me, please pm me.

Thank you

 

[Signor65]

As a starting point, maybe look at ABP.IO.

You don't necessarily have to make use of their software, but download a copy of their base single-layer application (it's free), set up your connection strings to point to your SQL server instance, and then generate a local database. Take a look at the structure that gets generated - it has built in multi-tenancy inside a single database, roles, permissions, organizational units, etc. It's a really nice starting point for how to structure things.

When it comes to the cross-schema queries this should not be too much of an issue, so long as the schemas live on the same db. Multiple db?? Good luck have fun.

Just checking, is this a db for a .NET project where Entity Framework can be used? Or are you going to be querying the database directly via SQL?

 

[Billionairebum]

This is brilliant thank you. My problem or "Trick" is that my users and organizations need to be able to interact with each other using permissions as needed. Certain organizations need to interact with each other and share projects,invoices etc with Admin/Owner/Manager/User levels. My thinking is to separate this out using schemas rather than separate dB as that would be hell. This is for a project using Next.js and SQL+Prisma.

 

[SpaceWalker]

dbdiagram.io - Database Relationship Diagrams Design Tool

Quick and simple free tool to help you draw your database relationship diagrams and flow quickly using simple DSL language.

dbdiagram.io

Check that too. Similar to @Signor65 response.

The main thing here is that using these tools, it allows you room for trial and error. So, the specifics and end result will be in your hands, unless otherwise required.

 

[spaceman]

Hey there
What does 3 levels mean?

Tenancy is solved by a tenants table with a name and description.

Authentication is who people are.
A users table and then password stuff etc or an external Id if they're using 3rd party logins etc

If a user can only belong to 1 tenant add the tenant id to the user.

If a user can belong to multiple tenants add a many to many usertenants table with userid and tenant id.

If you're not sure then make it many to many

Authorisation is what people can do.

Simple version is a role table with name and description

Then a many to many table called user roles with userid and roleid. Often in tenancy you would also want to add a tenantid to the user roles table. This means a user can perform some action but only to records belonging to a particular tenant.

The hard part is coming up with a sensible naming structure for roles that will grow in a way that makes sense.

One I use is like "invoices/*" or "invoices/read" or "invoices/edit" or whatever makes sense. Admin people get *, other people don't.

Z, "invoices/read", 123
Z,"invoixes/*", 234

Here user z can only read invoices for records of tenant 123, but can admin all invoices for tenant 234.

The hard.part isn't the data
The hard.part is applying the rules in your api.