Should I put my entire site on one database or split into two databases?

views:

answers:

+1 Q:

Should I put my entire site on one database or split into two databases?

My webapp that I am developing will incorporate a forum (phpbb3 or smf), a wiki (doku wiki flat file storage), and my main web app.

All of these will share a User table. The forum I expect to see moderate usage (roughly 200 posts a day) to begin with and will increase from there. The main web app will be used heavily with triggers, mysql events, and stored procedures.

Would splitting the forum and main web app up into separate databases be the wiser choice (IE maintainability and performance)?

Keep your app simple to begin with. If you're not expecting a huge amount of traffic to begin with, then it's fine to have a single database. You can always upgrade your site later. Changing which database a table is stored in shouldn't require a huge amount of code.

Mike 2009-12-24 07:29:38

It depends. If there is any chance of optimization, then splitting is good not otherwise. Another thing is that even if you split, you should be able to manage them all successfully, a bit of overhead.

Sarfraz 2009-12-24 07:30:08

+2 A:

Why would you ever use two databases? What possible reason can there be? Please update the question to explain why you think two databases has some advantage over one database.

Generally, everyone tries to use one database to keep the management overhead down to an acceptable level. Why add complexity?

S.Lott 2009-12-24 07:32:22

I've got to say that in light of your comments to the question (both current and the original one that's now deleted) I find grammatical errors in your own answer rather ironic :-)

ChssPly76 2009-12-24 07:36:26

but he does have a point. A single database does make it easier to maintain. However, if for security purposes I could split up the databases where admins can access everything and users can only access a specific database.http://stackoverflow.com/questions/282823/multiple-databases-vs-a-single-database

WarmWaffles 2009-12-24 07:41:42

@WarmWaffles: Please add these facts to your question. Please update your question. Don't place them as a comment buried in some random (downvoted) answer.

S.Lott 2009-12-24 13:24:48

@ChssPly76: Are you claiming that "two database has some advantage" is ungrammatical? It could also be a spelling mistake. It's a common-enough native English locution based on "two databases" being a set and therefore singular in the mind of the speaker. I'm not sure I see what's "ironic" about grammar errors and a question that confused me. Perhaps you could elaborate.

S.Lott 2009-12-24 13:28:43

@S.Lott - don't add comments. Fix your answer. Just fix your answer. Please fix your answer to be correct. Many, many people will be reading it :-)

ChssPly76 2009-12-24 18:06:29

@ChssPly76: Thanks for the helpful response. I'm sure you feel that there's something in there that I need to fix.

S.Lott 2009-12-24 18:59:25

I was joking (apparently, it didn't work) - hence the smileys in both comments. There were two typos in your answer - one was fixed by Michael; the other, as you've explained, can be read both ways (though saying "... why you think **having** two databases has ..." would likely be more readable). At any rate, nothing's wrong with your answer; I just found your "fix it" comment absolutely hilarious. Sorry if you took it the wrong way.

ChssPly76 2009-12-24 19:40:01

@ChssPly76: Thanks. I'm old and humorless. Also, I'm sensitive to correcting my mistakes (that's part of how I got the all the rep. points.)

S.Lott 2009-12-24 22:23:30

For the sake of maintainability in the future, I would definitely recommend against splitting DBs when you have a shared table. This will only serve to increase the size of your queries (since joins across DBs will require further qualification). This also will eventually lead to confusion when someone new can't figure out why their query doesn't work.

Furthermore, if the two DBs are actually running on separate servers or instances, you won't be able to join and will be forced to move back and forth between code and query to do something that would otherwise be a simple join. That may not be a big deal for a simple one row look up type queries, but for more complicated summary type queries, it means dumping off a lot of the processing that RDBMs' are specifically optimized for into your more general purpose programming language.

Rob Van Dam 2009-12-24 08:59:33

separating to two database could not guarantee performance. putting tables in different tablespace (i.e. different drives) could boost performance, as independent queries doesn't often compete for hard disk's read/write head's access turns

Michael Buen 2009-12-24 09:33:16

ansaurus

tags:

views:

answers:

Should I put my entire site on one database or split into two databases?

related questions