Saturday, March 11, 2006

Logical Data Modeling: An Introduction

Data Modeling In General
I found it worthy to note that the purpose of creating a normalized logical data model is to accurately document the business entities and relationships between them in a detailed model. The value of this logical model is two fold: One is for the business owners to have detailed comprehension of their own business information; Second is to transfer this business knowledge to the developers to equip them to accurately build the system to the exact business needs. They will also create an optimized physical data model (Schema) for the system based on this logical model. Therefore it is critical that you interview the appropriate knowledgeable business people and document the business as much as you can. You will run across business areas in which the business may not be well defined. It will then be your responsibility to understand this area as much as you can, model it accurately, and get approval on the design by the business owners.

Meta Modeling: A WarningDO NOT fall into the temptation to meta model a business area that is not well understood by the business owners in the attempt to avoid your due diligence. This includes throwing in many-to-many relationships into the model to solve problems where the relationship between entities were unclear. This will lead to complications in development and result in a system hard to maintain, use, and raises the cost of ownership. Think like a lawyer. The point is to model the business accurately and provide a natural means to describe and enforce the definition and relationships of the business entities that need to be managed. Only after you have exhausted all avenues of research is when you should use a meta model. But isolate that meta model to be applied specifically to the area that is requiring that level of flexability. Then the meta model will be your friend and not your enemy.

Step 1: Use Case Modeling
The first thing you need to do is document the real world scenarios of the business areas you need to model. This should be detailed in proper sentences to work out the nouns and verbs. The nouns and verbs will be your key to discovering the data model. Of course there are many other benefits as well: Classifying your users, identifying external systems and data dependences, identifying user interfaces, discovering required reports, understand business processes, and finding the scope of the system you are to build.

Step 2: Extended Relational Analysis
Data modeling using the rules of normalization ( is not the most natural and easy way to model any system. This can be proven by the very fact that many people don’t truly understand how to normalize a data model. But there is good news. There is a different approach that makes it childs play to create a normalized data model using a technique called Extended Relational Analysis (ERA). I’ve learned it years ago and has been critical to my career. So I highly recommend it. This doesn’t mean that you don’t need to understand the normalization rules, but it does mean that you don’t have to memorize them in to a daily magical chant so you don’t forget. Think of ERA as an as a way of thinking and a means to organize your thoughts rather than a software tool.

ERA technique is broken up into three areas: Entity Analysis; Relationship Analysis; Attribute Analysis. Entity analysis uses nouns out of the use case model to help define each entity that a business is required to deal with. Relationship Analysis uses verbs out of the use case model to help define each relationship between business entities. Attributes Analysis uses the modifiers (Adjectives and Adverbs) to help finish the definition of each business entity defined or create new ones missed with the other two analysis steps. There are many classes you can take out there to help you learn this technique in modeling ( Its worth the expense, trust me. It will make your modeling efforts much easier and the end results increase your chances of lowering your total cost of ownership of any system you build. Just do it.
For more detail see

1 comment:

David A. said...

I was lucky enough to be introduced to ERA in 1982 while a youngster at EDS. It was a defining moment that I will never forget. I use it professionaly everywhere I go. I watch with frustration while people (DBAs)struggle at something that has become so very simple to me. Sometimes they listen to what I have top say, most of the times they don't. Being the DBA has its perks and one of them is not having to know what you are doing. In the end, when it doesn't work very well, everyone will blame it on the programmers!!!!