![]() |
![]() |
|
![]() |
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. numberedNotes: 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:
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-255Notes: 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]" 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. ![]() |
![]() |
|