Normalization and Denormalization

Normalization is used to model a redundancy-free database requiring a minimum of memory. Database tables are merged and split to appropriately denormalize a normalizied database.

Introduction

Normalization is used to model a redundancy-free database requiring a minimum of memory. By creating a conceptual model in an entity relationship model, it is simple to arrive at the 3rd normal form if you avoid transitive or direct dependencies of attributes in an entity. The 3rd normal form is the practical standard in real projects.

A normalized ER model is transferred to a structurally equal database model via an initial mapping step; this model thus represents a normalized database. In the process, the mapping links the corresponding elements (entity and table, attribute and table column) to dependencies.

If denormalization is required for any reason, you can implement this in the database model by merging and splitting tables into columns or rows. This can lead to attributes of an entity being mapped in multiple database tables. Similarly, a database table can form the database for multiple entities. The relationship between ER model and DB model is fully documented owing to the preservation of the mapping dependencies.

You can split tables which were merged for denormalization to return to the original tables. You must determine between two cases if more than two tables were merged into one. If a corresponding conceptual schema exists, then starting at the leading selected table, this is split into further tables from this schema step-by-step. The user can decide whether they want to select further tables to be split for standalone database schemata.

Denormalization in Database Diagrams

Denormalization is carried out in the database diagram in Innovator for Information Architects.

Merging Tables Using a Foreign Key to Give a Unique Key (Roll Up)

How to proceed

Select the foreign key edge that connects the tables to be merged.
Select Merge>To Unique Key>Count (1-64) in the context menu.

The columns of the foreign key table are brought into the table with the unique key (one set for each quantity of the command).

The remaining table contains an element of Table Added with Direction type with the Direction property 'to the primary key'.

The moved columns all receive the Null Property 'zero'.

The columns of the foreign key are merged to the respective columns of the referenced key.

The foreign key table is deleted.

Merging Tables Using a Foreign Key to Give a Foreign Key (Roll Down)

How to proceed

Select the foreign key edge that connects the tables to be merged.
Select Merge>To Foreign Key in the context menu.

The columns in the table with the referenced key are brought into the table with the foreign key.

The remaining table contains an element of type Table Added with Direction property 'to the foreign key'.

The moved columns retain their Null Property unless it was an optional foreign key. If tables are merged to an optional foreign key, then all added columns receive the Null Property 'zero'.

The tables of the referenced key are merged to the respective columns of the foreign key.

The unique key table is deleted.

Merging Sibling Tables (Merge)

How to proceed

Select two database tables that each have a foreign key for the same table marked with "Z".
Select Merge>Sibling Tables in the context menu.

The columns of the table selected second are brought into the table selected first.

The remaining table contains an element of type Table Added with Direction property 'to the sibling'.

All columns in both tables receive the Null Property 'zero'.

The primary keys are merged.

The table selected second is deleted.

Splitting a Table by Rows (Horizontal Split)

How to proceed

Select a database table.
Select Split>Rows in the context menu.

A copy of the table is created with identical columns and foreign key.

Foreign keys that previously referenced the table are each changed to two optional foreign keys; one references the old table and one the new.

Splitting a Table by Columns (Vertical Split)

How to proceed

Select a database table or multiple table columns in the section content for the table columns.
Select Split>Columns in the context menu.

A new table is created, into which the selected columns are moved. The new table also receives a copy of the primary key, the columns of which, at the same time, form a foreign key on the old table.

Remerging Tables which have been Split

Context

You can split database tables that have been merged again in the database diagram. To do this, carry out the directions explained for merging tables above in the other direction step-by-step.

If two tables were merged into one, then these tables can be split again using the Split>Table context menu command.
If more than two tables were merged into one, then a dialog appears for you to select the leading table from.
- If a corresponding conceptual schema exists, then starting at the leading selected table, this is split into further tables and assignment is made from this schema step-by-step. This is why no other tables appear for selection.
- The user can decide whether they want to select further tables for standalone database schemata. The selected tables are merged into a separate table with the leading table and this is linked with the split tables using a foreign key edge. Tables which are not selected remain in the selected table.

Note

The name of the added table is used for the new table by default when splitting a merged table. The name is derived from the linked entity if the tables are maintained using automatic mapping.

How to proceed

Select a merged database table. Merged database tables have the "have merged table" property.
Select Split>Table in the context menu.

If the table merged precisely one table, then this is demerged again.

If the table was merged from multiple tables, then the Split table '{0}' dialog appears.
Select the leading table from the Leading Table drop-down list.

If the database schema was merged from the conceptual schema, then assignment of further tables is merged from the conceptual schema.

Otherwise, the dialog also has the Further Tables list. Activate the check boxes for the tables which should be split into one table with the leading table. Tables which are not selected remain in the selected table.

Confirm the selection with OK.

The division is shown in the diagram.