![]() |
![]() |
|
![]() |
Building Tables Part 4: Primary and Secondary Indices By: Liz Woodhouse 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:
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 <g>. 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:
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 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. ![]() |
![]() |
|