Modeling to Normal ...
Clear all

Modeling to Normal Forms

Raymond McGirt
Posts: 10
Topic starter
Joined: 2 years ago

I attended the on=line Data Modeling Boot Camp this past week.  Among the presenters was Chris Date.  He spoke about Normalization for Database Professionals.  It was a deep dive into Normal Forms with proofs for why they hold true.

It got me thinking about how we create our models  Specifically, what Normal Forms do we consider optimal?  Or, which Normal Forms are practically achievable?

I don't have an answer (that's why I ask the questions here).  I have Chris Date's answer, which I'll hold for a while.  So, I ask you - How would you answer the above questions?

For my part, my conceptual data models do not have a Normal Form.  They tend to be relational entities with no attributes.  My logical data models tend to be in in 3NF, though I think that BCNF is optimal.  My physical models tend to be 2NF for performance, which I think is optimal.


Topic Tags
4 Replies
Posts: 9
Joined: 2 years ago

I try for 5NF. Most of the time though, once you hit 3NF, the model is also in BCNF, 4NF, and 5NF, as it is rare (but definitely possible) to see violations of these higher levels in practice.

1 Reply
Joined: 7 months ago

New Member
Posts: 3

@raymondmcgert, The first problem is with agreeing on what we mean by the different kinds of data models.  To me, "logical" is the model that acknowledges the kind of database management we are doing. If it is "relational", then yes, all the levels of normalization apply. If it is object-oriented, maybe not. 

In my book, ("Achieving Buzzword Compliance: Data Management Language and Vocabulary"), I consider three kinds of "conceptual" models (Technology independent): 

- "Overview"-the executives views, mostly major data categories, with mostly many-to-many relationships. Attributes rarely show up here, unless they help clarify concepts.

- "Semantic"-  This capture's each business area's jargon, business technical terms, etc. The semantic web is a good way to keep sorted out different departments' words for the same things, etc.  Ideally, each entity type defines a concept that is meaningful, at least to a particular business area, and attributes should only apply to that concept.  Thus, by following this discipline, at least 3d normal form will be achieved.

-"Essential"-what John Zachman calls the architect's view. This recognizes underlying patterns to represent the underlying structure of the enterprise. This uses terms that apply abstractly to larger groups of business areas.  Here, by definition, each concept is only described by attributes that are appropriate to that concept.  Note that 4th and 5th normal form are not that by virtue of conforming to rules underlying structure, but by whether or not the structure shown represents the underlying reality.

I look forward to responses to this...

Posts: 2
New Member
Joined: 6 months ago

I agree that it is uncommon for a model to reach 3NF and not also be in 4 & 5NF. I generally strive for 5NF unless situation demands otherwise.

So as with everything, the true answer is 'It depends'.

Posts: 3
New Member
Joined: 5 months ago

Agreed; there is always an 'it depends' but in my experience defining the logical model to 3NF is good enough to arrive at an optimal explanation of the data. The non-redundancy and adherence to keys is enough for practical purposes to derive a de-normalised database schema where necessary from a sound starting point. 

However, there is no substitute for understanding the data and the business context in which it is set, so this is the best judge of whether the 3NF model needs to be taken further to represent the situation as is.