One database or many?

[drrollins]

I'm fairly new to data warehousing, though I've been a database developer for a long time.

At my new company, they have all data, including the staging data, in a single database.

This seems to break all kinds of conventions, and seems like it would cause issues with security, backup/restore, and other things.

Is this done in the data warehousing space? Are there substantial reasons for consolidating (or not consolidating) all warehousing activity into a single database?

 

[MDXer]

Well first off Data Warehousing will seem to go against all of your learned conventions depending on the methodology followed.

As for Having the staging area with in your DW if done right the only area it may pose a problem with is backups. You can implement a different schema to break the tables out into a more identifiable group, and apply a standard naming convention. Your security can be handled through the schema as well. As for performance it depends on how your files and files groups are structured as to the impact. Seperate file groups on seperate drives will cause little to no overhead against the production DW tables.

Data Warehouse vs OLTP conventions can be so different that I have been able to train developers with little to no DB experience easier than I have someone with 10 years of OLTP development.