> I know that one should go for normalization.

Absolutely!!!!
The fact that many databases are not designed properly and are not
normalised means that it is possible to create inconsistent data in
the table. By this I mean that you can add data to the table that
does not make sense in the real world, and if you allow the data to
be added that way, it is likely to cause a headache for you in the
future when you need to process that data.

> But how does it affect
> performance?

Two ways. Generally speaking, you need to store less data. For
example, in a system I worked on, the employees name was stored in
the shift table. That means that every time the employee worked, it
costed the database around an additional 50 bytes.

On the other hand, with a normalised database, you may need to create
additional key fields, but these are almost always worth the expense.
For example, what if one of the employees changed their name? Well
the poor old system had to then update 200 records in that table,
yuck. It also meant that if someone was editing one of those 200
shifts then the name could not be changed because of the record locks.

So normalised databases make it easier because you
a) Store less duplicate information.
b) Do not have to cascade anywhere near as many updates.
c) Help developers maintain the data.
d) To a large degree it removes need for complex triggers to prevent
illegal data from being added.

When all of this comes together, you get a performance benefit in
both the running of the system, the maintenance of the system, and
the development of the system. If need be, you can create stored
procedures and views to simultate the old tables and assist
developers who may not immediately understand the new structure.

Adam