Personally I find database normalization really interesting(in fact really enjoy doing it) but while designing the schema and normalizing it for the asset manager(containing generic and project specific assets with each having types of arbitrary relation depth) I am currently working on, I came to this basic question i.e. to what extent should one normalize a database(I am aware of 3NF) such that making multiple query, insert etc, doesn't become more costly than programmatically taking care of data inconsistencies, redundancy etc which normalization tries to avoid?
In designing your database you should normalize it to the fullest extend (well, with common sence in mind). While developing you'll notice some potential performance issues. In this case you may want to resort to using views or even denormalize a bit.
Though this differs from case to case, since some programs have serious requirements on performance. Others may be used by just a few users, in this case performance may not be that important.
In order to make a good decision on whether or not to denormalize and thus being able to execute simpler queries, you should run some performance tests for both cases. Keeping in mind what denormalizing would cause to the maintenance of your database.
In case of denormalizing, be sure to document this to the fullest extend.