I'm looking for advice on database design. Some background on my project...
Our legacy compliance management system is woefully inadequate for delivering business intelligence to our user community. The backend is an Informix database and most of the existing reporting solutions are based in Crystal. To gain full advantage of technology we employ in other environments, we are moving the reporting functions to SQL Reporting Services supported by a SQL Server 2005 database. The advice I'm seeking has to do with the design of this database. The data will be extracted nightly from Informix to populate the SQL Server db.
I've designed a database structure that is remarkably like that of an OLTP database. I'm now thinking that is should be structured on more of a data warehouse design as that is truly what its more akin to. The only reason I hesitate, is that a requirement exists to be able to view changes over time to different aspects of a person such as change in address, licensure, etc. Much of this capability doesn't exist in the production processing system today. i.e. it keeps on the most recent address and overwrites the previous one. I have a feeling the user requirements are attempting to address many deficiencies of the legacy system by implementing them in the reporting solution.
Much of this data does change frequently with license credentials changing, effective, expiration, renewal and continuing education compliance dates. The design I have now is more normalized than the existing system, but lacks many dimensions that would make it truly a data warehousing operation. Should I go the extra effort and implement a DW design or leave it as is? I'm open to thoughts anyone may have regarding this.
Then again, maybe I'm just thinking too much and needed to 'type out loud.'