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.'
Answer by Scot Hauder ·
One of the main purposes for normalizing is to make insert/update/deletes efficient and as a side effect, saves some space. Since you are moving the data each night for reporting reasons I would denormalize it to facilitate building cubes. Then you can report out of both the SQL Server relational tables that you import into and do analysis with the cubes. This will also allow you to build in the SCD type 2 changes the users will eventually want.