I was going to post this on Database Administrators, but as it is slightly related to PHP, I felt it would be better to post here.
I have a website that currently uses 8 databases, with tables within these databases that all link back to a particular table in the primary database (if that makes sense). I am aware that this is pretty poor design, everything I know is self-taught, so my knowledge with database design is pretty limited. The reason for me originally designing the databases like this is because the content within them will be increasing exponentially. I therefore felt that I should spread the load into separate tables, but I couldn't do this without having separate databases (for complex reasons).
Currently, for each PHP script that I run, I initiate 8 connections to my MySQL Server, one for each database. I know this is bad!!!
My colleague has suggested a complete redesign that uses just one database and a few tables, but with a lot more fields and a lot more content. My question is, quite simply, is he right? Should I try and combine all my databases into one and have as few tables as possible, even if the data is going to be pretty large? The argument he puts forward, is that databases are designed to hold large amounts of data whether it be 10 million or 10 billion records. This is their job, so I should just trust them and design the database in a way that works easiest for me.
I have read through the following Stackoverflow questions but I think my question is slightly different for the reason that the database is likely to have an extremely large amount of content and therefore the answer may be different. Apologies if not!
Thanks in advance
My question is, quite simply, is he right?
Yes, you need to keep your database hits as low as possible, not to increase it. If you have only one user, you open 8 connections. If you have two, you suddenly need 16! Three - 24!! How you came to conclusion, that this would spread the load? In my opinion it actually does the opposite, not mentioning problems with relational data and the design of the application itself.
So, some basic rules:
database - one application -> one database (unless really necessary and you need to know WHY and HOW to do that)
tables, columns - depends on the data you need to store, but generally you want to make it simple, flexible and easy to maintain over time, especially if it is a huge website (or plan to be).
If you'd have a query, that needs to select the data from multiple tables, you wouldn't write multiple queries either, right? You'd write one query with "joins" to do only one database hit, and it's basically the same principle with having multiple databases...