Let’s return to what David Remsen says about one-to-many data relationships: “When deciding upon how to best split data into tables we often ask "What are the logical units of the data?" In other words, what is best represented by a single row of data. It is often convenient to think of physical representations when applicable. For example, what types of information can we associate with a patient that only appears once. A person usually has one name and social security number so these can be safely placed in a table. We might add date of birth. [On the other hand] a person can have many tests. They might only have one but they might also have fifty. Thus one person might have many tests and this is represented by a One-To-Many relationship…”
As we stated previously, a one-to-many data relationship exists when one record from one data source is related to several records from another data source; a single country in a Country data source will be linked to many entries within a City data source. This much is clear and well known to developers and users alike.
With the uniPaaS application platform, development of an application that utilizes data that is in a one-to-many relationship is implemented by the interrelationship of two tasks or programs. In uniPaaS, a program is not an all encompassing application, but rather a very small but discrete task. Most developers in uniPaaS lean towards fine-grained programs because of their reusability.
The logic is very straightforward. The first task or parent task deals with the main record and the second task (sometimes call a child or subtask) deals with the "many" or multiple records that are related to the main record. This relationship is often displayed to the end-user using two different display formats, the parent program displaying the single record in screen mode and the child program displaying the multiple records in a table. This is not always the case, since you may decide that the parent program will also be a table.
uniPaaS uses a Subform control to display the child task's form within the parent task's form and refreshes the subtask each time the common variable is changed in the parent task.
A one-to-many data relationship is established between a primary data source and a secondary data source in such a way that each record in the primary data source has several related records in the secondary data source.
In a one-to-many relationship, there is a primary data source (the one) and a secondary data source (the many). The primary data source must contain a unique identifier or index to which the secondary data source can associate. The secondary data source will contain both the unique identifier of the primary data source (the reference index) and its own unique identifier (the secondary index).
This can best be illustrated through an example such as order numbers and order lines in an order entry program.
Primary Data Source
In the following example, the Orders data source is the primary data source.
An order record usually contains basic details regarding the order, for example:
The Order Number will be the Unique index of the data source, since each record has a unique order number.
Secondary Data Source
The Order Lines data source is the secondary data source. It contains several data items:
The Order Number and the Order Line will be the Unique index of the data source. The same order number can repeat several times with different order line numbers. The linkage between the Orders data source and the Order Lines data source is the Order Number.
For each record in the Orders data source there are several records in the Order Lines data source. This is the exact definition of a one-to-many data relationship.
Without the use of one-to-many data sources, all information would need to be contained in one record. This will cause problems, such as the end-user having to repeatedly type the same information, and large records because of the repeated data.
The result would be a large data source, which takes up more disk space and slows down the program's performance.
The solution to this problem is to define two data sources according to the following rules:
Separating data into two data sources like we did in the Orders and Order Lines example has several advantages.
The separation saves the end-user from having to type the same data several times and it also reduces the size of the records.
Step-by-step procedures on the creation of a program that utilizes one-to-many relationships are available in Module 18 of the Getting Started with uniPaaS course. For a description of what we mean by a one-to-many application platform, see our previous entry.