3

Background

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 Posts, Comments, Tags, Users. The exact relation between them is not so important for now.

Question

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.

3

You nailed the two obvious choices. But neither is quite as hard as you say, and which makes more sense depends on how much you expect to do in integrating content of one blog in another.

Separate Database approach:

This is the simplest to implement approach. You already have a connection string for connecting to your database. Just wrap it behind a function, that takes some sort of 'Request' object as an argument. It can then just return the right database connection string. Easy, and very modular. Essentially nothing changes in your application.

The only downside of this approach is that if you ever want to analyze across blogs (e.g. which Blog is the most active, which blog uses the word 'fart' most often, etc), that won't work well with this approach.

Within one database approach:

This requires an extra 'blogid' field in all your top-level tables (maybe all tables). Anywhere you grab data without first joining on another table (if you are already joining on a table with the blogid, you probably dont need to add the blogid to the new table - like perhaps the users table? This depends on the semantics you want too).

And yes, this does require a tiny change to your top level queries - saying where blogID=blogIDImWorkingOn. But it makes easier - stuff later - when you want to integrate/analyze content across blogs.

Hope this helps! Good luck!

  • 1
    Putting a "blog Id" on multiple tables that aren't directly linked to a blog will still be a maintenance headache. Normalize this first. You can always create database views to flatten out the tables to make querying easier. Views and proper indexes will take you a long way, and then denormalize the database only to solve specific performance issues. – Greg Burghardt Aug 6 '18 at 20:32
  • No disagreement, but he didn't ask how to re-organize the entire database, just to solve a specific problem. But if his time budget permits, that makes sense. Though - note - it probably has impact on his entire code base because of the ORM layer (unless he writes custom mappers). @GregBurghardt – Lewis Pringle Aug 6 '18 at 21:31
  • Accepted this answer as it describes both common approaches. It seems there are no out-of-the-box solutions and you either need to go for schema changes with IDs or you go for a separate database per tenant. – Danielku15 Aug 20 '18 at 16:23
5

While I'm not really an experienced architect, but as the question stands, you might not want to change much at all.

You just add a Blogs table, and a "blog_id" column to posts (just as your "comment" probably already has a "post_id" column). Well, depending on which entities you want to separate, you might make other changes, for example, you say you want to separate the users, but since you don't provide much details, it's fair to assume you don't need much complexity. Maybe users shared between blogs would be a feature.

Depending on the size of the project, it might not be too efficient, but again, if you don't forsee any problems, no reason to overcomplicate the system

  • +1 This should be the way to go. I work in a project where - in the terms of the example in the question - every single table has a "blog_id" column and I cannot recommend it at all. While it might look like a convenient approach at first, it is actually less work to just filter any table by its parent key, which must usually be done anyway. Other than that, I recommend the Boyce Codd Normal Form (BCNF; en.wikipedia.org/wiki/Database_normalization#Normal_forms) – Tim Meyer Aug 7 '18 at 15:26
  • This approach can have drawbacks. It leaves room for mistakenly mixing your client's data. Depending on the size of your company and the number of developpers, this can be an unacceptable possibility. If each blog is big, splitting per bd can also facilitate maintenance such as backup management. Splitting per db also allows you to split your db server easily if you are lucky enough to scale. – Simon Bergot Jul 30 at 10:47
1

We have solved this issue by using one database per customer and have a single configuration database that maps which subdomain/customer connects to which database. Simply by storing the connectionstring, customer subdomain, and other metainformation in this configuration database. Similar to what you propose as your second solution.

It does require some setup per customer, but I dont think this is avoidable no matter how you implement it. We use dependency injection and connect to the correct database during container initialization. This is done on a per request basis.

This is not really a good answer, but it works for us, so it probably will for you. I have no clue if there are better ways to do it tho. 😅

  • I also did some further reading as Killian nicely added the correct tag "multitenancy". It really seems to be the fastest and easiest solution to have separate databases. I might need to still clarify with our product management if we really can have a strict separation of all tenants. – Danielku15 Aug 6 '18 at 12:09
  • @Danielku15: I really really really would not start with this solution. Misamoto has the right solution. If you think the coding effort is difficult just to add a "blogs" table, the database maintenance effort will be monumental if you must maintain a bunch of similar looking schemas. – Greg Burghardt Aug 6 '18 at 20:30
  • I agree with greg-burghardt if the blog example really is the schema being used. But my understanding is that the blog schema is just a small example, and that @Danielku15 really is working with a much larger schema. – abydal Aug 6 '18 at 21:02
  • The blog example is really a small schema to illustrate the topic. Actually it is a way more complex ERP like system with a partial entity-attribute-value data model. From a DB query perspective it might be achievable to add the neccessary filters everywhere, but also domain level validations and unique constraints need to be extended. The multi-database system doesn't sound too bad for me as we already have a centralized DB update sequence on application startup. – Danielku15 Aug 7 '18 at 9:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.