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?
Best Answer
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.