Database Structure

The structure model is a hierarchical model and illustrates the "first cut" physical organization of the objects in the database. Children contain the key of the parent. In this example, the physical location of CustomerLocation table is clearly illustrated.

Producing the structure model is the first time elements of the logical (business) and physical (database) meet. It presents the perfect opportunity for the data analyst and database administrator (DBA) to begin their dance.

This "first cut" into what the physical database might look like. There is too little information to make design decisions at this point in the process, but it gives the data analyst and the DBA a place to begin their cooperative efforts.

What you KNOW at this point in the process is that the it seems logical in this business problem that a customer has more locations than a location has lots of customers. It's a "best guess." We will learn more as we work our way through the process and as the database begins to evolve.

Producing a model of the physical database structure is not often referred to in most data modeling texts, but is a very useful exercise. The physical database structure visually describes the organization and relationships of the tables as implemented in the DBMS.

The physical database structure is produced from the logical data model. It represents the underlying subject areas or sub-models that exist in all data models.

The physical database structure also illustrates the primary keys and primary key structure of each database table.

The physical database structure is an hierarchical model that shows the physical placement of the tables in the DBMS and implies the structure of their primary keys.

Rule: In the structure model, the key of the parent is in the child. It will take this form:

Primary Key = ParentKey-ChildKey

Primary Key = GrandParentKey-ParentKey-ChildKey

Primary Key = GreatGrandParentKey-GrandParentKey-ParentKey-ChildKey