Contents Index Step 3:  Normalize the data Step 5:  Verify the design

ASA SQL User's Guide
  Designing Your Database
    The design process

Step 4:  Resolve the relationships


When you finish the normalization process, your design is almost complete. All you need to do is to generate the physical data model that corresponds to your conceptual data model. This process is also known as resolving the relationships, because a large portion of the task involves converting the relationships in the conceptual model into the corresponding tables and foreign-key relationships.

Whereas the conceptual model is largely independent of implementation details, the physical data model is tightly bound to the table structure and options available in a particular database application. In this case, that application is Adaptive Server Anywhere.

Resolving relationships that do not carry data 

In order to implement relationships that do not carry data, you define foreign keys. A foreign key is a column or set of columns that contains primary key values from another table. The foreign key allows you to access data from more than one table at one time.

A database design tool such as the DataArchitect component of Sybase PowerDesigner can generate the physical data model for you. However, if you're doing it yourself there are some basic rules that help you decide where to put the keys.

Resolving relationships that carry data 

Some of your relationships may carry data. This situation often occurs in many-to-many relationships.

Between the Parts entity and Warehouse entity is the Inventory entity, which contains the Quantity column.

If this is the case, each entity resolves to a table. Each role becomes a foreign key that points to another table.

The foreign key relationship between the Parts table and Inventory table is Part Number = Part Number. The foreign key relationship between the Inventory table and Warehouse table is Warehouse ID = Warehouse ID.

The Inventory entity borrows its identifiers from the Parts and Warehouse tables, because it depends on both of them. Once resolved, these borrowed identifiers form the primary key of the Inventory table.

Tip 
A conceptual data model simplifies the design process because it hides a lot of details. For example, a many-to-many relationship always generates an extra table and two foreign key references. In a conceptual data model, you can usually denote all of this structure with a single connection.

Contents Index Step 3:  Normalize the data Step 5:  Verify the design