I am currently working on a user management applications (UMA) that manages a data warehouse security management (users, rights etc.).
The data warehouse has production databases (and production users) + pre-production (PP) databases (and PP users).
UMA like any other applications has its own PP and Prod environments.
Currently UMA PP deals with datawarehouse PP databases only and UMA Prod deals with datawarehouse Prod databases only.
Pros and cons (I see so far)
- intuitive - key testers (that includes data warehouse clients) are familiar with testing using PP users on PP databases
risky - by using a PP version to change actually used PP databases users might lead to security issues (a PP user might see some data that is not supposed to see) since PP version is delivered for some key users to actually check that UMA behaves correctly.
licensing - UMA must provide aggregate information for all databases (PP and Prod)
Not sure if it is relevant, but producing a new PP database in the data warehouse is not an easy task, so PP databases are "recycled" (partially data wipe, transfer data from a Prod.
Also, UMA also handles reporting systems users.
I am wondering what is the best practice to model in this scenario?
Question: How to model a user management system that deals with both pre-production and production databases?