DDH Software, Inc Making a big world smaller






Return to Knowledgebase Home
Introduction to relationships and the use of the Relationship field type.
Product: General HanDBase TopicArticle Number: 221Creation Date: 04/15/2003

The Relationship field type creates a relationship between two HanDBase databases, using the concept of a key as the link between the two databases. This provides for a more flexible relationship than using a combination of a Link and Linked field.

This tutorial has an accompanying applet in the gallery on our website, called "Relationship Linking Example". Click Here to find this in the Gallery. You are encouraged to download this and install it to your handheld so that you can see the fields and records described in this example.

To best understand the Relationship field type, you should have a grasp of how a key works in a relationship. Every citizen of the U.S. has a social security number, which is used to uniquely identify that individual. There may be 10,000 people with the name John Smith, but each John Smith will have a unique social security number. If you were to look at a sample of 3 John Smiths, their data in a database would look like this:

FirstLast SSN
John Smith 01234567890
John Smith 11234567890
John Smith 21234567890

In this example, the SSN field (social security number -- the values of which are intentionally too long to be real social security numbers) would be the key field for that database. The SSN uniquely identifies each person (or record) in that database.

If you had a second database of tax payments, you might see something like this:

SSN Date Amount
01234567890 4/15 450.00
01234567890 7/15 450.00
01234567890 10/15 450.00

You can tell at a glance that the same individual (the first John Smith) belongs to each of those payments. The first database has one record identifying who John Smith with the SSN of 01234567890 is. The second database consists of a list of other records, some of which belong to that John Smith. You can tell those records belong to that John Smith because his key (01234567890) is in those records.

A database of tax payments is likely to have millions and millions of records. Searching manually for each record that belongs to the John Smith with the key of 01234567890 would be impossible. But, a computer can scan the database for any records that have 0123456789 in the SSN field, and show only the records that have a match in that field.

This is called a one-to-many relationship. The first database has a single record for each individual, and includes a field that uniquely identifies that individual. You can't use the last name of the individual, because even less common last names than smith would have many different matches. For an effective relational database (a database that has a relationship with another database), each record should have a key field to help identify it. That's what the social security number is: a key field.

Technically speaking, the social security number in the database of payments is a foreign key. You don't have to know that term to use a Relationship in HanDbase. You can look at is as a field used to help match the individual records (for example, in the database of people) to records from another database that belong to that individual.

It is not necessary for the key field to be something meaningful like a social security number. The key could be just a number that keeps getting larger as each record is added. But, it helps to have a meaningful key field, just because it is easier for the human eye to tell at a glance that records belong to another.

With a basic understanding of a key field in a relationship, let's look now at the Relationship field in HanDBase.

A Relationship field is somewhat unique in HanDBase. Unlike most other fields, the Relationship field doesn't actually contain a value. The Relationship field defines a relationship between two databases. This will often be used in the form of a one-to-many relationship, as described with people and tax payments. To make things more clear, let's look at the tax payment database, and add more records:

SSN Date Amount
01234567890 4/15 450.00
11234567890 4/15 2200.00
21234567890 4/15 900.00
01234567890 7/15 450.00
11234567890 7/15 2200.00
21234567890 7/15 900.00
01234567890 10/15 450.00
11234567890 10/15 2200.00
21234567890 10/15 900.00

If this were a HanDBase database, the SSN field would be a Text field type, Date would be a Date field type, and Amount would be a Float field type. Now we have records that belong to each of the three John Smiths in our example. You can tell, because you see at a glance that there are entries for each of the three different SSN values in the John Smith database. For the sake of the example, let's say the name of the database is "Tax Payments".

Now, let's see how to create a relationship between the two databases (Tax Payments, and People). For each person, there is only one record in the People database. You don't need or want more than one John Smith with a SSN of 11234567890. If there were more than one, how would we (or the IRS) know which John Smith of SSN 11234567890 made a payment for this year, and which one didn't. With the understanding that the People database contains one record for each person, and the further understanding that each person has more than one record in the Tax Payments database, you can see which database is the "one" and which is the "many" in the one-to-many relationship. The People database is the "one" side of the relationship.

So, we can add a field of the type Relationship to the People database. Just to give the field a meaningful name, the Relationship field will be called Payments, because this will relate the People database to the Tax Payments database. Please note that no fields are being changed to a Relationship field. The Relationship field is an additional field, separate from the fields with actual data.

For the Payments field, you need to look at the properties and fill them out. The necessary properties are the following:

Related Field in this DB: SSN

This is easy. Since we have looked at both sets of data, we know that the SSN is the key field to use. This is the field that uniquely identifies each person record in the People database. For the example, SSN is that unique field. If this were a more simple time and every household had just one phone number, a phone number could be a unique field. Or, a field that has the full name of a person, such as "John Smith", could be used, as long as there are not more than one "John Smith"s in the database. The important thing to remember is that "Related Field in this DB" has to be a field that has a unique value for every field in the database.

Other DB Name: Tax Payments

This is also easy. We know we are relating the People database to "Tax Payments", so you only need to select the "Tax Payments" database. This will always be the name of the database that your Relationship field works with.

Other Field Name: SSN

If you have already looked at the data in both databases, this one is also easy. This is the field in the "Other" database that uses the key field. We are relating tax payments to people through the social security number. In this example, both databases have the same exact name for the field that contains that data. With planning, the values for "Related Field in this DB" and "Other Field Name" can be identical, which makes the set up of the Relationship field much easier

The other properties can be left as they are, to give you a People database like this:

First Last SSN Payments
John Smith 01234567890 *
John Smith 11234567890 *
John Smith 21234567890 *

With this in place, you can open the record for SSN 0123456789, and then tap the Payments Relationship field to see just these records:

SSN Date Amount
01234567890 4/15 450.00
01234567890 7/15 450.00
01234567890 10/15 450.00

The Relationship field will open the related database ("Tax Payments") and display only the records that have a matching value in the key field (the field specified in "Other Field Name"). This is a little bit like a filter for showing only the records in "Tax Payments" that relate to a specific person in the People database.

It is important for the "key" used in both databases to be an exact match. For example, a record in the "Tax Payments" database that has an extra space before or after the SSN number will not match with any records in the People database. The full value of the field chosen as the key (the field used in "Related Field in this DB" and the field used in "Other Field Name") must match. Any differences in spelling, punctuation, spacing, or capitalization will result in no matches.

The Relationship field makes data entry very easy, avoiding typographical mistakes. If you installed the example for this tutorial (the Relationship Tutorial Example), you can open the People database now to see how this works.

With the People database open, you can open a record, such as the first John Smith with SSN 01234567890. Then, use "Payments" to see the records in "Tax Payments" relating to this John Smith with SSN 01234567890. Now for the good part. Use the New Record icon to open a new record. This will automatically fill in the key field SSN with the value that will make this new record relate to this John Smith record with the key value 01234567890. Now, when the record is saved, it will automatically be related to the correct People record. This has the benefit of both saving time (no need to enter the key value into the new record) and eliminating the chance of mis-typing the key value.

If you open the "Tax Payments" database directly to create a new record, you do have to make sure you are entering the SSN value correctly without any misspellings of any kind. We encourage users to create new records in a related database like this by first opening the database with the unique records (the "one" database in the one-to-many relationship, or the "People" database in this example), then following the relation, and then creating the new records from there.

Optional properties

There are other options available in the Relationship field, which are not necessary to fill in, but can help in viewing the relationship.

The "Show from Other DB" option allows you to select a field in the database selected in the "Other DB Name" option. This can be the key field (the SSN field, in our example), or this can be a different value, such as the Date. This is easier to understand when looking at a sample.

If you have the Relationship Linking Example on your handheld or desktop, open the People database, and then open the record for the first John Smith, with SSN 01234567890. You seee the following data:

First : John
Last : Smith
SSN : 01234567890
Payments : 10/15/03

Where did the value 10/15/03 come from? Use the Payments link (tap the word Payments or click the button on the desktop) to see the related records for this John Smith, which are the following:

SSN Date Amount
01234567890 4/15 450.00
01234567890 7/15 450.00
01234567890 10/15 450.00

10/15/03 is the value in the Date field in the last record associated with this John Smith.

To see why, return to the People database and look at the properties for the Payments Relationship field. Date is chosen for the "Show from Other DB" option, and Last is the selected option for "From Which Record". This coincides with the value that is listed next to the Payments field in this John Smith's record. The value of the Date field from the last record for that person is shown.

If you change the "Show from Other DB" to SSN instead of Date, you will see the following in that John Smith's record:

First : John
Last : Smith
SSN : 01234567890
Payments : 01234567890

Now the value from the SSN field is shown, which really doesn't add any value, since the SSN is already in this record. Go back to the properties for the Payments Relationship field and change the "Show from Other DB" to Amount. The John Smith record will then look like this:

First : John
Last : Smith
SSN : 01234567890
Payments : 450.00

This shows more relevant information, because it does not repeat data that is already in the record.

The "From Which Record" option is used in conjunction with the "Show from Other DB" option. If you don't have a field selected in the "Show from Other DB" option, then it won't matter if you have First or Last selected. But, if you do have a field selected in the "Show from Other DB" option, First or Last will show the value of the selected for from either the first or the last record that matches the key value.

Go back to the properties for the Payments Relationship field and change the "Show from Other DB" back to Date. This returns the value in the John Smith record to show this:

First : John
Last : Smith
SSN : 01234567890
Payments : 10/15/03

To understand the difference between the first and last options, set "From Which Record" in the properties for the Payments Relationship field to "first" instead of "last". Open a John Smith record, and you should see this:

First : John
Last : Smith
SSN : 01234567890
Payments : 4/15/03

Tap or click Payments to see this John Smith's records again, and you will see that 4/15/03 is the value in the Date for the first record.

The "From Which Record" option is useful for specifying which record in the list of related records displays its data next to the Relationship button. The only options are the first or last records. But, as in this example, using the last record shows at a glance when the last payment was made. This is a shortcut to the related data, and may be all that you actually need to see from the related records.


This article has been viewed 1 times.

Return to Knowledgebase Home