164
Part II: Building Databases and Working with Data
Database Design Layers
Every database can be visualized as three layers: domain integrity (lookup) layer, business
visible layer, and supporting layer, as shown in Figure 7-15.FIGURE 7-15
Visualizing the database as three layers can be useful when designing the conceptual
diagram and coding the SQL DLL implementation.- Domain Integrity
- Look up tables
- Business Entities (Visible)
- Objects the user can describe
- Supporting Entities
- Associative tables
While you are designing the conceptual diagram, visualizing the database as three layers
can help organize the entities and clarify the design. When the database design moves into
the SQL DDL implementation phase, the database design layers become critical in optimiz-
ing the primary keys for performance.The center layer contains those entities that the client or subject-matter expert would
readily recognize and understand. These are the main work tables that contain working
data such as transaction, account, or contact information. When a user enters data on a
daily basis, these are the tables hit by the insert and update. You can refer to this layer as
the visible layer or the business entity layer.Above the business entity layer is the domain integrity layer. This top layer has the entities
used for validating foreign key values. These tables may or may not be recognizable by the
subject-matter expert or a typical end user. The key point is that they are used only to maintain
the list of what’s legal for a foreign key, and they are rarely updated after initially populated.Below the visible layer live the tables that are a mystery to the end user — associative
tables used to materialize a many-to-many logical relationship are a perfect example of a
supporting table. Like the visible layer, these tables are often heavily updated.Normal Forms ....................................................................................................
Taking a detailed look at the normal forms moves this chapter into a more formal study of
relational database design.Contrary to popular opinion, the forms are not a progressive methodology, but they do repre-
sent a progressive level of compliance. Technically, you can’t be in 2NF until 1NF has been met.c07.indd 164c07.indd 164 7/30/2012 4:18:16 PM7/30/2012 4:18:16 PM
http://www.it-ebooks.info