Using a shared monolithic TG app, I’m trying to decide whether a full separate schema vs scoping based on tenant_id is worth it. The app is composed of parent organisations which handles mgmt, book-keeping, tax fudgery, stocks.. the usual ERP shenanigans. And child organizations offering a e-commerce like marketplace, which is basically vendors reselling from the parents shared pool & optionally allowing buyers to sell to each other. Each child has its own brand & theme. & each child can also be parent for another. Pyramid style.

Can you give examples of each?

Everything always depends on vision, users and resources.

Hi sasin91, your app sounds very interesting and it's great to see others creating complex apps of this nature with Trongate. I too have created such a beast, that caters more to facility management and analytics for forecasting life cycle costing for several clients; mostly for local government and not-for-profit organisations. I went for a full separate schema but wished I'd gone with a more hybrid approach. Both have their advantages and trade-offs.

Using full separate schemas for each tenant can provide strong isolation between tenants' data, making it easier to manage security and prevent accidental data leaks and this is what I modelled my app on.

On the other hand, scoping based on tenant IDs can offer a more efficient and scalable solution, as it doesn't require creating separate schemas for each tenant. It allows you to manage all tenants' data within a single schema, using a tenant ID to distinguish between their data. This approach is often more suitable when tenants share some common functionalities and data structures - this is where I wished I had gone for a more hybrid solution in my app.

In your case, where you have a parent organisation handling management, bookkeeping, and various ERP activities, along with child organisations running a marketplace, you might consider a hybrid approach. You could use a shared schema for parent organisations to maintain common data, and then use scoped tables or rows based on tenant IDs for child organisations. This would allow you to balance the benefits of isolation for parent organisations while efficiently managing data for child organisations.

Additionally, since child organisations can also act as parents for others in a pyramid style, flexibility and extensibility will be key. Ensure that your chosen approach allows for the easy addition of new layers without overly complicating the structure.

Remember to also consider factors like performance, maintenance overhead, and potential migration complexities when making your decision.

Cheers,
Si

Simon,
Thanks for the great explanation.
I understand better what you are saying.
Dan