Is normalization process needed?

Question

Well after learning DBMS as a subject i got so many questions in mind. Normalization is one of them. As i learnt it there was a lot more confusion and i found that whatever we do in normalization process we can do it by general common sense also. Even while making projects also people are not used to follow it. So is it really needed? Is it followed in the companies? I am asking this question because probably it might consume more time to normalize the database. We can directly normalize it using just common sense therefore i don't think there is any need of following the standard normalization procedure. Correct me if i am wrong.

Answer 1

To answer your question, yes normalization is needed.

Common sense is a relative term and is open to interpretation. RDBMS's have been around since the 1970's. Normalization has been put into use on countless projects over the past 30 years, much to the benefit of the applications being developed.

The most complex time-consuming problems I have worked on in the past 15 years can be directly attributed to the database designer's lack of understanding (or willful ignorance of) the rules of normalization.

If you don't have the time to design a database properly, then you are in the wrong field, or are working on a project that is doomed to failure.

Answer 2

Every rule, every process, every pattern that is taught in programming courses is an effort to try to "institutionalize" common sense. If all of your developers have perfect common sense at all times and are clear-headed and insightful, then you don't need to follow anybody's rules, processes or patterns.

However, as the saying goes: "Common sense ain't" - Meaning that people in fact often don't have perfect common sense and won't naturally do everything that they need to do to keep themselves out of trouble both now, and even more especially into the future.

Normalizing your database is very much a common practice in organizations with professional data modellers and developers who understand the importance of getting your database schema right. It does not take a lot of time to normalize your database. In fact, most experienced data modellers do it naturally as they build out their models while they're still in the whiteboard phase.

Taking the (very little) time it takes to ensure that your logical schema is normalized before you start building, and taking the time to understand precisely why you are denormalizing in your physical schema, and what the impact of that redundancy will be on your code and business is an important part of a professional development process.

Answer 3

The point is, however, that the principles of normalization take those common sense ideas and provide a precise, accurate characterization of those intuitive concepts.

Chris Date and David McGoveran on The Principle of Orthogonal Design.

Answer 4

Normalization may be mostly common sense but it is "formalized common sense" (Chris Date). Perhaps the examples you studied at college were quite simple and easy to resolve in your head without careful application of logical methods. In practice not all data models are that simple. Having a precise way to analyse, understand and resolve complex problems is important once you get beyond a tutorial.

Answer 5

To answer your questions: If you can live with the consequences it really isn't needed. Yes, it is used by all the companies I have worked with.

Common sense can get you only so far. In some cases common sense is just plain wrong. Many of the design problems I have seen have been a result of giving in to common sense.

Common sense tells you social insurance numbers are a unique identifier. Besides the legal issues of using them as such, this fails on at least four counts:

1. Most countries began issuing the numbers before they had the appropriate automated systems. In some cases more than one person was issued the same number.
2. Some people may apply for and get issued more than one number.
3. More than one country issues social insurance numbers.
4. Not everyone has a social insurance number.

Data normalization is a different skill set than application design. Data base design deals with data at rest, as opposed to application design which deals with data in motion. Key values are critical to database design, while an application may function perfectly well without them.

Like many good practices it is a case of pay me now or pay me later. You can spend some time now normalizing you design, or spend lots of time later fixing you design. However you do the design, it may not be a perfect model for your needs. It is easier to add appropriate de-normalizations later with a full understanding of the risks and costs, than to remove unintended de-normalizations later.

Some of the things you should consider.

• Is the candidate key stable over time? If you allow the user to create it, it likely isn't.
• Should you use a surrogate key for this table?
• Besides the primary key are there other unique keys?
• Is the candidate key truly unique?
• Are the attributes truly dependent on the primary key?
• Are any attributes derivable from other data we have? If so, they are a de-normalization. Consider deriving them initially and de-normalizing later if necessary.
• Are all the attributes of the table part of the same concept? If not, split them conceptually, and consider how you want to do the physical model.

Once you have a normalized conceptual model you should consider whether this will be your physical model. You should be close and some companies don't bother with a formal transformation step. In other cases, the model may need significant transformation. In some cases, you may relax the rules for an entity. In others you may combine entities. Some cases like subtypes have alternatives for implementation. A bit of consideration at the beginning can save a lot of rework later.

If you have input from the business be sure you understand what they are talking about. I have seen the same term used to refer to three entirely different things. In others the differences may be subtle, and might be important.

Answer 6

To correct one common but important misconception: You don't "normalise a database", you normalise a Logical data model.

Data design, the process of designing how your data will live at rest, has four distinct phases: Conceptual, Logical, Implementation, Physical.

Normalisation is the process of refining the Logical model. Is it required? Yes, if you want your Implementation and Physical models to protect data integrity and to perform well.

Tactics that protect integrity can reduce performance so sometimes the normalisation process applied to the Logical model is reversed in the Implementation and Physical models, a process usually called denormalistion. This is a tradeoff - denormalisation means accepting less data integrity protection in the name of better performance.

(This also means that questions posted here of the form "is this table normalised?" are meaningless unless the poster includes the conceptual and logical models from which the table was derived.)

I suspect the OPs situation is that they learned "a database system" rather than learning "data design". I often see this in people who learned Microsoft Access. They come to believe that the database (the physical model) is the whole data model.

• "Normal Forms: Dependent on Dependencies", Fabian Pascal.
• "Data Warehouses and the Logical-Physical Confusion", Fabian Pascal.
• "Pro SQL Server 2005 Database Design and Optimization", Louis Davidson et al.
• "Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design", Michael J. Hernandez.