sbaer (5/6/2014)
If possible, establish a single data collection point. From there it is easy to distribute the client's data to the appropriate destinations. In the central repository, maintain all the the referential data that is NOT client specific.
Would this be a correct understanding of the above:
All of my "central repository" is stored in a single database called "Staging". As data is received, it flows through tables in Staging before getting to the final destination of the customer specific database. While in Staging, I can link to any of the central repository necessary, transforming data fields as necessary for the specifics of each customer. Once all of the cleanup and look up is complete, the data can be transferred to the customer specific database with the values required by the customer.
It would mean Staging would be a very large database with several schemas, stored procedures, and possibly hundreds of tables. But the data should reside there only temporarily as it is transformed so the physical size of the database would never be very large. I think the security team would buy off on this considering the data is removed to a more "secure" location once it is cleaned.
If I've missed anything, let me know. Otherwise, this seems like a logical and simple solution.
The staging would not be too large, the data would be removed from one import to another. It will need an amble breathing space but there should be no overall growth.
Several hundreds or even thousands of tables and procedures are no problem, establish a good logical separation via client_schema_object naming convention.
Otherwise, I think you got it!
😎