When should a new Database be created rather than new tables in an existing Database?

When should a new Database be created rather than new tables in an existing Database?

开发者_开发问答For example, if I have a site and I want to add a forum to it, should I add the forum tables to the existing Database or should I create a new Database and add the forum tables there?

In other words, when does it make sense to add new tables to a new Database rather than to an existing Database?

Thank you.

It is generally syntactically easier to join on tables that are in the same database. It's really a question of logical grouping.

In the case of a forum on a website, if you have a shared "User" table, or similar, you may want to keep things in the same database because it can make things like foreign key constraints and joining simpler.

you could design your application so that every table is in a separate database. If that seems ridiculous, ask yourself why it seems ridiculous, and you are likely to come up with a heuristic about what tables belong together. you can then apply that heuristic to future additions.

This answer doesn't really apply if you are talking about 3rd party databases. For 3rd party products that need their own database, I think ringfencing into a separate DB is almost always the right call.

I'm going to assume that you're really talking about forum software like PHPBB, or whatever - but this advice is still generally applicable.

You will want to have each discrete piece of software (each forum, blog, etc) in its own database whenever possible. A good rule of thumb is, if you have to install it, put it in its own DB.

The reason many software packages support being installed into a single database is an artifact of the early shared hosting industry, where a hosting account would be arbitrarily limited to a single MySQL database even though the number of databases are more or less irrelevant to performance (well, that's a simplification - putting everything in a single database can in some cases impact performance negatively, but we won't get into that.)

To wit, there's no reason to install everything into a single database unless your host only provides you with a single database.

If the items are totally separate, they can be in a separate database or in the same one. Some of the considerations for making them separate might include whether or not they need to have a different recovery model or different partioning rules or if they should be on separate physical hardware.

Data warehouses are often in totally separate databases often on a different server with an import process that moves the records to the warehouse. That is because the complex reporting queries run from a data warehouse might interfere with the day-to-day insert/update delete of the users of the transactional database.

If the data will be related however, you are usually better off keeping them in the same database. This enables you to easily retain the data integrity by having foreign key constraints for instance. In SQL Server (and I'm sure other major datbases probanly have some sort of equivalent), you can use schemas within a database to keep like things grouped together. So all my forum tables would be in the Forum schema and all my user related tables wopuld be in the User schema etc.

This is simply a question of meaning.

A database has some meaning: some statement or purpose or definition or reason for existing.

If there is no "meaning", then put all the data into one database until the meaning is sorted out.

create a new Database and add the forum tables there?

Never. That splits up something into multiple databases. The point behind a database is to keep things together, so you can work on related things in a single database.