When to apply normalization in database design

Question

Hello database administrators. Recently, I have been reading about normalization in order to improve my database design skills. However, I am a bit confused about when to apply this technique. Prior to learning about normalization I would usually read the scenario/description in the requirements and draw up an ER diagram (using the Chen's notation) using nouns as entities and verbs as clues to their relationships then go straight to carrying out the SQL statements to create the tables.

At the moment it seems to me that normalization is carried out after designing an ER diagram then applying it to any entity that contains a multi valued attribute.

Am I correct in thinking this? Or am I supposed to carryout normalization prior to creating an ER digram, perhaps after listing the possible attributes of a system?

Answer 1

It sounds like you learnd it somewhere in an CS course or similar (which is a good thing IMO). I learnd it in some classes as well and I liked the approach that was taken by the teacher:

colleagues from other universitites, courses,... told me they were given big papers that explained normalization in a very theoretical (therefore rather boring) manner and they had no clue how to move this theretical knowledge into an ER-diagram.

We, on the other hand spend many hours learning how properly design ER-diagrams, the ideas that make up a good and maintainable database, how to solve arising problems in the diagram as well as in the database (with SQL) itself and much more. After these concepts were thought, we were told "what you did is called 'normalization'". You have done this and that when designing the ER, this is the 1st form of normalization.....

A sentence that got stuck in my head for such questions from my teacher is "with a well-though and properly designed ER-model, you ALREADY have a normalized database, so you do not need to think about all the forms of normalization".

Since your question is not about HOW/WHY use normalization but WHEN in the designing process, I think my long text above gives you a possible answer: when you understood the principles of good DB design, than your final ER-model IS normailzed (e.g. you created proper join tables, used foreign keys were applicable,...). I think it is a process that needs to go hand-in-hand with the setup of the tables, their relations and so on.

Of course, normalization is not always needed or wanted but in most cases, "drawing" the ER and thinking it through saves you from applying normalization by looking at the model and thinking: "OK, 1st form, are my values as atomic as possible"

Answer 2

Good question. Database Normalization is often taught as a process of Normalization by Decomposition - i.e. you take an existing schema and then improve it by applying nonloss decompositions that create a new schema from the old design. In practice that is not usually what is required.

If you are creating a new table or set of new tables then more typically your starting point is a list of the attributes you want to represent. You should therefore identify dependencies between the attributes and then create normalized tables directly based on those dependencies. Often this can be done mentally or on paper before the tables are even added to the actual design.

ER modelling isn't necessarily the best way to achieve this. Object Role Modelling is a way to create a semantic model that accurately captures more aspects of data modelling and business rules than ER modelling can. You can use tools such as NORMA to generate a 5NF database schema directly from an ORM model, without any specific normalization steps.

Answer 3

Normalization, as a process, occurs after the logical modelling, and prior to the physical model. Too often, people skip the actual logical model and end up with a physical model that has business friendly names. The logical model should define the data entities and relationships (typically confused with table constraints), hence the term 'ERD.'

Since physical models are often discussed in terms of 'Normal Forms,' the tables must have already been 'normalized,' right? From a design perspective, the process of transforming that into a physical model is when you apply normalization. That is when entities become relations (or tables) and the relationships are enforced through constraints, as a byproduct of the normalization process.

In other words, your thinking is correct :)