When create new Database

Question

I have a general Database Design question: When is it better to create a new Database instead of adding new Tables to an existing? The question is related to design/maintainability andalso performance issues.

Background: we are mirroring the main tables of our customer by importing the data every night into our DB called RM2. Some new (ASP.Net-)Projects need access to this data too, now i'm wondering if i should create new databases for each project or merge their tables with the RM2(current size: 37991.94 MB).

Answer 1

You can get other database for projects if any project want use only self database. But if you want get datacenter of this you should create for it a other shared database + self database for each project.

Answer 2

I won't necessarily answer your question, but I'll give you a bunch of other questions to consider as well:

1. When should I add files to a filegroup in my database? - When files get too big, where "too big" may be a matter of opinion.
2. When should I add a new filegroup to my database? - When you want to be able to optimize disk usage for different database operations.
3. When should I add a new schema to my database? - When you have a set of objects that are logically related and may require different default permissions for users.
4. When should I add a new database to my application? - When you don't need any referential integrity between any of the tables in the two databases. When you don't want to allow any ownership-chained permissions to cross between two sets of objects. When you want to independently backup and restore. When you want different SQL Server recovery models for two sets of data.

I guess that may have answered your question some. ;-)

Answer 3

This isn't a database design question. This is an organisational question. The organisational aspects of this question are much more important than technical questions.

The answer is: whatever makes life easiest for you as developers

For instance, you say:

Some new (ASP.Net-)Projects need access to this data too.

How integrated are these projects with your project? Do you actually share data (or write to the same tables)? For instance, if you make a breaking change to one of your tables, do you need to make changes to the code in the other projects at the same time? (Sometimes really hard to synchronize between two projects).

If you don't actually share data (apart from the customer data, which I assume is effectively read-only), then use separate databases (OR schemas). This makes changes a lot easier to manage.

Another trick is to have in each database a set of views onto the customer data, which lives in elsewhere, in another schema.

So, have a database per project, with views in each database onto the customer data, which lives in a single separate database.

Performance shouldn't really be an issue, unless the databases live on separate machines.