Good Database Design Part 5: Physical Design

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.