1

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?

  • 1
    I guess it depends on how much data you will be pumping in, how often, and what the access method(s) are. Normalization will dedupe many values, minimizing the amount of space required and the number of records in each table. This alone can increase performance and allow for better index optimization – Steve Mangiameli Jun 29 '16 at 18:43
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.