[Best Practices] Relationships

by David Workman
Data Mosaic

Warning: this is a brain dump, altogether too long to absorb easily, not well edited and definitely lacking in examples. However, I am treating this as a work in progress and feel that it is better to get this out in rough form and let the Servoy community post comments to fill in the gaps than to let it sit indefinitely on my to do list to polish off. There are definitely other developers who can explain some of the sections better than me anyway. So feel free to post with your relationship tips and questions and we'll see what the final result is down the line. Here goes....

Index

(1) What a relationship is
(2) Defining a relationship
(3) Relationships across different server names
(4) Relationships with global parameters
(5) Self relationships
(6) Relationships to filter lists
(7) Relationships for value lists
(8) Relationships with lots of parameters
(9) Using relationships in calculations
(10) Using relationships in methods
(11) "Piping" data with relationships
(12) Many-to-many relationships
(13) Performance and showing many related forms at the same time
(14) Date fields as parameters
(15) Going to related record

(1) What a relationship is

A relationship (or relation) is a way of connecting data in one table with data in another table. A relation is defined by one or more matching rules varying from a basic one-to-many relation to complex relations with multiple matches. A match is defined by a field in table A "matching" a field in table B via an operator. The most common operator is an equals sign.

Once defined, you can use a relationship to display related data via a related field, a portal, or a related tab panel. The first is useful for seeing data from a "parent" record, and the others are useful for looking at groups of related "child" records.

A relationship is also a named object which you can access programmatically in the method editor. In this fashion it represents a foundset and can be manipulated in the same way foundsets are. Most functions that use the foundset as a parameter can use the name of a relationship instead of "foundset".

(2) Defining a relationship

A relationship needs at least one column from a table on the right and left sides to be matched via an operator. Valid operators or =, >, >=, <, <=, !=, and LIKE. You can match to the same table or to different tables. You must however match to a stored column on the right side. Which means you can only use global columns on the left side of the relationship. If you are connecting to a calculation on the right side that calculation needs to be stored (create a column with the same name as the calculation).

Most database flavors allow for the ability to create relationships at the database level. If this is the case, Servoy can create relationships in your solution based on your back-end foreign key constraints. If what I just said is greek to you, ignore the "create" button at the top of the relations dialog box! This is a case where you need to have a bit of back-end database familiarity before you venture into this territory.

Note that if there are back-end constraints in the tables you are using for a Servoy solution, these constraints are not bypassed by Servoy even if they are not represented in your solution as relationships. So if one of the constraints has a delete rule this will trigger and delete related records when you delete a parent record in Servoy and it won't be something you can detect from within your solution. Know what you are doing if you have back-end constraints!

(3) Relationships across different server names

You can define relationships that go from a table in one Server connection to a table in another Server connection.

GOTCHA: Servoy does not automatically data broadcast in this case.

What this means is that if you create, modify or delete a record via this relationship, a form that has records showing from the table and server connection on the right side of the relationship will not be updated when changes are made from the left side of the relationship.

You tend to fall into this trap when you have multiple server connections pointing to the same database. As a general rule, do not have multiple server connection names for the same database! There are no advantages, only pitfalls when you do that.

If you must use this kind of relationship, generally only use it for viewing sets of data. Showing a list of transactions from an accounting system database on a client record in a Servoy controlled database is a good example.

(4) Relationships with global parameters

This type of relationship has one or more global fields on the left side of the relationship. These are extremely useful relationships for viewing sets of data based on user choice as you can change the values on the left side of the relationship without changing what record you are on.

Again, this type of relationship is best used for only viewing records. Why is this? Well it's time for the....

GOTCHA: calculations and summaries using integer and number type fields are not reliably updated when records are created or deleted via global relationships.

Just trust me on this one. If you want all your calcs and summaries to show correct values, do not create or delete records through global relationships.

(5) Self relationships

A self relationship is when you match two columns from the same table. Matching a global variable to a column is considered a global relationship even though you can have the same table selected for both sides.

Generally this relationship is used as a view filter like global relationships or to set up value lists. If you are using a lot of self-relationships, it can also be a sign that your data structure is poorly normalized.

There is one unique case where a primary to primary key relationship is sometimes needed. Use it to enforce viewing of the same record in a tab panel. Most times this is not needed as forms based on the same database connection and table share the same foundset. But there are subtle ways to break this connection (or have been in past versions of Servoy) -- none of which are pleasant to track down -- hence this brute force method of getting what you expect at all times.

(6) Relationships to filter lists

As mentioned in an earlier section, it is a common technique to use global relationships to set up dynamically filtered lists. As the value in the global variable changes, the list of records displayed changes based on the relationship match.

This is a correct and fine technique as far as it goes but keep in mind that it is limited. Let's say you have the following value list in your global combobox:

Red
Orange
Green

To show all records in the list the global needs to be blank. Which means you need a button next to the global combobox that clears the global -- you can't add the value "blank" or "show all" to the value list which would be more user friendly.

Better yet, what if you wanted to show the record count next to each value in the value list?

Show All (345)
------------
Red (255)
Orange (79)
Green (11)

Servoy allows you to fire methods when particular events happen. You can also assign values to value lists in a method. So to do the above example, when the form is shown calculate the counts for each category, create an array with each item you want in your value list, and assign the array to the value list.

Then when you change the value of the global field that has the value list, detect what value the user selected and find and display the set of records that corresponds to the selection.

For this example, the displayed list is relationless and you as the programmer manage what records are displayed based on events. If you are coming from a non-event driven development environment, this may be a new concept for you! But as you can see, it is a much more powerful and flexible technique than using a relationship to control filtered lists.

(7) Relationships for value lists

Value lists can be defined many ways in Servoy. The values can be hard coded, pulled from table columns, pulled from related table columns, pulled from related-related table columns, and even set programatically at runtime. This is a lot of options and leads all new developers through an extended phase of experimentation to find the best way to handle value lists. It usually doesn't take long to figure out that hard coding value lists leads to a maintenance nightmare.

A common technique for managing all of your value lists in a solution is to have a value list table where you store all of your value list items. At its most basic, you need a column for the value list item and a column for the value list name. Each value list in the table needs a global field set to auto-fill with the name of the value list. Then create a relationship from the global to your table and create a value list based on this relationship.

You can expand this concept many ways. You can have another column and relationship to define related value lists (if value x1 is in field a, show values 1, 2 & 3 in field y...if value x2 is in field a, show values 4, 5 and 6 in field y...etc). Add a user interface so your users can modify the value lists themselves. Finally, bundle this whole functionality into a module and use in all of your solutions!

(8) Relationships with lots of parameters

I just counted...Servoy allows you to have up to 11 parameter pairs in a relationship. Give me a solution that has relationships with this many parameters and I'll show you three better ways to code that solution. In other words, just because you can do something in Servoy doesn't mean you should!

Let's take a moment to ponder the mechanics of what is going on when Servoy displays a form. Any data fields on that form need to be filled with data. Servoy creates a SQL query for you based on what table the form is based on, what fields you have placed on the form and what relationships these fields are based on. If you are like me you like Servoy because you don't want to have to do all that work yourself. Probably for the exact same reason as me--you aren't a SQL guru wizard!

Ignoring all else SQL related, you should know one thing -- certain SQL queries take longer to figure out at the database end of things than other queries. Generally the longer it is, the more time it takes to figure out and send data back to the client. What makes a SQL query longer? Lots of parameters in the relationship. My rule of thumb is that any relationships with more than three parameter pairs needs to be looked at seriously and justified.

What is your alternative to lengthy relationships? Write the query for the data you want to see, put it in a method and fire it at the specific event you want to return that data. This way, you control exactly when potentially long queries happen. You can even give user feedback to tell them the solution is chugging away at something this way.

(9) Using relationships in calculations

GOTCHA: In calculations you can't assume that there is data on the other side of a relationship. If there isn't, calculations will throw an error which you can see when you dbl-click the status area in Servoy Developer (bottom left border). So always check if a relationship has records in it before doing something:

//check if records in relationship
if (utils.hasRecords(fncl_client_to_office)) {
    //relationship has records, now do something
    return fncl_client_to_office.name_office
}

UPDATE 2007-01-09: Calculation errors of this type can cause errors when you export a solution.

(10) Using relationships in methods

1- Just like in calculations, be aware that a relationship may not have any records. You don't get an error if there isn't, but a null value is returned if you refer to a related field when there are no records there.

2- The "foundset" object is a special Servoy data structure that represents the current found set of a form. A relationship name is also a foundset object. For example, many functions take the word "foundset" as a parameter. In most of these functions, you can replace the current form's "foundset" with the name of a relationship to use the found set represented through the relationship.

3- You can "chain" relationships together to refer to data multiple tables away. For example, let's assume three tables (clients, orders, order_items), two one-to-many relationships to connect them structurally (clients_to_orders & orders_to_order_items) and two non-structural relationships to view data from the bottom back to the top (order_items_to_orders & orders_to_clients).

To refer to the "order_date" field from the table "orders" in a method at the "order_items" table level you would put the name of the relationship followed by the name of the field (order_items_to_orders.order_date). This is indeed what you get when you dbl-click on the the order_date field under the order_items_to_orders relationship node.

To refer to the "client_name" field in the table "clients" in a method at the "order_items" table level you can do this by specifying both relationships in order: "order_items_to_orders.orders_to_clients.client_name". This relationship object hierarchy is not obvious by looking at the method editor nodes. Notice that you don't need to specify the form names for each relationship either -- you as the programmer just need to know what relationships can chain together to get from one table to another table multiple relationships away.

(11) "Piping" data with relationships

Showing data more than one relationship away on a form is a common challenge. The standard way of doing this is to create a calculation that returns a value from a table one relationship away and then refer to this field from the table two relationships away.

I am not a big fan of this method because it can be a lot of work and just clutters up the calculation list. Considering that it isn't very good practice to make data editable this far away from "home base" the main reason for grabbing data from far away is to display in a non-editable fashion on your form.

What I do instead is create a label object, turn on the displayTags property and then refer to a field using the relationship chain technique referred to in the previous section. To use the same example, place %%order_items_to_orders.orders_to_clients.client_name%%" in the text property of the label and you see the client_name field from the clients table on a form based on the order_items table.

(12) Many-to-many relationships

Refer to Jan Aleman's article here

(13) Performance and showing many related forms at the same time

This is another situation where you need to resist doing something just because you can. With the tab panel object it is easy to go nuts putting many related forms on one form. The more you show, the more SQL queries Servoy has to generate when the parent form is shown. (Note that only the visible tab of a tab panel generates a SQL query so you can safely have many tabs in one tab panel.)

You can also generate a sizable performance hit if you show a related form within a related form within a related form (etc) using nested tab panels. My rule of thumb is to limit tab panel nesting to three deep if they are related tab panels. If they are relationless tab panels, I consider four or five levels deep to be acceptable.

(14) Date fields as parameters

Date fields are perfectly fine as parameters in relationships. Just keep in mind that many date types in SQL databases have a time component stored even if your date field mask doesn't show the time component. The time component can make matching dates problematic as two seemingly equivalent date values will likely not be equivalent once you take the time component into account. If you want to just match on dates, use a date type that doesn't have a time component or create calculation fields that strip out the time component and match on the calculations.

(15) Going to related record

A common task is to go to a detail view of a related record that you are showing as a list. When you click on the record in the related list(onRecordSelection event) run a method that:

• captures the primary key
• loads the record in a detail view form -- forms.form_name.loadRecords(primary key)
  
• shows the detail form -- forms.form_name.Show()

(You can combine steps 2 & 3 by using showRecords() function.)

There is a side effect to this technique and that is that the foundset of the target form is not preserved. If you want to preserve the foundset of the target form, you need to instead set the record index of the target form to the record index of the record you want to go to. If the source form and the target form share the same found set, you can simply show the target form and you will be on the record you clicked in the source form.

If both source and destination forms do not share the same found sets, the record index for the record will most likely not be the same in both forms. In this case you will need to capture the primary key of the source form and loop through the target form's foundset until you find the record and then set the target form's index to the loop count. Some sample code here:

//arguments[0] is the source form primary key
var userID = arguments[0]

//loop through target form's foundset until you find a primary key match
for ( var i = 0 ; i < foundset.getSize() ; i++ ) {
    var record = foundset.getRecord(i + 1)
    if (record.id_employee == userID) {
        foundset.setSelectedIndex(i + 1)
        return
    }
}