Multiple application using one database?

Question

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

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

Answer 2

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 3

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.

Answer 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