When working on a big site, what is the rule for the number of tables that should be in any single database? Or does it just get split up based on server capacity and logical organization?

I assume for someone like myself the ceiling is virtually to the moon but Im just curious.

However many you need. Seriously.

A typical rule of thumb is there should be a maximum of 7 tables listed with join conditions in a data warehouse. If yours is for a site, it's probably transactional, so you'd want to access less tables at the same time. But that doesn't speak to how many you might have, dormant in a particular query.

If you have a gigantic, highly scalable database driving your site, you might take some of your core tables, and "partition" them out into a table per day. That could mean thousands of tables in a database. This is a very common scenario. I don't know how MySQL handles that (question for Tripy) but Oracle, SQL Server, and hopefully many others, will have no trouble whatsoever with this.

If you're reading a lot, you might want one giant table, so as to avoid joins and complex predicates. If you're writing a lot, you'll want as many "broken up" tables as possible, so that you can write a single logical operation and avoid as much as possible. For example, if you had people and addresses, you might want them in different tables so you can change somebody's last name, but not have to also rewrite (because of row splits) their address. But normally, normalizing is a good thing, for performance, and because other database people will understand it and better be able to help you.

I don't know how MySQL handles that

I'm honestly not even sure if mysql support partitions....
Never looked it over...
And a short google search tells me that it's very new...

MySQL 5.1 New Features: MySQL Partitions Partitioning is a way of pre-organizing table storage. You can say "some of the table's rows will go here, some will go there, still others will go to to still other places". Often, depending on the storage engine, the effect is to spread the table's rows over different files or even different disks.


We - one of the folks who write the MySQL Reference Manual and one of the folks who test new features - will try to touch on everything that we, or beta testers, or participants in the the MySQL forums, have ever considered is worth touching on about partitioning. Nevertheless we won't repeat what's already in the MySQL Reference Manual when we can't think of a different way to say it.


We'll show examples and test results that we ran with the version that's latest at the time we're writing this - MySQL 5.1.12 BETA.

The actual 5.1 version to download is the 5.1.26, but I'm not even sure it's tagged "stable"....


So, I would simply just say it's a bit too young for my taste...

Thanks guys,

what do you mean by this though:

might take some of your core tables, and "partition" them out into a table per day.

What we mean about partitioning is that if you turn that database feature on, 1 table can become 100 or 1,000 tables. In a lot of situations, this is considered a best practice, so that should give you an idea about how many tables an RDBMS is able to manage.