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.