When developing an app, you must create an Entity-Relationship model for your data. The most common relationship is a one-to-many or parent-child relationship, where one register of one table (for instance a department) is related to an undetermined number of registers of another table (for instance, employees). Creating those relationships is not straightforward in Remedy and best practices are not enforced.

In this post I will review how to create those relationships in BMC Remedy with a guided example.

Database Normalization

If you have attended a database design course or your are an experienced programmer, you surely know how important is to normalize your database in order to avoid redundancy an lot of related problems. You will also know that at some situations, the normalization is not the best solution, and at some cases the performance of not normalized databases is higher that its normalized counterpart.

But, If you are novice to database design, you are probably wondering: What is normalization? Normalization is about structure your data in some way that no redundancy is found and the database fingerprint is minimized. I don’t want to enter in a theoretical class but I can give you a little example. We have departments and employees. Each one is identified by an ID number, so the sales department is DEP000000000002 and John Doe is EMP000000000013.  Each employee belong to one department. And at each department there is an undetermined number of employees. This is what a database designer calls a one-to-many relationship. The way to store it in a normalized way is to store the ID of the related department in the employee register. But only this ID, no other information is saved at the employees table. That means that if we want a list of employees and its department name we must made a search at both tables (a join select).

Remedy joins aren’t flexible, and if you can avoid using them, then your develop work will be lesser. So I do not recommend to normalize your data. That is to store not only the department id at the employees table (or form), but also the most important fields of the department (like the name).

Modelling the data of the relationship

To create a one-to-many relationship we have the next options:

Looking at BMC applications I can found examples of all three options. Maybe the first is the most uncommon. But we can find a lot of examples of the other two. For instance, categories are saved at the incidents without the ID (third option). And the assigned group is saved with the group ID and support group information (name, organization, tier, …).

My recommendation is to choose the second one since you are still following the database design best practices of using the primary key of the one side to create the foreign key at the many side; but you still adapt it to the Remedy style.

The example

Let’s use the classic example. We have to forms:

Let’s create the forms.

Creating the relationship

To create the relationship we must add the next fields to the AAA:Employees form:

So let’s add these fields to the AAA:Employees form.

Add a menu to select the department ID

We create a search menu and add it to the department ID field at the AAA:Employees form. This menu will list all department names, but the data inserted in the field will be the department ID. This menu will be called AAA:EMP:DepartmentList

Now, we add this menu to the department Id field at the employees form.

Now the behavior is that we can select the department by its name, and the department ID will be populated.

Retrieve the department name and location from the server

The next step is to populate the department name and location from the department ID when the user selects a value from the menu. To do it we need to create an active link AAA:EMP:DepartmentNameLocation:

Ensure a correct Business Layer

If this field is populated from a filter, the API or a web service, the active link won’t be fired, so the name and location are not assured. We must create a filter that checks these values (or retrieves them) if the ID is changed. Same action as the active link, and fired when the ID is changed.

Comments at this point

Well, we have created the relationship. We can select a department from the menu, populating the ID and automatically retrieving the name and location from the departments form. At this point we must consolidate a concept: The important data is the department ID. That means that the department name and location are placed only for two objectives:

But for any reference purpose, the valid value is the department ID. For instance, if we want to add a table at the departments form that shows all the employees at this department the qualification must be based on the department ID, not the department name.

Improving the employee form and making it a little more user friendly

Normally the user doesn’t care about the ID. So we can hide this field. But the menu is based on this field. One solution to hide the field is to create another menu on the department name. But this is not a good practice. The menu must deliver the department ID. What can we do?

We can use this trick i’ve seen sometimes from BMC’s developments:

The result is that the department ID field will be hidden behind the department name. The user experience will be that the menu (that is actually attached to the department ID field) is attached to the department name field.

On update cascade, on delete cascade

The last thing to do is to consider, what to do when:

BMC’s developments normally doesn’t care about these actions. But I think that this is a bad practice and compromises data integrity. We must create workflow rules, in the form of filters that deny undesired actions (for instance deleting a department with employees), propagate the action (change the department name at the related employees, deletes the related employees) or take some action (erases the department information at employees registers).

The best way to propagate it is to create an action field (a display only field to allow determining an action to a form) that says “REVIEW RELATIONS”, and perform this action on all related children.