We have SQL Server DWH server. It has dwh_raw, dwh_prod and so on databases with client data, business operations. Now there is a task to allocate new data - on employees, their salaries and so on.
Is it better to use a separate database or a separate shema inside dwh_raw database? Plus the dwh_raw database is daily copied to dev server, so if we use it, we need to provide security both on prod and dev servers
Personally I would say it is good old fashioned way to kept it in a different database or schema for tight security implementation.
If the tables are to be used in conjunction with the others tables relatively scarcely. That’s is if the data in itself is self contained a separate database will be the answer. If it is not then a schema or user could be enough.
When you need significant table structure in addition to what has been established already, which has administration/security requirements that are different from all the other data, those tables for the employee data want to be in a separate database to make the boundary between it and the rest of the data simpler and less error prone.
