Multiple application using one database?

Question

Hi

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?

Answer 1

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).

Answer 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.

Answer 3

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

Answer 4

Do not create separate databases. Then you will have a mightmare 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 aplications 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 wil be affecting other aplications with your code.

Answer 5

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.

Answer 6

"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)