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 1: Files and Fields
By: Liz Woodhouse
ParadoxCommunity.com

Introduction

Tables make up the foundation of your database. Perhaps the most important part of designing your database is designing and building the tables properly. To design them properly, you need to understand basic database design theory and the rules of normalization (for details, follow this link to the Database Basics and Data Normalization papers by Denn Santoro (located on the Paradox Resources page at that link)). To build them properly, you need to understand how Paradox® tables are stored and be familiar with the table structure/restructure dialog and its options. In this series, we'll review the files that make up a Paradox table, explore the table structure dialog and recommend practices to ensure you don't have troubles with your tables down the line.

[NOTE: The table structure/restructure dialog is not identical in all versions of Paradox, but the concepts are the same, and the various options use the same terminology.]


Table Files

Paradox tables consist of multiple, related files. Each file serves a specific purpose and is only created if needed. For our purposes, we only need to know that these files exist, what their general purposes are, and how we should handle them. Here's a table of all the Paradox table file types:
Extension  Description
.DB        Paradox table - where the data lives
.MB        Memos - where memo and binary data live
.PX        Primary index or key
.VAL       Where validity checks are kept
.TV        View (display properties) settings
.FAM       Listing of files related to the .DB
.Xnn       Secondary single-field index, numbered
.Ynn       Secondary single-field index, numbered
.XGn       Composite secondary index, numbered
.YGn       Composite secondary index. numbered
Notes: The 'n' in the last four file types represents a numeric digit. The .Xnn and .Ynn files always come in pairs, as do the .XGn and .YGn files.

.MB files are used to store data for certain field types; these are listed below under Field Structure.

We'll discuss things like what the primary index is, and what secondary indices and validity checks are in future articles. For now, it's enough to know that each .DB file can have multiple related files with the same name as the .DB but different extensions, and that all of these files must stay together. The easiest way to be sure all of a table's files stay with the .DB is to use Paradox's Table Copy and Table Rename utilities, which automatically take care of related files.


Field Structure

The first thing you're presented with in the table structure dialog is an area to define the fields which will go in your table. Each field has the following properties:
Name    What your field will be called
Type    The type of data to be stored in your field
Size    The size (if relevant) of the data
Key     Whether your field is part of the Primary key
Min     Minimum value for the field
Max     Maximum value for the field
Default Default value for the field
Picture Pattern field values must match
Req'd   Whether the field requires a value
[NOTE: Key and Validity Checks (items listed after Key) will be discussed later in the series.]

Name
The field name should be descriptive, but shouldn't be longer than necessary (by default, field names are used as field labels on forms and reports, so you may wish to take that into consideration; also, you'll type the name in code eventually, and you don't want to have to type more than necessary). Paradox field names can be up to 25 characters in length. Paradox allows a variety of non-alphanumeric characters in field names, but it's recommended you don't use these as they can cause problems in QBE and SQL queries, in compatibility with other applications, and in functions within Paradox code. So, here are some recommendations to keep in mind when naming your fields:
  • Only use letters (A-Z, a-z) and numbers (0-9)
  • Do not use spaces, underscores, dashes, or other such characters
  • Start your field names with a letter
  • Use MixedCaps, rather than alllowercase or ALLUPPERCASE, for readability
  • Don't use reserved words as field names
That last recommendation deserves a longer explanation: Reserved words include Paradox methods/procedures, SQL or QBE reserved words, property names or values, etc. If in doubt as to whether something is a reserved word, search for it in the ObjectPAL help file or in an SQL reference book. Examples include: Color, Type and Size (properties); Red, True (property values); Date, Time (field types and methods); Delete, Create, Locate (methods); Select, Calc, Unique (query reserved words).

Type and Size
Type and Size go together, so we'll discuss them together. The following are the field types and sizes supported by Paradox tables:
Symbol Type            Size
   A   Alpha           1 - 255
   N   Number
   $   Money
   S   Short Integer
   I   Long Integer
   #   BCD             0-32
   D   Date
   T   Time
   @   Timestamp
   M   Memo            1-240
   F   Formatted Memo  0-240
   G   Graphic         0-240
   O   OLE             0-240
   L   Logical
   +   Autoincrement
   B   Binary          0-240
   Y   Bytes           1-255
Notes: BCD type is not fully supported in Paradox, and you'll probably never use it. See the help file for details. Size for BCD fields represents the number of digits allowed after the decimal point.

Memo and Formatted Memo field sizes are limited only by the amount of disk space you have. The size value above represents the number of characters which will be stored in the .DB file (all characters will be stored in the .MB file).

Size is optional for Graphic, OLE and Binary fields, and indicates how much of the data will be stored in the .DB file (all of the data will be stored in the .MB file).

Please see the Paradox help file for details on the types of data these fields will hold. For our purposes, we'll discuss some recommendations on things that certain field types are good for (or bad for).


Field Use Recommendations

Autoincrement fields: These fields are good when the sole purpose is to have a unique value. If the value will be used to link tables, if it will have meaning in any way (such as a customer number), if the value must remain the same, don't use an Autoincrement field. Why? Well, there are some problems which make these fields risky: they can 'lose track' of which number they're on; moving data into a copy of the table (for rebuilding the table, or archiving, for example) can reset the field values if you don't do it just right (just right involves restructuring the tables before and after copying or moving data); repairing the table under certain versions of the BDE can lead to the numbers being reset. So, while it's possible to use these fields without ever having a problem, the risk is high and you should be aware of that beforehand.

Long Integer fields make for good 'ID' fields: they'll hold a lot of values (-2,147,483,648 to 2,147,483,647), they're exact (more on this when we discuss indices), they don't take up a lot of space in the table and are 'fast' when doing locates and sorting.

Short Integer fields are good for when you need an integer value that will never go below -32,767 or above 32,767.

Logical fields are great for true/false or checkbox type values.

If you use Memo, Formatted Memo, Graphic, OLE or Binary fields (a.k.a. BLOB (Binary Large OBject) fields), keep these at the end of your tables (it's believed to be more stable, though I can't provide you with direct evidence of this). In some situations, such as when these fields are not frequently used, when the table is already large and you want to keep its size down, or when not every record will have a value in one of these fields, it may be best to keep these fields in a separate table with just the key field and the BLOB field(s). (More on this when we talk about linking tables together.)

Also, it may be best not to use Graphic, OLE or Binary fields at all in certain situations, but rather, to store the files which would have gone into such fields as files on your hard drive, and in an alpha field in your table, store the file name (and possibly path) to the file. You would then use code to access the specified file (more on this in another lesson). This technique keeps your tables small (and makes corruption less likely) and allows you flexibility when working with these files.

I recommend storing date and time in separate fields rather than together in a timestamp field. This is simply my personal preference as I find there's more flexibility in how the values are used when kept in separate fields.

You should be aware that Number fields (and by extension, Money fields) are not always exact. Computers are binary (ones and zeros), but our number system is decimal (increments of ten), this means that decimal numbers cannot always be stored as exact values and are susceptible to rounding errors. If you know this ahead of time, you can plan accordingly. Details can be found on the newsgroups, in the following thread:
     Subject: TIP:PdoxWin:Floating Point Arithmetic
        Date: Tue, 29 May 2001 22:04:48 -0400
        From: "Michael Irwin [CTech]" 
    Reply-To: ctech@corel.ca
Organization: CTech
  Newsgroups: corel.wpoffice.paradox-faq

Summary

Tables are the most vital part of your application. They're where your data lives, and when they're properly built, creating the rest of your application, and adding to it later, will be much easier.


Part 2: Validity Checks and Table Lookup


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.