CODEWITHSUNDEEP
mysql
Darkmyre
Darkmyre

Reputation: 73

Multiple databases or many many tables?

I've done some research on this question (both via google and on here), but haven't found anything I felt matched my situation so am asking.

I have a project that currently has a one account - one environment model, and is looking to expand to one account - many environments. The environments will be identical (at least as far as table structure is concerned) and will require around 100 tables. I'm torn between two possible approaches:

  1. Use a single database, with table prefixing to separate each environment and an unprefixed account table
  2. Use many databases - a central account database, and a separate one for each environment (the central one will likely have other central, once-only data, such as tables for our forum software)

Are there any significant performance gains/concerns with either approach? The data will (at least for now) all reside on the same physical server. Queries should only ever need to access a single environment (except in very rare circumstances) - and of course the main accounts record.

Upvotes: 7

Views: 2868

Answers (5)

Loopo
Loopo

Reputation: 2195

Interesting question. As a standard answer I would suggest letting one instance run all the accounts. I.e. the prefix solution. This is the approach that is used all over by hosting providers.

It seems to make sense to have one RDBMS running the show. Easier for backups and other system-wide tasks. I would suggest that in terms of performance one instance running is going to be much more efficient than running a separate process for each account.

If you need to enable load balancing the 'prefix' model would also be easier to scale since most modern RDBMSs have addons/features to support this type of functionality, rather than having to set this up multiple times for each instance for each account.

Modern database systems are easily capable of handling thousands of requests per second, they won't have any performance loss by looking up fancy customised (prefixed) table names. As long as you can come up with a simple hierarchy (account-prefix) way of separating accounts you shouldn't have a problem running thousands of tables.

The only potential downside is security. And in most cases even having multiple servers wont fix your security problem.

Upvotes: 3

Ja͢ck
Ja͢ck

Reputation: 173662

The project I'm currently working on is similar in that respect; one account for multiple sites.

The solution I've gone for is delegated authentication, one service that solely deals with authentication and issues verifiable assertions about an identity. This can be coupled with authorization as well. To get an idea of what is required you can look at the OpenID project, and OAuth2 for authorization.

Setting up this service is not easy (though the OpenID site has setup guides for it), but it does provide the flexibility to move your environments out to different physical locations without having to change your code. In fact, you're free to even keep a single database and perhaps move one environment to a dedicated machine when it gains more traction than the others.

Upvotes: 0

Marvo
Marvo

Reputation: 18143

We've got the multiple database thing at work -- and different database vendors (DB2, Oracle, MySQL.) HUGE PITA, though some of that pain may come from the fact that each database is owned by a different group. But if you ever need to join data (you think the circumstances are rare NOW, but you wait...) in the database (as opposed to the app) you'll regret the multiple server solution.

Upvotes: 3

Oleksi
Oleksi

Reputation: 13097

I think it'd be simpler to only manage one database. This will make development and configuration much easier, than having many databases to worry about configuring correctly.

In terms of performance, you can let the DBMS handle the clustering/distributed stuff, so you won't have to worry about it. Splitting up the data yourself isn't going to make things faster usually, since the DBMS can (usually) do a much better job at that then you can.

Upvotes: 3

aserwin
aserwin

Reputation: 1050

I would say use many databases to allow for future modification of the various environments. It is doubtful you are talking about static data that all of the site would use, each site deserves its own set of data...

If for no other reason, (as I said before) to allow for modifications.

Upvotes: 0

Related Questions