Alpha Five Sets and Lookup Tables

What does the term "relational" mean?

A relational database, as opposed to a flat file database, can link two or more tables together. Fields from these tables can be displayed together on the same form or report, almost as if they belonged to the same table. When you relate tables in Alpha Five, the file that defines that relationship is called a set. Remember that sets do not themselves contain data, their tables do.

I'm not sure whether to use a Set or a Table Lookup. What are the advantages and disadvantages of each?

A relational set is efficient because it eliminates the need to duplicate information in multiple records or in more than one table. It also allows you to put fields which are only necessary for a small percentage of records into a small, secondary table. Sets save disk space. When you need to update information, sets save you from having to go to several different places to make changes.

Some users, however, are more comfortable with a table lookup, in which the values from the lookup table are actually filled in and stored in fields in the first table.

One subtle but important difference between lookups and sets is that lookup values will not change once filled in on a particular record, while sets always display current information from child table fields. If you use a lookup to fill in unit price on your invoice table and you change your prices, you can go back to last month's invoices and see exactly what you charged customers at the time you sent them the invoices. On the other hand, you may want to use a set to link your price table to your product catalog table so that you always have access to current prices.

How do I decide which table should be the primary table in my set?

In designing your set, consider the following. In a set you can access all of the records in the primary table, but you only have access to those child table records that match a parent record. If you link a third table as a child of the second table, you will only have access to a record in the third table if a matching record exists in both its parent (the second table) and the primary table.

You have the greatest degree of control over the primary table of the set, especially with regard to indexing. There is no limit to the number of sets in which you can include a particular table. To serve all your needs, you may wish to flip-flop your set tree. Create one set with table A as the primary and table B as the child. Create another set using the same tables, with B as the primary and A as the child.

I made a set, but I can only see data from the primary table. What happened to the child records?

One possibility is an improperly defined link. If the parent and child records aren't matching up at all, this may indicate that and error in selecting the linking fields or that common fields’ data differs in some way.Another possibility is a corrupted index. When you add a table to a set, Alpha Five builds an index on the linking field of the child table. Try rebuilding the child table's indexes.

Is it possible to link together in a set tables whose common fields do not contain exactly the same data?

In the Set Editor, Alpha Five allows you to use an expression to define what constitutes a match between a parent record and a child record. A good illustration of this is a set in which the primary table contains a shipping address, including a five digit zip code. The child table contains UPS shipping zones which are based on the first three digits of the zip code. The linking index uses the child table's three-character zip field. To match up the correct UPS shipping zone with the first three characters of the zip code field in the parent table, use the following common field/expression: SUBSTR(ZIPCODE,1,3)

Return to database tips page

Proctor & Peake, Inc.
9620 NE 2nd Avenue, Suite 209
Miami Shores, FL 33138
305-751-1181 / 1-800-943-6031
e-mail us