I would like to discuss the following design issue:
I'm working in a Project in which several systems are involved, like SAS Marketing Automation and Microsoft Dynamics 2011. There is a database to hold the marketing campaigns information, other to stores the CRM data and a Data Hub to store and exchange general data across the systems.
At the beginning I created a database (The Data Hub) to store the data coming from an external provider. The other systems have their own databases and also read data stored in the Data Hub. Then I created new schemas in the Data Hub to stored relevant data from the other systems, like marketing lists, campaigns and so own.
Now I have to load data into the CRM, so I created a separated Staging DB to prepare the data before load them in the CRM database.
My new requirements are twofold:
- Store changes made in the CRM by the operators back in the Data Hub.
- Send the current data to the external provider.
My first try was to build new schemas to manage the permissions and so on, but I don´t know exactly which strategy should I follow.
Why is your opinion about this issue?
I know I should consider another factors like maintenance, backups, security, server size, etc.
Any comment would be appreciated.