Denormalization is used when there is a lot of tables involved in retreiving data.Denormalization is done in dimentional modelling used to construct a data ware house.This is not usually done for data bases of transactional systems.
This is one of those great It Depends answers. People following the Kimball approach will tell you almosgt always Inmon almost Never. But simply speaking Star is better in OLAP and Snowflake in Operational DBs or for ETL.
There are various reasons behind normalizing and denormalizing. If you dig deep into the practical world everywhere all the applications have both type of formation. You cannot deal with only one type of application. Example of Practical Scenario: Banking contains payments. Payments can happen accorss banks borders and worlds. Hence standards would be different. Keeping this in picture if you feel that the WH should be constructed region wise would be a big investment. Instead small databases are created with Data Mart in picture. It's not a full data mart. Companies prefer to create OLTP databases with concept of OLAP existing. Most important for any customer is quick response. A person wanting information about history would definitely have to wait even though the report would just take a min to generate. Coz this is business. Whereas a customer who is at the desk would need the info and at that time the bank representative has to quickly retrieve the record This can happen if normalization is done. Again here you would say that if it is too much normalized then it would be slow as there are more tables involved. This leads to the question which was raised. Thus it's a combination based on the practical world's use and thus the designers think both way while preparing the chart way forward.
