CODEWITHSUNDEEP
sqldatabase
James Parsons
James Parsons

Reputation: 6057

Is it considered a poor practiced to use a single database for different uses across different applcations

What if you had one large database to server all your apps. So your website that needs to store customer orders can use the same database that your game uses to store registered users. Different applications could have tables only for them to use. Some may say that this could be a security issue, because if someone cracks your database, they could attack all your applications. But in a lot of databases you could use a line like the following to restrict access:

deny select on aTable to aUser;

I am wondering if this central database would be considered a poor practice, and if so why?

Upvotes: 0

Views: 92

Answers (2)

Philip Kelley
Philip Kelley

Reputation: 40319

Conceptually, it could be done.

Implementation-wise, to make the various parts distinct from one another, you could use both naming conventions (as per @Sable Foste) and/or separate database schemas (table Finance.Users, GameApp.Users, etc.)

Management-wise, things could get tricky. Repeating some points, adding others:

  • One application could use a disproportionally large share of resources (disk space, I/O, CPU)
  • Tracking versions could be tricky (App is v4, finance is v7) -- depends on how many application instances you have to support.
  • Disaster recovery-wise, everything is lumped together. It all gets backed up as one set, it all gets restored as one set. Finance corrupt? Restore from backup... and lose your more recent game data.
  • Single point of failure. One database goes down, all your applications are down.

These (and other similar issues) are trade-offs you'll want to consider. Plan ahead, to lessen the chance that what's reasonable and economic today becomes a major headache tomorrow.

Upvotes: 1

Sablefoste
Sablefoste

Reputation: 4192

They way I look at it, a web application is nothing more than a collection of web pages. Because of this, it really doesn't matter if one page is about, say, cooking, while the other page is about computer programming.

If you also consider it, this is very similar to Openid, which I use to log into my SO account!

If you have your fundamental security implemented correctly, it doesn't matter how the user is interacting with your website. Where I would make this distinction is in two cases:

  1. Don't mix http with https. On a shared host, this isn't going to be an issue anyway; if you buy the certificate for https, make everything that way (excluding the rare case where this might affect performance).
  2. E-commerce or financial data should be handled fundamentally in a different way. If you look at your typical bank, they have multiple log-in protocols, picture verification and short log-in times. This builds confidence in user's securities. It would be a pain in the butt for a game site, or most other non-mission critical applications.

Regarding structure, if you do mix applications into one large database, you should consider the other maintenance issues, such as:

  1. Keep tables separate; consider a prefix for every table unique to each application. Following my example above, you would then start the cooking DB table names with 'ck', and the computer programming DB table names with 'pg'. This would allow you to easily separate the applications if you need to in the future.
  2. Use a matching table to identify which ID goes to which web application.
  3. Consider what you would do and how to handle it if a user decided to register for both applications. Do you want to offer transparency that they can share the same username?
  4. Keep an eye on both your data storage limit AND your bandwidth limit.
  5. If you are counting on these applications to drive revenue, you are putting "all your eggs in one basket". Make sure if it goes down, you have options to restore or move to another host.

These are just a few of the things to consider. But fundamentally, outside of huge (big data) applications there is nothing wrong with sharing resources/databases/hardware between applications.

Upvotes: 1

Related Questions