Saturday, January 27, 2007

The Importance of a Logical Data Model

As this is my first post I wanted to start with an explanation of the theme of this blog. The title: Rapid Application Development, refers to the software development methodology started by James Martin in the 1980's, not to some vague marketing concept as the term is more frequently used (for details see my article:

My specific spin will be designing, architecting, and implementing Microsoft .Net n-tier web applications since that is my career and my passion. That's the idea anyway, let's see how it looks after more than one post. ;)

Appropriately I wanted to start with with a discussion of the importance of developing a Logical Data Model, which should represent a vital step in the building of all software development projects, and is a core part of the rapid application development methodology.

Previously I'd thought logical data models were a crutch for people who were unable to model physically. I mean, why use some rough approximation of a real data model when you could use Micorosft Visio to develop something that will actually turn into a real database in order to start your actual software development?

Recently, however, I've had the pleasure of working with Steve Dempsey, a man who worked with James Martin and knows the RAD process inside and out. More specifically he is someone who believes a project will not suceed without a logical data model.

The more we've discussed our differences of opinion, the more I've come to concede my point. It hit home for me when I considered a recent project where using it would have drastically shortened the length of the project.

It comes down to how you apply the logical data model. For instance it's not just a starting point for the database, think of it as a starting point for the object's in an object oriented world as well. The strength of the logical data model is realized for instance when you build UML sequence diagrams based on the entities in your entity relationship diagram - and more specifically when that doesn't mirror your actual database structure!

My recent project used objects that didn't mirror the logical data model and the consiquence was non-intuitive code, a degredation of maintainability, and designing in complexities that could have been avoided.

Conclusion? Logical data models rock. Use them. They're not a crutch, they're a launching point for development.

And in case you're visually oriented consider the contrived example below where employees are denormalized into their company in the physical model, but are used in the logical model and sequence diagram (click the image to enlarge):

If this thought make sense then this is a BLOG is for you. Not sure? Please comment. I will post more concrete how-to's, .Net code samples, my favorite web resources, and undoubtedly random thoughts that may still interest you ... some other day.

- Lee


Sai said...

Hi. I was looking at your physical and logical model and it seems that the logical model looks like a physical model.

From what I have done, in a logical mode, I would use more of business terms.

I could be wrong though... Would like your thouhts please

Lee Richardson said...


I would say the difference between a logical and physical data model is an area up for debate. In terms of things people can probably universally agree upon:

1. Logical data models have many-to-many relationships where physical data models have associative or resolution tables.

2. The goal of a logical data model is primarily to identify entities and their relationships while ignoring the details of primary keys, foreign keys, and indexes, while the goal of the physical data model is to hold data, access it quickly etc.

Microsoft Visio, my preferred modeling tool, doesn't support #1, so that's a moot point for me. And as far as #2, on my projects I gradually refine the logical data model into a physical one, so the goals overlap.

So in my world the two would always be nearly identical except when politics or performance dictate the need to denormalize (i.e. my logical model is almost always more normalized than my physical model).

You're the second person now to point out my somewhat casual use of the terms, so I've listed it in my ideas for future blog entries.

Anonymous said...

Well, I don't really see the advantage of your visual example. I am looking for reasons to embrace the Logical Data Model and that is how I stumbled across your page.

As far as I can tell, the only reason you would separate out employee from company in the way shown is if and employee could have multiple companies at different points in time AND you cared about the previous companies. This can be known up front and usually is and could be designed for immediately. So why initially denormalize them? The only reason I can see is to simplify for non-tech people. I have to search for more justifications for Logical Data Model.

Lee Richardson said...

"The only reason I can see is to simplify for non-tech people"

I don't buy that argument, but there are some similar ones. For instance sometimes it is politically advantageous to present things one way when you actually implement them another.

But after reading your comment and re-reading my post about a year later I think the visual example is too convoluted. But that doesn't mean I still don't believe in the value of logical models to display the way things really are vs the way the database portrays them.

For instance I have occasionally found it useful to denormalize my physical more than my logical for either performance reasons, or because too many 1-1 relationships can just get cumbersome in code, even with a good ORM tool. I talk about this somewhat in my Data Modeling Mistakes series.