Convert the logical model into an
actual database. This is where you work within the limitations of your DBMS and
hardware, keeping the physical database design as close to the ideal of the
logical model as possible.
If you are fortunate enough to have good modeling software, most of the
physical design may be taken care of for you. And you are probably designing
against an ERD, which, I find much easier.
In Part
Three I said, ‘If you cannot arrange your model so that lines do not cross
over each other, you may have some logical flaws.’ I think that this holds true
through the physical model. The only exception that I have seen is using a
universal reference table; one reference table (that is generally two
tables--one for type and one for values) instead of specific reference tables.
Personally, I do not like this approach. More accurately, I like the approach
as it is a neat concept, but poor implementation. There are often a couple of
items that require more than one attribute, so what do you do about the
exceptions? Additionally, you are renaming your columns to reflect the
reference value which is lame (and breaks natural joins (you use natural joins
all the time, right?) or not renaming your columns in which case you cannot
have more than one reference domain within a relation and your names do not
reflect the actual attribute which is even lamer or a combination of the two
which is the lamest by far.
How do the entities translate into tables? Oft times they will be identical to
the logical model. There are certain constructs that do require changes. A lot
of this depends on how you modeled the logical design. If you created a very
thorough logical model, it may contain business rules that will need to be
enforced via code mechanisms, and not immediately reflected in relations. It
may be nearly identical to your logical model.
Ensure that any structural changes you make at the physical level are reviewed.
There is the possibility that translating something changed an existing
relationship or even broke a primary key.
Creating the tables is really just the beginning of the physical design. It is
critical as it is the foundation, but there is a lot more to do.