Has anyone worked in an environment where multiple in-house applications are all under one database with each application given its own schema within that single database? I don't just mean a few small, related applications in one DB, but like full on standalone applications being put into one DB with other standalone applications.

I'm dealing with an architecture like this that was put into place long before me and am trying to argue that a new standalone application should be put into its own separate DB, but I'm basically being told that this "put everything into one database" architecture makes more sense than a separate DB and I am kind of at a loss for what to even say. I feel like I can't find much info about this out on the internet because no one else is putting multiple applications into one database like this, but maybe I am completely wrong?

I am working with Microsoft SQL Server and .NET web applications.

  • 1
    Can you articulate the reasons why you think each application should have a separate db? – Robert Harvey Sep 13 at 14:35
  • @RobertHarvey - It seems logical (and more "clean") to separate unique domains into unique databases. I also share the thoughts as outlined in this answer: softwareengineering.stackexchange.com/a/105788/51121 – Kevin Sep 13 at 15:26
  • 1
    The author of that answer sounds like a microservices advocate. The kind of scalability problems he's talking about won't happen in 95 percent of installations. I can rebut every one of his points. – Robert Harvey Sep 13 at 15:30
  • 2
    If there is a natural demarcation between databases, it's not going to be between applications, it's going to be between companies or departments. Even then, sometimes it can be advantageous to put everything in the same database; cross-database joins are an enormous pain in the ass. – Robert Harvey Sep 13 at 15:33
  • @RobertHarvey - I agree, there's not necessarily a natural demarcation between applications. In this case this is an application for very specific data for a specific department. In fact, we are thinking with this "application" that it can potentially be broken up into even smaller apps/pieces which of course would not require their own separate DBs. No other system uses the data in this application, but if it another system in the future needed to the other system would utilize data from a data warehouse or would go through this application to pull/modify data. – Kevin Sep 13 at 15:43

Yes I have worked in a similar environment.

The difference between a schema and a database isn't a huge one from an application perspective. After all, all the databases on a server run on the same server. I wouldn't sweat it.

The key thing is you maintain a separation between the tables and not have one set reference another, or have queries that span domains.

I would leave the DB setup to the DBAs and just ensure my SQL was good.

  • The same with me. Also consider the license costs, and the small extra overhead to manage more than one database and its licensing over time. – Joop Eggen Sep 13 at 13:12
  • tbh I think I would choose the database separation over schema if it was me. I don't think you pay licence fee per DB? and things like backups will be easier. But as a developer I try not to care – Ewan Sep 13 at 13:17
  • I know there would be no extra licensing cost, but the maintenance/overhead is something that was brought up as an issue. – Kevin Sep 13 at 13:55
  • I guess the maintenance comes down to the maintainer. not sure I would like to get the request to restore a single schema from backup though – Ewan Sep 13 at 14:36

In short

I have worked in both kind of environments. I was an unconditional promoter of the shared-db approach. It took my some time to see its limitations and to reconsider: Envisage it only for tightly related applications working in the same bounded context.

In general the shared-db work very well. Like COBOL programs before them. And like them, they still makes us see data as something passive that waits to be processed by a software that knows how to deal with it (and that will do it right). Single-dbs have the advantage of letting application share enterprise data. Exactly as global data is shared by different modules.

But modern software engineering has learnt us to encapsulate modules, avoid global data, and go the OO way: data makes sense only together with the code that can handle it reliably. Why do we continue to ignore these principles at the database level ?

In long

The graal of the single corporate database

Single-db work very well, because in a company, all activities are somehow linked. That's the principle behind many leading ERPs. Having everything in one DB allows you to integrate in real time different applications very easily.

Benefits:

  • Reduced operating cost
  • Opportunities to reengineer processes: all the data is there; so you are free to redistribute functionality between applications.
  • Opportunity to rationalise the db design: End of the 80's beginning of the 90's I witnessed a couple of centralisation projects, with impressive reduction of the number of application software (in particular ETL/interface programs)
  • Synergies: different teams share the same skills and can share ideas by communicating of their DB scheme.

Risks and disadvantages:

  • The success will depend on the ability design the DB scheme from a global perspective, across the domains. If everybody continues to build a sandcastle in his/her own namespace, you'll have the inconvenience of the global DB without most of its advantages.
  • Strong coupling of the components: you can no longer reuse an application in a different context (another site, another company), because data is intertwined with a lot of other apps.
  • Unknown dependencies: Since everything is linked in one DB, and its easy to access a table in another scheme/namespace, the dependencies are not clear. This has of course an impact on the organisation of developments: are you sure that some db tables you tested with were not changed by some developer of another app ? How to organise the db evolution ?
  • One bad behaved programme can create inconsistencies across the db
  • Scalability is ultimately limited by the scalability of your DB engine.
  • Slow down of innovation, in part because of fear ? Due to the unknown dependencies, most of the time SW-engineers will prefer not to touch a working production db.
  • Vendor lock in ?

But aren't we in a different world now ?

Nowadays the trend is to avoid the monolith and to conceive flexible evolutive and scalable architectures:

  • In our internet-aware world, application can communicate through webservices and no longer require a common db to exchange in real time.
  • Microservice architecture even prone to avoid a shared DB: each (micro)-service is expected to have its own private DB, so to increase decoupling and accelerate deployment of new developments.
  • Cloud operations let reconsider the operating costs under a totally different angle. You no longer care about how many dbs servers you need to patch, upgrade or backup: it's included in the price.
  • NoSQL made it's way to the mainstream: many applications can work perfectly with an RDBMS. But there are specialized problems are better handled with different technology: a graph DB, a document oriented DB, or need for geolocalized sorting may favor specialized DB engine. One-DB policy would be a blocker.
  • Some application don't need a DB. For example event streams process in real time large amounts of data between applications, without requiring the data to be shared on a db (the db equivalent would require inefficient repetitive queries to detect new data).

Conclusion

The key for a good architecture is no longer to define the right tables in a single db, but to define the right APIs, letting services make the glue between the different apps.

This being said, let's not be dogmatic. For tightly related applications working in the same bounded context, a shared DB is still a valid alternative.

I've also been in situations where different apps were in the same database. I understand your frustration, but sometimes "best practice" is subject to "good business."

Like you pointed out, if the apps were related, it would obviously make more sense to have them on the same DB. If there is a business reason (like the other answer and comments pointed out), then you could accept that reason or try to make a better business reason for having them separated. Two I can think of would be possible security concerns and scalability. Now-a-days, many apps are being virutalized and containerized. It is trivial to add a DB in a docker container served along side dockerized (or whatever container app you use) apps. Of course this line of though assumes that the expense of licensing or staffing does not outweigh the benefits of being able to add another container as needed.

So, to summarize, their architecture is not unheard of, it is done. If you would like to suggest a different direction, understand why they made their decision (technical or business) and try to make an argument that affects that area.

protected by gnat Sep 14 at 13:15

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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