Designing a database

Question

I am currently trying to design a DB for a new project I am working on. My question stems around Normalizing and Denormalizing....what I am trying to figure out is should you always normalize to the fullest extent? The data will be information that the users will be revising very very rarely and will most often be interacting with the data view reports / views.

So my first attempts were normalizing the data, but then when I got deeper into the project and realized that they would very rarely be adding / changing data I thought it might be better to just denormalize the structure from the start.

Any words of wisdom.....there could be a chance that in the future they will interact with the data more than today, but still it will not be like an order system or CRM system.

Always appreciate the input.

--S

Answer 1

The usual advice for designing OLTP databases is normalise to 3NF.

Whereas, best practice for a DataWarehouse or OLAP database is to denormalise into a Fact Table and supporting Dimension tables (a star schema).

So it depends on your precise usage scanario. From your description it sounds like you have something closer to OLAP.

I would normalise everything initially and then if there are performance problems look at denormalising.

SQL Server indexed views could possibly help you if your reports involve large numbers of aggregations, and you should create indexes to try cover your query workload.

• SQL Server 2008 Indexing Best Practices

• Scaling Up Your Data Warehouse with SQL Server 2008

Answer 2

Normalization and proper design will save you problems down the road. Even if this particular project doesn't seem to need it, do it anyway, to be sure you're in the habit, and getting practice,

Most of the apps I've had to re-write were having issues due to a poorly designed DB.

So, as it's been said many times here, normalize as much as possible, unless it really hurts performance, and even then, only if there is a compelling reason to de-normalize.