Introduction
The current version of HanDBase has the capability of supporting relationships between databases based on arbitrary field comparisons.
This document will try to explain what is a relationship field, when it is best to use them, when not and will give two examples of typical relationship field using databases.
A good suggestion is that you follow along with your actual handheld device while reading this document. Once you've gone through the two examples, feel free to modify them for some other purpose. This practice will help you better understand the workings of the relationship field type.
Since HanDBase is available in different platforms and may contain slight differences between them, your steps needed in order to follow along the examples may vary from the ones described here.
What's a relationship?
Suppose you have two different tables (Fig. A), each table has data that at some point you will want to associate with the other database. One such example is when you have a database with all your Clients and another database with all the Purchase Orders from those clients. The usual way to go about this is to compare two fields, one from each database, this is done as a text comparison. If both fields are equal in content (A), then a value from a source database is transferred over the destination database (B).
 Figure A.
HanDBase also allows you to configure if you want to see the value from the First or Last records. Why? Imagine you have several records that match the criteria you set in the field comparison, which one to show? In that case you choose whether you show the first record on the database or the last record on the database. An example for each of the situations follows:
First: When comparing the start date of your records with another value on your database. Such as the first entry of a journal with the current one (how many days from when we began?).
Last: If tracking shipments to different clients. A record can always show the last shipment done. It can also apply for patient visits to a physician (when was the last visit?).
To Link or not to Link?
At this point you may see some similarities between the Link/Linked field types and the Relationship field type. Although they work by creating associations between databases, they are somewhat different. One may argue that it's technically possible to interchange between them for various situations, but it may not be the case at all times.
Link/Linked field type associations are very fast in HanDBase. They work by creating a one to many (Fig. B) links between a record of one database and many records of another database. The figure below shows a database with its set of records.
 Figure B.
We could go back to the same example as for a relationship that was mentioned before in this document, where a database could hold a list of Clients and another one could hold a list of their Purchase Orders.
The main differences between a Link/Linked association and a relationship association can be summarized in the following table:
| Link/Linked |
Relationship |
| Faster. Relationships rely on direct string comparison on a per record level, this slows down filtering. Links can be filtered out based on their unique link ID. |
Follows traditional methods of 'table' associations. This may be more pleasant for conservative SQL database users. |
| Generates a unique identifier for each association. This guarantees correct handling of links, even when synchronizing with other users. |
Easy to set-up. Since it doesn't require a field to be created and configured in the linked database. It works just by directly comparing the local field with the destination and pulling a value from the other table in case the condition matches. |
| True one to many records association. This may be desired in many situations, especially if there's the possibility that a relationship may match several records (we may have many Clients named John Doe). |
Many to many associations. Link/Linked associations have a parent (Link) and several child (Linked) databases; the way they are setup to work does not allow a child to become a master based on the same field. Relationships may go back and forth using the same base fields due to their one-sided condition generation, thus allowing many to many associations. |
Customers and Orders - A Relationship Field Example
We will now go through a step-by-step guided example on how to setup two databases that are associated with relationship fields. These are our goals:
- Create a Customers database where we can store basic information for each customer.
- Create an Orders database where we can store information about an order for a particular customer, date and product.
- Associate the Customer with every Order.
- Show the date for the last Order a Customer made.
The end result of this example is a simple, yet useful tracker for customer orders. Let's begin:
- Creating a Customers database
Open up HanDBase and Create a New database. You should be taken immediately to the General Settings dialog. Enter the name for the database: Customers. Close the General Settings dialog and move to the Edit Fields dialog. Create the following fields with their respective types, keep the default values for all (only name the field and change the type): Company Name (Text), Customer # (Integer), Address (Text), Phone (Text), Last Order Date (Relationship). Shown at Fig. C.
 Figure C.
We'll come back to setting values for the relationship field.
- Creating an Orders database
Save and close the Customers database and Create a New database. This we will call: Orders. Setup the following fields with their respective types (also keep here all the defaults): Customer # (Integer), Customer Name (Relationship), Product (Text), Total Price (Float), Date of Order (Date). Shown at Fig. D.
 Figure D.
- Refining Orders
We now have our basic databases, this gives you an overview of what we are going to do. Since we still have Orders open, let's start from there. Some basic changes: Change the 'Total Price' field number of decimals to two (monetary value), on the 'Product' field add some values to the popups (via Edit Popups button), the values can be vegetables, electrical appliances, etc. Finally we edit the 'Customer Name' field...
- Customer Name Relationship
When editing a field of type relationship, the new items may be a bit confusing. Let's clear things up (Fig. E): Related Field in this DB is the field we are using on this database to compare to the Other Field Name from the Other DB Name. These values establish the relationship. It is suggested that you fill up the values from top to bottom, as the order is important. Once you've established the relationship, you choose what value to Show from the other DB when the condition (the two fields) match. As mentioned earlier in this document, we may choose between the first matches found or the last match found when applying the relationship. In this example's case we are going to compare the 'Customer #' fields from both databases, if they match then we show the first record's value for 'Company Name' from the Customers database. We may also choose the last of the records for the match condition, since we assume we are going to have Customers with different numbers, and thus should make no difference.
 Figure E.
- Last Order Date Relationship
Here we also compare the 'Customer #' fields of both databases, but on this database we display the 'Date of Order's value from the other database. Since we want the last order, make sure you select the 'Last' From Which Record. Please see figure F to see how our results look like.
 Figure F.
- Test Run
Enter a couple of customers in the Customers database, keep their 'Customer #' value different (Fig. G):
 Figure G.
Go to the Orders database and enter a few entries for one particular Customer (refer by using an existing 'Customer #') as per Fig. H. Each time you go in and out of the Edit Record screen the relationship values get updated. If you tap on the 'Customer Name' you are taken to the associated values for that relationship (all, not just the first or last). Tap on Back to return to the Record. We also can observe the same effect on the other database. Also, notice how in the Customer records, only the last record date is shown for 'Last Order Date'.
 Figure H.
Customers and Orders - A Relationship/Links Example
The above example can provide us with a good starting ground for working with relationships. But one thing I'm sure you've noticed is that when we enter a 'Customer #' we must make sure that it is unique, if it's not then the we may see the Orders for the current Customer plus the other Customer with the same 'Customer #'. This step can be solved when blending Relationships together with Links, because Link/Linked field types have globally unique IDs automatically generated for their association. This we will show in the following example. These are our goals:
- Create a Customers database where we can store basic information for each customer.
- Create an Orders database where we can store information about an order for a particular customer, date and product.
- Link the Customer with all its Orders, without the need of a Customer #.
- Show the date for the last Order a Customer made.
Let's begin:
- Creating the Customers database
Create a New database. Enter the name for the database: Customers. Create the following fields with their respective types, keep the default values for all (only name the field and change the type): Company Name (Text), Address (Text), Phone (Text), Last Order Date (Relationship) and Orders (Link). Shown at Fig. I.
 Figure I.
We'll come back to setting values for the relationship and link field.
- Creating an Orders database
Save and close the Customers database and Create a New database. This we will call: Orders. Setup the following fields with their respective types (also keep here all the defaults): Customer Name (Linked), Product (Text), Total Price (Float), Date of Order (Date). Shown at Fig. J.
 Figure J.
- Refining Orders
We now have our basic databases, this gives you an overview of what we are going to do. Since we still have Orders open, let's start from there. Some basic changes: Change the 'Total Price' field number of decimals to two (monetary value), on the 'Product' field add some values to the popups (via Edit Popups button), the values can be vegetables, electrical appliances, etc.
- Customer's Associations
As we did in the previous example we will associate the two databases in two points. We start by the Customer database. Go to its Edit Fields section and choose the 'Orders' field, we will link to the 'Customer Name' in the other database and will show the 'Company Name' from this database (Fig. K) as our customer. On the 'Last Order Date' relationship we associate for the relationship conditional the Link/Linked fields from both databases. This is important since Links are verified by their real numeric value, which is a link.
  Figure K.
- Last Order Date Relationship
Things get easier as we go. On the 'Customer Name' field we choose the Orders database (which is the on that's calling us on the link) and the field that contains the Link, this is straightforward since we just have to choose them from the popup values. On the 'Customer Name' field we choose again the Link/Linked fields for association, but we now display the first company name we find. This technique is very useful for when you have a Link/Linked set of databases and you want to 'show' many fields of data from one database to the other.
 Figure L.
- Test Run
Enter a couple of customers in the Customers database (Fig. M). Go to the Orders database (by taping on the Orders field) and enter a few entries for one particular Customer (each new order is a new record). If you are to go back to the Customers database you will see the latest Order for each one (if you entered data for that particular Customer).
  Figure M.
Conclusion
Associations whether they are links, relationships or any other, help us optimize our data (normalize). This is essential in order to prevent duplicate data entry, better organize data and allow easier maintenance.
Relationships are one of the methods of associating records between databases. They are easy to set-up and easy to understand since they follow traditional database techniques. They are also flexible enough to allow many to many relationships.
You may download the files used in this documentation at: http://www.ddhsoftware.com/gallery.html?show=number&record=1524.
|
