Natural vs. Relational Linking

Subforms offer two different types of linking: Natural and Relational. Natural linking is the default. While natural linking has the advantage being simple and fast, it is also inflexible. Once a record is set as a child of another record, it cannot be moved to be a child of a different parent record. The parent record can delete its children, or add more children, but it cannot share a child with another parent record. This can lead to data normalization problems. To remedy this, Sesame also provides relational linking.

Relational linking retains the same parent/child relationship as natural linking. The difference is in the method by which any particular parent record recognizes its child records. Where natural linking has a direct pointer from the parent record to the child record, relational linking provides the addition of Key Field set. This key field set stores the name of a field in the parent and the name of a field in the child. For any of the records that are of the correct type to be considered a child of a particular parent record, the data in the parent's key field and the child key field must match. Relational linking is even more flexible than a simple match between two fields. The parent's key field acts as "Retrieve Spec" and can use the same syntax as the retrieve form uses for that field type.

A simple way to understand the basic difference between relational and natural linking is to imagine two families: the Nevilles and the Orvilles. The Nevilles all hold each others hands. The Neville parents recognize their own children because they are holding the eldest child's hand. The eldest child holds the next oldest child's hand, on down the line of children to the youngest. The Nevilles are a naturally bound family. The Orvilles are relational. The parent wears a tee shirt that says "Orville" on the front. The children each have tee shirts that say "Orville" on the back. The parent knows the children by matching the names on the tee shirts.

Deciding whether to use natural or relational linking is simpler than understanding either. Just ask yourself if the data in the subform you are designing will repeat, unaltered, under more than one parent record. If the data will repeat, then it is wise to use relational linking, otherwise, or if in doubt, use natural linking. If the data must be unique to each parent, as in the example of logged transactions, natural linking is the natural choice.

For more information, see Appendix 4 - Advanced Concepts in the Sesame User Guide and the Knowledgebase article: "Should I choose Natural or Relational Subforms?".