Good Database Design Part 3: Logical Design

Armed with requirements and a naming convention, we are ready to commence designing.

The logical model is a diagram of how the requirements will be structured; not how they will be implemented.

One of Einstein’s great quotes, ‘Make everything as simple as possible, but not simpler.’ Excellent words to live by, and definitely to design by. Even the most complex problems are a series of simple steps. Break it down.

Many of the entities of the logical model will translate easily into physical database tables. This should not be a factor of the process; it is just a convenient byproduct. It is important to keep the logical model free from physical restrictions—you do not care how your DBMS implements things. You do care about modeling the data structure as defined by the business requirements.

This may sound rather odd, but--so far--I swear by it. If you cannot arrange your model so that lines do not cross over each other, you may have some logical flaws. I recognize that your model is not actually flat. I also recognize that on larger models this may be even more difficult. However, when I see lines crossing on an ERD, it often indicates unnecessary joins. These may be in place at the physical level to simplify joins (not that that is any excuse), but they really should not show up on a logical diagram.

Embarcadero’s ER/Studio is one of the finest database products I have ever used. It is the single greatest modeling tool that I have ever used. And, unfortunately, it is prohibitively expensive for many outfits. Someone needs to explain the economic cost curve to them (yeah, had to look that one up—Econ 101 was a long time ago). For a nice freeware diagramming tool, DBDesigner (unfortunately, it is only for MySQL, but is capable of a decent looking ERD).

Modeling can be one of the most engaging areas of database design. You are creating something with a purpose without worrying about the physical and implementation limitations. Granted, you may not be able to do anything clever yet because you are bound by the business requirements. Still, modeling is more enjoyable than documenting.