Alpha Five Sets and Lookup Tables
|
Alpha Five Sets and Lookup Tables |
 |
|
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. 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. 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. 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. 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) |
|
|
| < back> Proctor &
Peake, Inc. |
top |
What
does the term "relational" mean?