37

I have multiple applications some that use data from the same sources. Is it best practice (or what are the pros/cons) to:

  • leave the data in databases shared by multiple applications

    1. saves space as only one database is needed
    2. complicates indexing as different applications have different querying needs

  • import data daily into per-app databases

    1. uses more space as duplicated data exists in per-app databases
    2. easier indexing as each app can focus on its individual needs

I may have left out other advantages/disadvantages, please list if any, also how is this done at your workplace?

Improve this question
3
  • 1
    How are you defining application: separate builds, different developers?
    – JeffO
    Sep 5, 2011 at 12:27
  • Sharing the database turns the whole database into a big and messy public API. And it's missing all abstractions that you might have build in the first application.
    – Patrick
    Dec 22, 2012 at 9:18
  • Is performance an issue? With enough records that would dissuade one from a large single database. Space is cheap.
    – Rig
    Dec 4, 2013 at 17:05

6 Answers 6

Reset to default
46

Space is cheap these days, so I'd advise to use one database per application.

Sharing one database for amongst multiple applications has some serious disadvantages:

  • The more applications use the same database, the more likely it is that you hit performance bottlenecks and that you can't easily scale the load as desired. SQL Databases don't really scale. You can buy bigger machines but they do not scale well in clusters!

  • Maintenance and development costs can increase: Development is harder if an application needs to use database structures which aren't suited for the task at hand but have to be used as they are already present. It's also likely that adjustments of one application will have side effects on other applications ("why is there such an unecessary trigger??!"/"We don't need that data anymore!"). It's already hard with one database for a single application, when the developers don't/can't know all the use-cases.

  • Administration becomes harder: Which object belongs to which application? Chaos rising. Where do I have to look for my data? Which user is allowed to interact with which objects? What can I grant whom?

  • Upgrading: You'll need a version that is the lowest common denominator for all applications using it. That means that certain applications won't be able to use powerful features. You'll have to stick with older versions. It also increases development costs a bit.

  • Concurrency: Can you really be sure that there're no chronological dependencies between processes? What if one application modifies data that is outdated or should've been altered by another application first? What about different applications working on the same tables concurrently?

Compared to that, data imports/ETL-processes are almost always pretty straightforward and simple. Load the data as often as you need to, space is cheap. You can account for scalability for each application independently, adjust and tweak the structures as you need them and there won't be concurrency issues. Side effects can be traced much easier, too.

Edit: I'd like to point out, though, that as @Saeed mentioned, if you can encapsulate data manipulations in a service which is commonly available, then it's easier to share one database with multiple applications. As long as you don't need raw access that is a very good approach.

Improve this answer
7
  • When using a service for the shared db as per @Saeed's answer, don't I still have the concern with multiple apps haven't different needs and requiring a wide variety of indexes on the database?
    – Laz
    Sep 4, 2011 at 19:10
  • 2
    @Laz: Probably, depends on the use-cases and requirements.
    – Falcon
    Sep 4, 2011 at 21:46
  • 2
    One of the fundamentals of relational database design is not having duplicata data. Having different databases which contain overlap of the same data is just asking for trouble.
    – Pieter B
    Dec 5, 2013 at 12:56
  • 1
    Pieter B: I assume you have never worked in enterprise environments, like for large corporations. Having one database with many different applications and different development teams is just asking for trouble and can eventually bring development to a hold. That said, there's never a black and white choice imho.
    – Falcon
    Dec 5, 2013 at 15:47
  • @PieterB: multiple applications reading and writing the same data is a good indication that you should factor out a service layer against which all applications can make requests. On the other hand, if they are different enough that you cannot factor out a common service, then they are different enough to require separate tables/databases.
    – Dan Lyons
    Dec 5, 2013 at 19:24
25

I had a similar situation once. My problem was to build 3 applications, one for inventory management, one for procurement management, and one for managing users, i.e. employees. My recommendation is not to break databases physically per application, or join them physically per application. Rather, IMHO logical separation works better.

For example, all 3 applications needed to work with employee information. Both inventory and procurement systems were using the same information of goods and inventory items.

I created a shared database, in which I stored the information of users and goods. Then I built a service layer on top of it and I used those services in other applications. To show a list of all employees who are now in the company for example, I only needed to call a method from the service like GetOnWorkEmployees().

I also created a common UI for interacting with users and goods which was a separate web application in its own.

So, adding to what @Falcon has pointed to, I think that you can benefit from centralizing the common functionality among applications in one database.

Improve this answer
2
  • 5
    +1 for logical separation and suggesting a clean architecture. SOA makes such things really easier
    – Falcon
    Sep 4, 2011 at 12:28
  • Definitely +1 for logical organization. Let the data be grouped to optimize the balance of coupling and cohesion and then applications can dip into whatever databases they need to do their work.
    – Joel Brown
    Sep 4, 2011 at 19:04
10

If these applications are meant to work from the same data - for example, the same list of products and customers - then keep the database together. You dont gain anything by seperating the databases. Thats purely a 'human' issue - to the server its just bytes on a disk. It doesnt care if its 1 or 100 databases. But if you do split it, you then have to deal with data synchronization. The indexing issues you bring up arent a real issue - you'd spend the same amount of processor time maintaining the indexes if the db's were split.

If performance starts becoming an issue, replicated the db to multiple servers to balance things out.

Improve this answer
3

It may or may not be worth the trade off in your situation, but maintaining data integrity is easier with a single database. In MS SQL Server at least, you cannot foreign key from one database into a different database. You can simulate foreign key behavior with triggers, but it's not particularly elegant.

In addition, creating local copies of the data can be dangerous when writes come into play. If AppA and AppB both have a copy of some shared data and AppA updates it, AppB will still have the old data. Or, you will have to setup triggers to keep the data in sync.

Improve this answer
1
  • +1: It's easy enough to map multiple applications to a single database.
    – kevin cline
    Sep 4, 2011 at 18:10
1

Once I had multiple websites which got popular over time. They used separate databases, mostly because the hosting provider allowed only 1Gb storage space each. But! As soon as I released a service that included all these websites, I started having to do transactions amongst these websites, and the most convenient way to do this is definately moving everything into one big database.

So I optimized the database structure and squeezed the relevant parts to this central database, but left everything else out.

My opinion is somehow related to the paradigms of OOP. Similar data must be stored together, so if you build different applications, you should use different databases for them.

In the above case using common db couldn't be avoided, but remember that I also kept some tables apart in a separate db, that aren't part of the common queries.

Moreover, if you keep them separated, they will be easier to backup, there will be less chance for data loss. If something goes wrong and applications interfere with each other, the database gets messed up, and you basically don't want to expose your app to this danger.

All in all, you can maintain a common database for common queries, but also keep one for each of your applications.

Improve this answer
1

Can you elaborate more about your application architecture?

If your database functions just as a data repository without application logic such as triggers or business package code, it would be better to have a single database and encapsulate business level functionality to services that use the database for all their actions. If you have triggers or code in database schema you will get into great trouble by using a single database which can be troublesome in many cases.

Improve this answer

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