One Database for Multiple Applications?

Question

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.

Answer 1

In short

I have worked in both kinds of environments. I was an unconditional promoter of the shared-db approach. It took me 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 works very well. Like COBOL programs before them. And like them, they still make 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 applications share enterprise data. Exactly as global data is shared by different modules.

But modern software engineering has taught 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 works 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 in 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, applications 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 a RDBMS. But there are specialized problems that 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 applications 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 be 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.

Answer 2

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.

Answer 3

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.

Answer 4

I been working in such organization since past 11 years. It works marvelously.

There are like 25 applications at front-end, all web applications, they all share the same database.

When To Have A Different Database

You need a different database when you have different overall business rules. For example if company is different. Each company has its own processes. They don't differ much usually between departments of the company. A different company is a different beast totally, run even same type of business differently.

Different SBU In Same Company

Over the years we did start using a different database but only when we have to put a special case in our business logic at enough places. It was for a different strategic unit. Think SBUs - Strategic Business Units.

The way to distinguish is when the unit has its own budget, do its own hiring and may even have its own goals which may even be in conflict with other SBUs of the same company. An example is a newspaper company in Pakistan called Jang Group of Newspapers. It from its start in 1940s publish 2 different newspapers: Dawn and Jang. First one is in English, second in urdu. The 2 SBUs have different target audience and therefore do business differently.

One unit may well be in profit and other in loss. Also, for an employee moving from one SBU to another is 90% as difficult as moving to another company altogether. These are 2 more points to distinguish between SBUs in same company, and therefore to know when a different database is needed.

Different Company

Consider a software house. It dont make support softwares. It make one software for one client, sale it to it, then move on to making another different software for another client. Repeat clients may or may not come and they dont depend on repeat business. They move from transaction to transaction. Obviously the software house will use a separate database for each client.

Different Law -> Different Business Process -> Different Business Logic

Consider an american company that also do business in france. Laws are different so business processes also have to be different. If they are different enough then they may decide to use a different database for each country.

Special Case In A Function -> Separate Function -> Separate Container of Functions (Object, Component, Header File etc) -> Separate Front-End -> Separate Database

As in all things in software development it comes downs to how many things a person can keep in mind at a time. The smallest unit of business logic is in a function. Now consider a function that to program it a programmer has to keep 4 things in mind. If only one of them differ between the two countries then an average programmer can handle it, he now keep 5 things in mind. If instead of one there are three things that differ between the 2 countries then the programmer now have to keep 7 things in mind. Very few programmers can do that. So he will just make a separate function. As those separate functions and therefore their containers (objects, components, header files etc) pile up at some point it all get so complicated that a different front-end (website, say) makes sense. The next logical step is a different database altogether.