I have an application which stores a lot of entities in a classical relational database (Microsoft SQL Server) and I use an ORM (Entity Framework) to query data from it. This database has one schema any only a "global view" to this data exists. The data is made available to users via an OData API.
For simplicity let's say it is a blog system with the tables
Users. The exact relation between them is not so important for now.
With the next version of the application we would like to operate multiple blogs within one software installation. All blogs with their contents are independent from each other and operated by different users.
How can such a extension to the software be done?
The most obvious solution to the problem seems to me to make the separation part of the data model. This would require to introduce a container entity and let all other entities point via FK to the container they belong to. Then in the application all queries need to be extended by a filter to only load data for the current container.
As you can imagine this requires a lot of development effort. Every query must properly specify this filter and if not, data from wrong blogs might be shown. Also unique constraints need to be reworked to be unique-per-container.
Another idea was to create even 1 database per container and connect to the corresponding DB depending on which container is accessed. The maintenance effort behind this also seems a bit complex to me.
Are there maybe other technical solutions to this question?
Unfortunately I am lacking of the correct terms to do a proper research on this topic. The best thing I could hope for is some SQL Server feature that allows storing multiple "sets" of data in the same database schema with strict separation.