16

Think Design: I have many applications that share the same user database! Other tables are shared as well such as user activity logs, purchases etc

1) Anyways my question is if I was to make all applications just use 1 database for everything! Would I have any problems with scalability? Or any other problem doing this? Is it better to have 1 database ? ? Or worst?

2) Or should I just let every application have their own database, then use web service to share the common tables between the applications?

001
  • 62,807
  • 94
  • 230
  • 350
  • 1
    possible duplicate of [Single or multiple databases ](http://stackoverflow.com/questions/1676552/single-or-multiple-databases) – OMG Ponies Aug 13 '10 at 17:38

8 Answers8

41

"Or should I just let every application have their own database,"

In the 1950's, every application had its own private set of files. After a decade or so, some smart people started to observe that certain data elements within those "appliation-private files" was actually duplicate information. Customer names were all over the place, point-of-sales info was duplicated all over the place, etc. etc.

Database technology was invented by yet smarter people to solve that problem.

And now these days, by making databases "application-private", the generation of internet programmers are resurrecting the very same problems that were already solved in the 1960's.

Just a thought of mine, nothing really important.

"Those who forget history, are doomed to repeat it". (And that is NOT a thought of mine)

Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
21

The more processes you have accessing a shared resource, the more likely you are to get into scaling/performance and timing issues.

Even if your applications are small, I would recommend against this.

Probably the worst part of this whole venture would be the unnecessary complexity you will be adding to your application set. After all, programming is not linear, and simply adding a single table to interact with increases your overall complexity by more than a factor of one.

At the very least, create a service for interacting with commonly used tables and have your applications make requests through the service.

I empathize with your desire to merge resources into a common location, but I think in this case you will be setting yourself up for more hardships down the line. I just don't think it's worth it.

In response to your edits... I'd go with option (2).

Robert Greiner
  • 29,049
  • 9
  • 65
  • 85
  • The benefit of creating shared services for interacting with commonly used tables is that you can enforce any standardised business logic requirements (e.g. Payroll being consistent with Accounts, etc.) Otherwise the applications are effectively communicating with one another via the DB without any enforced service logic. – LotiLotiLoti Jan 19 '19 at 10:30
6

Do not create separate databases. Then you will have a nightmare to maintain and things will get out of synch. People will have different names and ids in different systems and it will be the worst nightmare you have ever seen. Dups in one system will match to one person in others creating reporting issues. Just writing reports when trying to get disparate systems to match up will be nasty. Plan for scalability instead. Learn how to partition data.

If you have multiple applications hitting the same database make sure that data integrity is maintained by the database not any of the applications! This is critical as otherwise some applications may not know to maintain certain things others need and you will have a data integrity disaster to clean up. Use real PK and Fk constraints, define default values, use the correct datatypes so that invalid dates can't be entered etc.

Do not allow developers to change the database structure without approval from database specialists who know what other applications may be affected by the change. Make sure to learn how to write performant code the first time as you will be affecting other applications with your code.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • 2
    This is why you have microservices. If data needs to be shared, it is not the best approach to have multiple apps talk to a database. That creates a lot of coupling. It is better to have a microservices that exposes an api to that db. That way all those apps can use the service instead of the DB. – vpassapera Jun 07 '18 at 21:58
  • 2
    @vpassapera, no they can't all do so. Sometimes you need to not go through a service like when you import 16,000,000 records through SSIS. To run that through as service would be stupid in the extreme. Sometimes you need to write a script to fix data such as when all the data from client A needs to be changed over to client B because client B bought client A. It is stupid to assume that no one will ever hit the database directly or outside your service or microservice. – HLGEM Jun 08 '18 at 19:45
  • 1
    Migrating the database is not the same as letting a bunch of different applications actively use a live database (e.g. write and read). You're playing a game of "no true Scotsman" here (e.g. moving the goal post). It is in fact, stupid to let other applications have direct access to your database. There's plenty of examples and articles on the web showing how wrong you are. I suggest you do some research. – vpassapera Jun 13 '18 at 20:32
  • 1
    Also, let me specify for the above, reporting and ETL is not the same thing. No BAs should be creating new records, and if they need denormalized information, there's plenty of routes to go, such as views. From a developer standpoint, you're simply incorrect. – vpassapera Jun 13 '18 at 20:33
4

Even if you had one database you could still separate user objects with the use of SCHEMAS, then you can also give users access to just their schema and not anyone else's schema. Read about Schema here: http://msdn.microsoft.com/en-us/library/ms190387.aspx

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
2

Applications tend to copy the organizations that create them. If a database has two customers, you could find yourself being asked to make a change that could break features that the other customer uses. So if the application has one customer but several distinct schemas, you might want to put them in one database. If the application has many customers, you might want to separate out them to different databases even if the schema is much the same--if you need to support the possibility of evolving the application for one user but not another.

Blogger (the google app) is a good example. None of the customers has clout to ask for a feature just for them, so blogger most likely puts everything into one schema/one database.

MatthewMartin
  • 32,326
  • 33
  • 105
  • 164
  • Blogger is just 1 big database? Btw SO is just 1 big database isn't it? – 001 Aug 13 '10 at 18:06
  • Like I initially said, "most likely". The SO engine and schema are in at least 3 DB's, one for SO, one for the old stack exchanges, one for the new stack exchanges, which is logical because each has a different set of customers and different forces making them evolve. – MatthewMartin Aug 13 '10 at 20:08
1

Some things will be generic across your business. These things include Users, User Activity/Audit logs, etc. These can be easilly maintained in one database without much effort.

Some things, even though they share a common structure need to be separated for some business reason. Since there is a declaired reason that this is a business need, it doesn't matter whether its easier to do in one database or not- do it in multiple databases.

Some things, though, fall in a grey area. Every unit has to deal with a 'Customer'. Should this be in a shared database? There may be others as well. It seems like these things share so much commonality, you really want to store them in one DB.

From personal experience: don't. Separate business units consider each of these things differently, and accommodating all of those differences can make your tables a maintainence nightmare. If you want a Data Warehouse where you can store all your business data, that may be a good idea, but actual day-to-day operations data should probably be stored in separate databases to make maintainence and use as uncomplicated as possible.

AllenG
  • 8,112
  • 29
  • 40
1

The way you asked the question best for you is continue "database first" approach. Use single database and mitigate issues created by the fact that there are many apps using same database:

  • Strict db schema and database constraints
  • Separate stand-alone database management, database release process
  • Consult, inform all apps/developers about schema changes, block them from changing schema
  • Encourage developers to use db transactions because their app cannot control operation integrity
  • Some may suggest moving code to database (e.g. Stored Procedures) but that's debatable
  • If things got complicated then most relational databases has schemas that provides further data separation at database level

Also the way you asked the question: You feel that something is off with this design and it's true. But it depends on the short term/long term requirements/expectations for this system and it's not provided in this question.

I use this general "rule of thumb" to make a decision: what first thing we are thinking (me or a team) when faced with a business issue we need to solve:

  • If it's tables, fields and relations - use single database and preferably single app. Grow from there later when time is right and if still needed.
  • If it's business operations, business workflows, business services - then go down that path and attach dedicated databases where needed.

No database engine or programming language can solve poorly designed system. If you do that part right the tools you choose are second priority. Do what you and your team are best at.

I know this post is old but decided to add my two cents here.

P.S. Look at DDD concept. It has a deep dive into this matter.

Andrius
  • 11
  • 2
0

How about writing an application wrapper around the user database let all the other application use that service to get user information instead of a database?

Adeel
  • 761
  • 7
  • 24