Home arrow Articles arrow Interactive Paradox arrow Building Tables: Part 4a: Primary and Secondary Indices
24 April 2014
 
 
Building Tables: Part 4a: Primary and Secondary Indices Print E-mail
Contributed by Liz McGuire   
08 November 2001
Part four of this series on the basics of building tables in Paradox covers primary and secondary indices.

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

Introduction

In the previous section, we discussed table language, table level and passwords and how to take advantage of these advanced table features.

In this two-part article, we'll continue exploring the table structure dialog, and discuss the following items:
  • Key (primary key, primary index)
  • Secondary Indices
Your table's key and indices are as important as any part of your table. They allow you to link tables together and they can add speed and flexibility to your application. For our purposes here, we'll discuss how to create the key and secondary indices and some recommendations relating to their use. A complete discussion of using keys and indices is outside the scope of this series. However, I recommend reading reference material on relational database design, including that mentioned in Part 1 of this series.

Note on Linking Tables
As one of the main functions of a relational database is linking related tables together, and as that is always done via at least one index, it seems relevant here to briefly discuss the terminology and general concept of linking tables.

Tables are linked together on fields of compatible types and sizes. When two tables are linked in a data model, a form or report will display the related records, and only the related records, from both tables at the same time. When two tables are linked in a query, data from records with the same values in the linked fields will be retrieved into a single 'answer' record.

I have simplified this description (and the following ones) in the extreme to ensure sufficient knowledge to understand the importance of indices. As previously mentioned, a study of relational database design is warranted.

There are two basic types of links in Paradox: one-to-many (1:M) and one-to-one (1:1). There are other types of links, but these are more conceptual and when created in Paradox, they are represented as one of the two previously mentioned types (a study of relational database design should cover all types of links). Some terminology which I and/or others might use to refer to the tables involved in a link are: the first table and the second table, the master table and the detail table, the parent table and the child table, the one on the left and the one on the right . In each case, I've kept the names in the same order as the link name (one-to-many or 1:M).

The link names reference how many records in the second table of the link relate to one in the first table. Thus, in a 1:1 link, there is one record in the second table for each record in the first table; in a 1:M link, there are many records in the second table for each record in the first table.

When linking tables in Paradox, you can link from any field* or set of fields (they do not have to be consecutive) in the table on the left to any index in the table on the right. Thus, there will always be at least one index (either the key or a secondary index) involved in a link between two tables. (*Because indices cannot be created on the following field types, you cannot create a link between them: memo, formatted memo, graphic, OLE, binary, bytes, logical.)


Primary Index: The Key

The key of a table is that part of a record which uniquely identifies that record, or distinguishes it from all other records. It is defined in the restructure dialog in the place where fields are defined (generally, there is a column which you mark to indicate the field is part of the key - exact details vary between versions, please see the help file for specifics). The following are the rules for keys:
  • A table can only have one key.
  • Memo, formatted memo, graphic, OLE, binary, logical and byte fields cannot be part of the key.
  • The key must be, or start with, the first field in the table and a multi-field key must include the first fields of the table, in order, without skipping any fields.
  • The key must be unique (if it's a multi-field key, the combination of values across the key fields must be unique for each record).
  • The key can contain a maximum of 16 fields.
When you key a table which already has data, there's a possibility some records (which have duplicate key values) will be removed from the table. If this happens, the duplicates will be placed in :PRIV:KEYVIOL.DB (if this table already exists, Paradox creates one with the same name, but numbered (e.g. KEYVIOL1.DB)).

Key Recommendations
The following are my recommendations, based on learning and experience, for best practices when establishing a key. As with all situations, details may dictate varying from these practices, and others may have differing opinions on the subject. I strongly recommend studying basic relational database design so you have a good understanding of how relational databases work and how keys fit in the picture.

The primary purpose of a key is to uniquely identify a record. Therefore, one of the first rules is that the key should consist of something which is capable of being unique. A name or phone number, for example, is not a good way to uniquely identify an individual because more than one person can share the same name or phone number.

Another purpose of the key is to link tables together. When Paradox links one table to another, it does so by comparing the values in the linking fields. If the values are identical in both tables, then Paradox knows they are related. Therefore, another important attribute of a key is that it be an exact value. All of the field types which Paradox allows to be use as or in the key store exact values, however, not all of them provide an easy means for the user to enter or query for the same exact value. An example is a Timestamp field which stores time down to the millisecond. However, Paradox does not provide a way for a user to enter a timestamp value down to the millisecond, so the milliseconds stored are those Paradox chooses to store and depending on the method used to enter the timestamp value, this may not be discernable except by code. For this reason, I recommend one select from the following field types when establishing a key: Alpha, Short Integer, Long Integer, Date. (NOTE: Please see part 1 of this series for why I don't recommend Autoincrement fields be used for this purpose.)

Another important attribute for field values used to link tables together is that the values not be changed. While there are ways of dealing with changing values (ensuring they are changed in all related tables), the risks associated with attempting to keep all tables synchronized as well as the increased maintenance involved suggests that the simpler and safer approach is to use an unchanging value, and the only way to assure the value will never change is if it is meaningless (the data represents nothing other than a unique identifier for the record and a linking value back to this record in other tables).

Other things to keep in mind: A smaller key consisting of fewer fields will be faster and more stable (less prone to corruption) than one which is overly large. The key makes up a part of all secondary indices and therefore, a large key has the same influence on the speed and stability of secondary indices. My personal recommendation is that you use one, or where needed, two, long integer fields as your key and that the values in these fields be given no value or purpose other than to be unique and to link records in multiple tables.


Secondary Indices
Last Updated ( 16 May 2005 )
< Prev   Next >
 
Top! Top!