Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Interactive Paradox Articles  |  Beyond Help Articles  |  Tips & Tricks Articles  


Building Tables
Part 4: Primary and Secondary Indices
By: Liz Woodhouse
ParadoxCommunity.com

Primary Index

Secondary Indices

Relationship Between the Key and Secondary Indices
(The following was taken from a newsgroup post by Tom Krieg who was paraphrasing Dan Ehrmann. Edited for this article.)

Paradox (the BDE) adds the primary key to every secondary index automatically, so it can actually find the record referenced by the secondary index. Each secondary index is just a table (X...), of secondary index "values". Each secondary index has a key table (Y..), which enables the BDE to quickly find a value "in" the index. To actually find the .DB record referenced "by" the index, the BDE requires the physical block number or the primary key.

Each secondary index contains the secondary index field values, the primary key and the physical block number of the .DB record itself. The block number is not, however maintained and is only updated when the table is restructured. To access a record via a secondary index, the BDE first goes to the physical block defined by the physical block number. If the record has been moved (because of a primary key change or insertions) and is no longer located in that block, the BDE reverts to using the record's primary key (from the secondary index) to locate the record.

Now you can see why it's a really bad idea to use large, concatenated primary keys because they're included in every secondary index record. It's also a good idea to keep .DB records in the same block after they're created (don't permit changing of primary keys or insertion of records except at the end of a table) because performance is enhanced because the BDE can find a record referenced by its physical block number rather than have to do a few more disk seeks to find it by its primary key.

Using Secondary Indices
Like the key, secondary indices are frequently used for linking. In fact, you might have a secondary index in one table hold key values from another table so that you can link the two tables together. In this situation, the secondary index is also referred to as a "foreign key". Just as with the key, having exact values to link together is critical to ensuring expected results.

While the key ensures uniqueness of each record, a secondary index can be used to ensure uniqueness of data. Paradox allows you to specify that the field or fields in a secondary index contain unique values. This is done by selecting the "Unique" checkbox when creating the index (exact details may vary between Paradox versions).

Other options available to secondary indices are Maintained and Case-sensitive (also selected via checkboxes). It should be noted that an index which is not unique, contains only one field, and is both case-sensitive and ascending, regardless of whether it is maintained, must be named the same as the field which makes up the index (Paradox does this automatically, and it's only a concern when creating the index via code). All other types of indices should be named differently from the fields in the table.

A final option available in secondary indices is sort order (available only in level 7 tables). Each field in the index can be sorted in either ascending or descending order (e.g. you can have one field sorted ascending and another sorted descending). This is done by adding the field to the index, highlighting the field name and then selecting one of the ascending/descending radio buttons. (Again, exact details may vary between versions, please see the help file for the restructure dialog for version-specific information.)

Secondary indices are the only way to sort a keyed table without creating a second "sort" table. This is done by switching the index used to display the table (either in code, or via the Filter Tables dialog accessed by menu or toolbar button). This is one of the best uses of secondary indices as it allows you to quickly change how the table is viewed (sorted by last name one moment, by first name the next).

In addition to sorting the table differently, secondary indices allow you to set ranges which quickly limit the records displayed. Ranges are faster than filters, especially in multi-user environments or with large tables. To set a range, use code or the Filter Tables dialog (select the index, press the Range button and enter a range).

In addition to displaying ranges of records, secondary indices allow you to quickly locate values either manually, via code or via interactive search utilities, by first ordering the records by the field(s) of interest (selecting the index). It should be noted that certain types of locates are only able to take advantage of single-field, case-sensitive, maintained indices (the help is not clear on exactly what searches, testing should be used to determine best practices for your exact situation).

One thing to note about secondary indices (maintained ones anyway) is that they must be updated each time a record is added or a field in that index is edited. This can take time, and so in a table with lots of secondary indices and lots of records, data entry times can be slowed. Therefore, in heavy data-entry environments, you should apply secondary indices judiciously rather than everywhere possible.


Summary

Properly keying and indexing your tables is vital to the success of your overall database application. When used properly, they enhance speed, flexibility and data integrity.

Building tables is one of the first steps in creating a database application. A thorough understanding of the options available will help you use the Paradox table structure to your advantage and avoid potential problems as your database grows.


Discussion of this article


 Feedback |  Paradox Day |  Who Uses Paradox |  I Use Paradox |  Downloads 


 The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation.
 Paradox is a registered trademark of Corel Corporation.


 Modified: 15 May 2003
 Terms of Use / Legal Disclaimer


 Copyright © 2001- 2003 Paradox Community. All rights reserved. 
 Company and product names are trademarks or registered trademarks of their respective companies. 
 Authors hold the copyrights to their own works. Please contact the author of any article for details.