A work colleague is developing SQL Server database tables for a reporting project. Basically, the only activity on the table will be populating it from a ETL process (SSIS).
He insists on creating a complex normalized design but I do not think this is necessary as there will be no activity on the table other than INSERT from the ETL, and SELECT for viewing the report data.
From what I can tell this is more suitable to OLAP rather than OLTP. Or since all the data is just output to Excel anyway, one table should be enough to store it? There is no aggregation either. Why the need for multiple tables?
Any suggestions on this? Is a simple table the best way forward or is it better to use normalized design?
Best Answer
For databases of a significant size, the normalised logical design is typically created first. Following this, sample queries are run and the database is then denormalised for performance if need be (the physical design). If the gains are minimal, you may elect to stick with the logical design for the simple reason that it is easier to understand and is more efficient from a storage point of view.
It would be somewhat presumptuous to go for a denormalised design from the off without any sort of performance testing - even if this is clearly an OLAP system.