Saturday, February 25, 2012
Denormalization Field Map

A normalized database allows for efficient and economical data storage and retrieval. Business applications require displaying denormalized information to the users. The structure of the database is hidden from the user. End users can only relate to the business entities that they are accustomed to in the real world.

For example, consider the Order Details table in the Northwind sample database. The diagram below shows the normalized database schema. Normalization increases the performance and decrease the footprint of database records. On the other hand, this means that related data is stored in multiple tables, increasing the complexity of data presentation.

Code On Time automatically performs denormalization. It inserts several reference fields from master tables into each business object presented to the user. For example, in the “Reference Information" category on the Order Details screen, it includes Order Customer Company Name, Order Employee Last Name, and etc. These fields are not explicitly available in the Order Details table according to the database diagram.

DenormalizationProcess

The application generator detected direct and indirect foreign key relationships between Order Details and the rest of the tables in the above diagram, and has included reference fields from the linked master tables up to 3 levels. Only one master field is borrowed for inclusion into Order Details for each relationship. Usually, these fields are found either as 1) aliases for foreign key lookups of direct outgoing master relationships, such as Product Name or 2) read-only fields placed in the Reference Information category, such Order Ship Via Company Name.

image

Suppose we need to add the parent Employee’s FirstName and HomePhone to the Order Details forms.

Denormalization

Start the web application generator, click on the project name, and choose the Business Logic Layer option.

image

Switch to the Denormalization Field Map section, and enter the following field map:

dbo.Orders => dbo.Employees
FirstName
HomePhone

image

Press Finish to save your changes.

Click on the Refresh button. Select the Orders controller to ensure that it will reflect the new denormalization field map, and press Refresh.

image

Finally, press the Generate button.

When the website comes up, navigate to the Orders page. You will see that the fields have been added to the Reference Information category of this screen. Now you can find out the first name of the employee made the order and call them without having to look them up. All other pages that use the Orders data controller will also reflect these changes.

image

However, if you check the Order Details screen, the new reference fields are not there. You need to Refresh every relevant data controller in order for them to include the fields referenced in the Denormalization Field Map.

Click on the project name again, and press Refresh. Select the OrderDetails controller, and press Refresh.

image

When the website comes up again, you can see that the new reference fields FirstName and HomePhone have been added under Reference Information on the Order Details page.

image