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 applicationssaves space as only one database is neededcomplicates indexing as different applications have different querying needsimport data daily into per-app databasesuses more space as duplicated data exists in per-app databaseseasier indexing as each app can focus on its individual needsI may have left out other advantages/disadvantages, please list if any, also how is this done at your workplace?
Should I use one database per application or share a single database amongst multiple applications
database;database design;multitenancy
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 isthat you hit performance bottlenecks and that you can't easily scale theload 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 databasestructures which aren't suited for the task at hand but have to beused 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.