Relationship Designer

The Relationship Designer allows you to visually establish relationships between the tables within your project's default database. The Relationship designer will automatically handle whatever modifications to your underlying database that might be required to support a relationship you create within it.

The Relationship Designer can be accessed from the View tab of the Ribbon.

Figure1: Relationship Designer

The Relationship designer enables you to view and specify relationships between tables in a project's associated database.

Figure 2: List of tables to be added to the Relationships View..

From the View Tab of the Ribbon you can choose the Relationships view which leads to a blank slate, if relationships have not been created in the application. Tables can be added to the Relationships View by right clicking the background and selecting "Show Table". This brings up a dialog with the list of tables in the current project as can be seen in Figure 2.

Figure 3: All tables from the CMS project just added to the Relationships View.

Once all the project’s tables have been added to the Relationships View the actual relationships can be created. In order to create a relationship between two tables the user should drag the primary key of a table over the corresponding (foreign key) field in the related table.

In the case at hand there are two relationships to be defined. The first is between tables Section and Article while the second is between Article and UserCatalog. This is because an article will be published under a section of the website and created by one of the site’s users.

Once the drag and drop operation is completed, a dialog box will popup asking for the specific details of the relationship that is being created. This dialog is shown in Figure 4.

Figure 4: Edit Relationships dialog showing a relationship between tables UserCatalog and Article.

At this moment the user can specify if referential integrity will be automatically enforced by the underlying Firebird database server.

As defined in Figure 4 the relationship between tables UserCatalog and Article will cascade any changes. What this means is that if a User has his Id changed in the UserCatalog table, all corresponding articles will get updated to reflect the new Id. This avoids the possibility of ending up with inconsistent data in the database. The cascade delete option means that if any user is deleted from the UserCatalog table, all articles created by that user will also get deleted.

Note: In this case, in the interest of simplicity of design the Cascade Delete option is being turned on. Generally speaking this is not a good idea for a production system.
A strong argument can be made that if someone accidentally deletes a record you do not wish to run the risk of having tons of other records deleted, in other tables.

In Figure 5 you can see the complete relationship map for the tables in the MorfikCMS database, with the two relationships created.

Figure 5: Relationships diagram for the tables in the CMS project.

Related Topics