Updated chapter 2 from my Paradox 9 Power Programming: The Official Guide
[Updated 6/8/2008 from my updated version on my HD. Also added pics.]
The table is a tool that you, the developer, use to store data. In your continuing quest to study the fundamentals of design, this chapter discusses constructing a database; that is, creating tables with relationships in mind. It discusses all three types of database relationships and their variations: the one-to-one (1:1) relationship, the one-to-many (1:M) relationship, and the many-to-many (M:M) relationship. For local table users, this chapter also helps you decide which table structure to use, namely, Paradox or dBASE. Finally, this chapter covers a few of the interesting characteristics of both dBASE and Paradox tables. First, however, consider the importance of aliases to the developer.
Aliases
In the past, when you developed an application and moved the data, you had to redefine all your links (a link establishes a relationship between tables by connecting corresponding fields). If you moved one of the tables to another directory path, you created a big problem. The usual solution was to break and then create the link again. An alias is the name you assign to a directory path and implement to solve this problem. Paradox uses aliases to refer to a location of files, such as a directory or to a specific database on a SQL server using either the BDE's native drivers or ODBC. You manage your aliases by selecting Tools | Alias Manager. In addition to using two types of native drivers (file-based and server based), you can use ODBC drivers with the BDE.
In Paradox table terms, the location that is pointed to by the alias often is called a database. Although aliases might be new to you, they are a great time-saver. They make using hundreds of tables, queries, and forms in many different directories and on different servers manageable. There's tremendous value in having the capability to select File | Open | Table and browse through the aliases that you set up. In addition, you can use aliases in your code to refer to files. If you move the files, you just need to define the new path in the alias.
Using an alias offers the following benefits:
Long pathnames are shortened to a single user-defined word.
Your applications are instantly relocatable. ObjectPAL code can refer to objects by means of an alias. For example, when the path of your tables changes, redefine the alias to point to the new path.
You can use multiple sets of data with the same application. After an application is set up with an alias, you can redefine your alias to point to a different path; instantly, you are working with a different set of data. This makes it possible to develop an application on your local drive and then switch the tables it uses to network tables and even to a SQL server with little modification.
You can set up aliases on a system by selecting Tools | Alias Manager or by using the BDE Administrator. You can use the Aliases tab of the BDE Administrator to easily browse through existing aliases and set the paths of existing aliases or add a new alias. I find it easier to use the BDE Administrator to add and alter aliases (see Figure 2-1).
Figure 1: The Databases tab from the BDE Administrator
When you're ready to start creating your tables, you need to decide where you want to place them. You have two choices: the current working directory or another directory. If you're developing a single-user application, it makes sense to put all the files in the same directory if you will never need to move the tables to a network directory. If you are developing what might become a multiuser application, consider separating the tables from the rest of the application and using an alias to refer to the tables. Using an alias gives you the ability to move your tables to a network directory later, if needed. Because Paradox doesn't search for a table, you must develop your application with tables in the current working directory or use an alias. Hard-coding table paths usually is a mistake because it then becomes more difficult to move the tables.
Tip: Put your tables in a directory different from the one that contains the forms, reports, and libraries. Use an alias to refer to the tables. This makes your application instantly a network-compatible application by making the tables relocatable. When you install the application onsite, you simply have to ask where to put the data and where to put the application. The application can be local or Demo Fileswork. Place the data files where they need to be, and change the alias path. If you eventually want to move your data onto a SQL server, then you will have less code to rewrite.
Standard Aliases
When you create a standard alias (see Figure 2-2), you can use one of these driver types:
PARADOX Paradox, for .DB tables
DBASE dBASE and FoxPro, for .DBF tables
ASCIIDRV ASCII text, for .TXT tables
Figure 2: Alias Manager showing a standard alias
The Alias Manager available within Paradox by selecting Tools | Alias Manager only allows you to configure the path attribute of a standard alias. To configure the other settings (for example, for a Paradox driver), you use the BDE Administrator. The BDE Administrator is available via a shortcut in the Utilities group of the Paradox group. Figure 2-3 shows the BDE Administrator displaying the Paradox driver settings.
Figure 3: Paradox driver settings in the BDE Administrator
The following table lists the settings of the Paradox driver. For other driver settings, refer to the BDE Administrator help.
Parameter
Description
VERSION
Internal version number of the Paradox driver.
TYPE
Type of server to which this driver helps you connect. Can be SERVER (SQL server) or FILE (standard, file-based server).
NET DIR
The directory location of the Paradox network control file PDOXUSRS.NET. The active NET DIR parameter is stored in the Paradox section of the BDE configuration file and has precedence over any other NET DIR parameters that may be stored in older 16-bit configuration files, or in the System Init section of the current configuration file, or in the Registry. These other NET DIR entries will have no effect. To access a Paradox table on a network drive, the active NET DIR parameter in the Paradox section of the BDE configuration file must point to a network drive and folder in common to all users.
LANGDRIVER
Language driver used to determine table sort order and character set. [available drivers]. US default: 'ascii' ANSI (DBWINUS0)
LEVEL
Type of table format used to create temporary Paradox tables. Level 7 - Paradox for Windows 32-bit tables Level 5 - Paradox 5.0 tables Level 4 - STANDARD table format introduced in Paradox 4.0 Level 3 - Compatible table format used by Paradox 3.5 and earlier versions. Default: Level 7. To use Blob fields, secondary indexes, and strict referential integrity, specify either Paradox level 4 or Paradox level 5 tables. You will probably want to use the lowest level possible in order to maximize backward compatibility. Choose Level 7 only If you need the advanced indexing features supported by that table format. For example, Paradox 7 tables are the only level that completely support long filenames (this is an issue when dealing with RI or lookups across directories). Note: The default level is 7 unless the BDE is already on the system. The setup will leave the settings unchanged if it finds the BDE.
BLOCK SIZE
Size of disk blocks used to store Paradox table records, in multiples of 1,024 bytes. Valid settings depend on the table format: Level 5 and 7 - 1,024, 2,048, 4,096, 16,384, and 32,768 Level 3 and 4 - 1,024, 2,048, and 4,096 Default: 2,048
FILL FACTOR
Percentage of current disk block that must be filled before Paradox will allocate another disk block for index files. Can be any integer ranging from 1 to 100. Default: 95 Note: Smaller values offer better performance but increase the size of indexes. Larger values give smaller index files but increase the time needed to create an index.
STRICTINTEGRTY
Specifies whether Paradox tables can be modified using applications that do not support referential integrity (such as, Paradox 4.0). For example, if TRUE you will be unable to change a table with referential integrity using Paradox 4.0; if FALSE, you can change the table, but you risk the integrity of your data. Default: TRUE.
Public and Project Aliases
You can create either a public or project alias. Project aliases are stored in the PDOXWORK.CFG file, which is loaded whenever you change working directories. You can think of project aliases as aliases that belong to a certain project stored in a specific working directory.
Tip: If you are developing an application using local tables and you know you are eventually going to move the data onto a SQL server, then do yourself a favor and spend a few hours studying the field and table naming rules of your target SQL server before you create your first table. While creating your local tables, use a naming convention that uses common rules. If you use the naming rules in common between the two database types, then you will save yourself time when you move your data to the SQL server because SQL servers often have different naming rules from the naming rules in Paradox.
SQL Aliases
The database engine provides several SQL native drivers including InterBase, Oracle, Microsoft SQL, Sybase, DB2, and Informix. These native drivers generally offer greater control and speed over many ODBC drivers. When both a native and several ODBC drivers exist for a SQL server (for example, as with Oracle), you might wish to do some experiments with them to determine which will perform best for your application architecture. Figure 2-4 displays an Oracle SQL alias in the Alias Manager.
Figure 4: Alias Manager showing an Oracle SQL alias
ODBC Aliases
You can make use of any ODBC data source when you create an alias (see Figure 2-5). To create an ODBC alias, first you must use the ODBC Administrator to add, delete, and configure ODBC drivers or data sources. This command appears when you right-click an existing ODBC alias in the Databases tab of the BDE Administrator or the ODBC driver on the Configuration tab. It displays the ODBC administration utility provided by your ODBC driver vendor.
Figure 5: Alias Manager showing an ODBC alias
To make use of an ODBC driver, follow these steps:
Add the ODBC data source to your system using the ODBC Administrator.
Define the DSN (data source name). Note that if AutoODBC in the BDE Administrator (under Configuration | System | Init) is set to TRUE, then any ODBC DSN automatically shows up as an alias in Paradox. The default is set to TRUE when installing Paradox onto a machine that does not have the BDE already installed.
Set up an Alias that uses the data source.
Note: If you add an ODBC data source, you must define its DSN before you can connect to that database.
Primary Keys
Paradox tables enable you to manage many types of data in its fields. A field in Paradox terms is a single value in a record-for example, City. The entire City column of a table is considered the City field. In addition to standard types of data, such as text, number, date, and currency, you can store a variety of data, such as graphic, OLE, and binary. With Paradox table structures, you have a variety of data types including Time, TimeStamp, BCD, and Autoincrement.
The structure of a table is the arrangement of fields in a table: their data types, indexes, validity checks, and so on. Now, more than ever, deciding on table structure and relationships is a crucial element of developing an application. Develop tables in two steps: decide on data components and then decide on the table relationships. The first step in deciding table relationships is to decide which fields need to be a part of the primary key.
A record is the horizontal row in a Paradox table that represents a set of data for that item, such as a person's address information. A primary key sorts your table and makes each record unique. A primary key isn't required but it is highly recommended. In fact, I suggest that every table you create should contain a primary key. A key on a Paradox table orders records and ensures unique records, and allows referential integrity, which is a way of ensuring that the ties between similar data in separate tables can't be broken. Referential integrity is defined at the table level in Paradox. Establishing a key has several effects. It prevents duplicate records, sorts records, enables use of the table in a detail link, and speeds general access to the table.
A Paradox primary key can consist of more than one field (up to 16). These fields are treated as one-a composite. A composite keyorindex is a key or index composed of two or more fields of a table. A composite primary key must be made up of the first fields of the table. Use composite key fields when a table contains no single field in which every value is unique. Together, the combination of the fields in the key sorts the table. Define the primary key from the first field through however many fields it takes to make each record unique.
When a table has a composite key field, duplicate values are permitted in an individual key field as long as the values are not duplicated across all the key fields. In other words, the key fields, taken as a group, must uniquely identify a record. To sort tables that have composite key fields, Paradox starts with the first field and then sorts the following fields. Paradox's primary key also promotes normalized table structures (more on this later in this chapter).
Should you go crazy and key most or all the fields in a table? No. Your goal is always to find the least number of fields that will make each record unique.
Primary Keys and Secondary Indexes
An index is a file that determines the order in which a table displays records. Objects such as UIObjects, TCursors, and Table variables can use an index to point to a specific record and to speed up certain operations. A secondary index is an extra index primarily used for locating a specific record, linking two tables, querying, and sorting. Paradox tables can have a primary key and as many secondary indexes as you want, whereas a dBASE table can have only indexes-that is, files with .MDX or .NDX extensions. In general, you can refer to any file that sorts a table as an index.
Tables need indexes to speed them up. When using Paradox tables, get used to the idea of creating maintained and case-sensitive secondary indexes. Accepting and doing this simple step will greatly improve the overall speed of your application-especially queries. When you move up to writing applications that access SQL servers, remember that creating indexes also speeds them up.
Using a Primary Key
To demonstrate the proper use of primary keys and secondary indexes, we will develop a typical address table. In the following example development cycle, you will see how a table may change over time as you realize what truly makes each record unique. This is a natural process. Although it is a good idea to finalize your table structures as early in the development process as possible, you will learn from this example that you can finalize your table structures at any stage during the development process.
In a typical table consisting of addresses, you might index on the combination of first and last name. This combination of first name and last name makes every record in the table unique. The following is a typical address table with * representing the composite primary key:
1: First Name* 2: Last Name* 3: Address 4: City 5: State 6: Zip 7: Phone
This table, however, first sorts by first name and then by last name. Ann Martinez will come before Bobby Brown. For a more standard sort, you might sort by last name and then first name. If you need to search by first name, create a secondary index that consists of first names so that you can search and sort by a person's last name, first name, or last and first names. The following is a typical address table with a more standard sort order. It has a secondary index represented by **.
1: Last Name* 2: First Name* ** 3: Address 4: City 5: State 6: Zip 7: Phone
After entering records for a few days, you may discover that you have two identical names, such as John Smith. Because the combination of last name and then first name no longer makes each record unique, you come to the conclusion that it is a good idea to include the person's middle initial. With large amounts of data, you might even include the street address. This takes into account people with the same name, but who live at separate addresses. It also enables you to keep track of people's home and work addresses. Following is an example of a fully unique address table with a secondary index on First Name:
1: Last Name* 2: First Name* ** 3: Middle Initial* 4: Address* 5: City 6: State 7: Zip 8: Phone
In most cases, keying from the first field through however many fields needed to make each record unique works. This technique is awkward at times, however. Consider the previous case in which you have included one's address to force each record to be unique. The unique primary key now contains two types of data (a person's name and address). The key now consists of four fields and therefore takes up more disk space then a key with fewer fields. A different approach, perhaps slightly more elegant, involves using a single, unique field entry, such as a Social Security Number field to make each record unique and using secondary indexes for alternate sorting and searching. Following is an example of a fully unique address table with two composite secondary indexes:
1: SSN* 2: Last Name 1 **2 *** 3: First Name 2 **1 *** 4: Middle Initial3 ** 5: Address 6: City 7: State 8: Zip 9: Phone
If this table is developed for the government, then you might discover that the government actually reuses Social Security numbers. Therefore, if you need to keep a history, you may need to find another unique identifier, such as Birth Date:
1: Social Security Number* 2: Birth Date* 3: Last Name 1 **2 *** 4: First Name 2 **1 *** 5: Middle Initial3 ** 6: Address 7: City 8: State 9: Zip 10: Phone
If, for example, it is illegal to ask for a customer's Social Security number in your state, then the preceding table structure will not work. Because of a business rule, you might have to find another keying solution. The following presents a final solution, an autoincrementing key or artificial key:
1: ID* 2: Last Name 1 **2 *** 3: First Name 2 **1 *** 4: Middle Initial3 ** 5: Address 6: City 7: State 8: Zip 9: Phone 10: Social Security Number 11: Birth Date
In this final solution, a random unique identifier is used to make each record unique. Although this artificial key is not preferred, the Autoincrement field would work well in this situation. The Autoincrement field type is a special field type that contains unique noneditable numbers. Paradox begins with the number 1 and adds one number for each record in the table. This field type can be very useful when designing tables. In particular, it is useful for adding an artificial, unique, primary key to a table.
Note: Autoincrement fields have the same type as a long integer. This is important to know when linking with detail tables.
In general, try to avoid this final solution and use it only as a last resort. Using an artificial key generally is considered a bad idea. Have you ever gone into a store and been asked whether you know your customer number? Sometimes it's your phone number or your Social Security number, but sometimes it's a random number that you were expected to remember (and you probably didn't). If you use this last solution, make sure that you build an easy-to-use, effective way to quickly look up a customer's ID number.
As you can see, deciding which fields to include in a table is only half the battle. The other half of the battle is deciding what makes each record unique.
Relating Tables
The following material reviews the concepts and theories behind setting up table relationships. If you need help linking tables with the data model, refer to Paradox's manuals. Advanced database developers will appreciate this review of table relationships.
When you hear developers talk about table relationships, you hear things such as "the two tables are linked in a one-to-many." What they really are describing is the theoretical number of records possible in each table. The term one-to-many translates into the following: For every unique record in the first table, the second table can have one or more records. Examine a few cases to clearly understand relating tables, the terminology, and the theory behind relating tables.
The One-to-One Relationship (1:1)
Suppose that you're working with two tables that are both keyed on the Social Security number (the field name is SSN). One table contains personal information; the other contains medical information. You want to pull data from both of them and display that information as though it came from one database. You need to relate the two tables based on a common field or common fields-in this case, SSN.
After you relate these two tables in a 1:1 relationship, the medical database will display the correct record whenever you display a record from the personal database. Whenever you want to relate two tables, they must have one or more fields in common.
Now for the theory. Although you may not see a good reason that you can't dump all the information from both tables into a single table, there is one. In general database theory, it's a good idea to group data into smaller tables based on logical splits of the data such as address, employee, and medical information.
If you dump all the data into one huge table, managing the data can get out of control and the tables can grow unnecessarily. Imagine a table with address, employee, and medical information in it. It is a single table with perhaps 50 fields in it. Every time you create an entry and store just address information or work information, the database reserves room on your hard drive for all 50 fields, even though the majority of the fields are empty!
Now, imagine that this table is broken into three tables: ADDRESS.DB, EMPLOYEE.DB, and MEDICAL.DB. If you enter a record into the address table, no room is necessarily reserved in the employee and medical tables. Breaking large tables into several tables can save disk space and makes working with your data faster and easier to analyze.
An Example of a 1:1 Relationship
A 1:1 relationship is really just a large table split into multiple tables and linked on a common field or fields. In the following table relationship, for every record in Customer, there can be only one record in Custnote (see Figure 2-6).
Figure 6: A 1:1 relationship in the Visual Database Designer
The One-to-Many Relationship (1:M)
A master table in a multitable relationship is the primary table of your data model. If there is only one table in your data model, it is the master table. A detail table in a multitable relationship is the table whose records are subordinate to those of the master table. A detail table is also called a slave table, a child table, or a many table. A clearer way to state this is as follows: for every record in the master table, there can be many records in the detail table.
An Example of a 1:M Relationship
When you look at an invoice from a store, you typically are seeing a master table and its detail records; the end product of tables set up in a series of 1:1 and 1:M relationships. This next example shows two tables from a typical invoicing system. For every order in the Orders table, there can be many line items in the Lineitem table. Note that the two tables are linked on Order No and that the Lineitem table uses a composite primary key.
Demo Files: The form and data models that demonstrate this table relationship are: dm_1-m.fsl and dm_1-m.dm
Orders.db Lineitem.db 1: Order No* -->> Order No* 2: Customer No Stock No* 3: Sale DateSelling Price 4: Ship DateQty 5: Total Invoice 6: Amount Paid 7: Balance Due 8: Payment Method
The Many-to-One Relationship (M:1)
Usually when discussing table relationships, you're talking about the primary key. More specifically, however, you should be talking about the fields on which the tables are linked. The next example does not use an index on the first table; it demonstrates a M:1 relationship. At first glance, this may seem like a 1:1 relationship, but on closer inspection it actually is a M:1. You can have many records in Orders with the same Customer No for any one Customer No in the Customer table. The Customer No field in the Orders table in this case is called a foreign key.
Demo Files: The form and data models that demonstrate this table relationship are atdm_m-1.fsl and dm_m-1.dm
Orders.db Customer.db 1: Order No* 2: Customer No <<-- Customer No* 3: Sale Date Name 4: Ship Date Street 5: Total Invoice City 6: Amount Paid State 7: Balance Due Zip 8: Payment Method Country 9: Phone
The Many-to-Many Relationship (M:M)
A many-to-many (M:M) relationship is when two tables share a common field or fields. Both can have multiple values based on the field(s) in common. Following is an example of a M:M relationship:
The preceding M:M relationship becomes clearer when you add an intermediate table and make the relationship a M:1:M (see the "An Example of a M:1:M Relationship" section later).
Relating Three Tables
Now that you understand the basic three table relationships, let's add a third table to the scenario and study some case examples.
An Example of a 1:1:1 Relationship
Earlier in this chapter, I mentioned breaking a large table full of address, employee, and medical information into three tables: ADDRESS.DB, EMPLOYEE.DB and MEDICAL.DB. The following is that table relationship.
Demo Files: The files DM_1-1.FSL and DM_1-1.DM are available at http://prestwood.com/forums/paradox/books/official/. These files demonstrate this table relationship.
Address.db Employee.db Medical.db 1: SSN* --> SSN* --> SSN* 2: Last Name Department Male or Female 3: First Name Desk Phone Color of Hair 4: Address 1 Manager SSN Color of Eyes 5: Address 2 Start Date Weight (lbs) 6: City Salary (per year) Height 7: State Shift Start Time Blood type 8: Zip Shift End Time
An Example of a M:1:M Relationship
The next example is a follow-up to the M:M relationship presented earlier. This three-way table relationship is really just two 1:M relationships. For every record in the Address table, there can be many records in the Phone table and in the Credit table.
Phone.db Address Credit.db 1: SSN* <<-- SSN* -->> SSN* 2: Phone Desc* Last Name Credit Card* 3: Phone First Name Number 4: Address 1 Expiration 5: Address 2 Credit Limit 6: City 7: State 8: Zip
A Deceiving Example of a M:1:M Relationship
As stated earlier, a typical invoicing system is simply a series of 1:1 and 1:M table relationships. The next example is of a M:1:M relationship. More precisely, it is a M:1 between Orders and Customer and a 1:M between Orders and Lineitem. There can be many records in the Orders table for every one record in the Customer table. For every record in the Orders table, there can be many records in the Lineitem table. The Customer No field in the Orders table is a foreign key; that is, the key of another table.
Orders.db Customer.db Lineitem.db 1: Order No* --------------------->> Order No* 2: Customer No <<---- Customer No* Stock No* 3: Sale Date Name Selling Price 4: Ship Date Stree Qty 5: Total Invoice City 6: Amount Paid State 7: Balance Due Zip 8: Payment Method Country 9: Phone
An Example of a 1:M:M Relationship
The next example is a 1:M:M (or more precisely, a 1:M with a 1:M). This example uses the same tables as the preceding example, but it views the data differently. A secondary index is used on the Orders table.
Customer.db Orders.db Lineitems.db 1: Order No* --->> Order No* 2: Customer No* ->> Customer No** Stock No* 3: Name Sale Date Selling Price 4: Street Ship Date Qty 5: City Ship Via Total 6: State Total Invoice 7: Zip Amount Paid 8: Country Balance Due 9: Phone Payment Method
A good technique to use when you present this much data is to show the user only one or two identifying fields from each table and to use another page or form to show the details.
An Example of a 1:M:1 Relationship
Take a look at a table relationship that often throws developers off. In this scenario, you are developing the table structure for a clothing manufacturer. You have two tables: types of material and outfit patterns (MATERIAL.DB and PATTERNS.DB). You need to connect any one record in the Material table to any one record in the Patterns table. Initially, this sounds like a M:M, but it is not. After you start developing this table relationship, you will discover that you need a third intermediate table, as in the following:
This three-table relationship is a 1:M:1 relationship. For every record in the Material table, you can have many records in the Outfits table; and for every record in the Patterns table, you can have many records in the Outfits table.
The Recursive Relationship
The last table relationship to be demonstrated is a recursive relationship. A recursive relationship is when a single table is used as though it were two tables. In certain table structures, a single table contains all the fields needed to link to itself in a 1:M relationship. For example, take a look at the following table:
Employee.db 1: SSN* 2: Department 3: Desk Phone 4: Manager SSN ** 5: Start Date 6: Salary (per year) 7: Shift Start Time 8: Shift End Time
Any one Manager SSN field can link to many records defined by the SSN field. To do this relationship, you need a primary key and a secondary index. Putting a table multiple times in a data model enables you to do a recursive relationship. To aid in visualizing this relationship, use a table alias in the data model.
In a data model, a table alias is an alternate name for a table. A table alias enables you to rename a table in a data model. This enables you to refer to the table in the data model by the table alias rather than the table name. This is important in ObjectPAL when you need to refer to a table in a data model. Rather than referring to the table name, you can refer to the table alias. This way, if you change the underlying table, you do not have to change your code.
Table aliases are also helpful when using a recursive relationship in a data model. You can give the same table that appears multiple times in your data model different table aliases. This isn't required, but can have the advantage of clarifying your data model.
The recursive relationship is easier to understand if you look at a variation of it, as follows:
Emp2.db 1: Manager SSN* 2: Employee SSN*** 3: Department 4: Desk Phone 5: Start Date 6: Salary (per year) 7: Shift Start Time 8: Shift End Time
Now you can easily see that the combination of Manager SSN and Employee SSN makes each record unique. In addition, there is a secondary index on Employee SSN, so you can still use the table in the same manner as the previous example.
Note: Another technique for doing a recursive relationship in Paradox is to use a live query in the data model and link from the query to the table. For more information about live queries, refer to Paradox's manuals.
Normalizing Data
Now that you have laid down a foundation for creating and relating tables, move onto normalizing data. The process of normalizing table structures arranges data to minimize data redundancy. This, in turn, usually leads to a database that uses less disk space, is easier to extract data from, and is sometimes faster. When you normalize data, you decompose one big flat file table into multiple relational tables. Each record includes the smallest number of fields necessary to establish a category. Rather than dumping all possible fields into a large table, normalized tables distribute information over many tables. This saves disk space each time a user doesn't need to use a particular set of fields. In addition, normalized tables provide more flexibility in terms of analysis and reduced errors in data since data is stored only once. Normalized data should be your goal at all times.
In this method of organizing information, you group fields into categories in which each category contains the least number of fields necessary to establish a unique group. A normalized data model is not required in Paradox, but it is highly recommended. The normalization process discussed next is broken into three steps: remove identical records, remove repeating groups, and move fields that do not relate to the key to another table. These three steps are commonly known as the first three normal forms. Although several other rules for normalizing data are common, it is generally agreed that the first three normal forms are sufficient for most database applications.
First Normal Form: Remove Identical Records
To put a table in first normal form, you remove all identical records. When you key a table in Paradox, you remove all duplicate records. (See the section, "Using a Primary Key," earlier in this chapter for a complete discussion.)
Second Normal Form: Remove Repeating Groups
To put a table in second normal form, you remove repeating groups-a process that is more difficult to explain. An example is used to help explain this step. In a traditional name and address table, you store a person's telephone number as part of the main table. In today's fast-paced, high-technology world, someone can have many telephone, fax, and communication numbers. A person can have several home and work numbers, as well as fax and modem numbers-perhaps even Internet and CompuServe account numbers. You could guess the maximum number of potential fields and put them in the table. (Typically, developers will put Home, Work, and Fax fields in their tables.) Some users might have no numbers, however, and some might have all three. Because when a record is created, database products allocate disk space for all fields defined, this can be a waste of disk space.
In addition, this scheme does not work when you need to have four or more numbers. Data with the potential for so many telephone numbers should be normalized. Again, the second rule to normalizing your data is to remove repeating groups. A traditional table with a Home field, a Work field, and a Fax field repeats a single field phone number three times. A good solution to this problem is to break apart the two bodies of information and link them in a 1:M relationship with a data model. In other words, for every one record in the parent table, there can be many records in the child table. Following is an example of using a second table for telephone numbers:
Rolodex.db Rolodx-p.db 1: Last Name* -->> Last Name* 2: First Name* -->> First Name* 3: Middle Name* -->> Middle Name* 4: Address 1 Phone # Name* 5: Address 2 Phone Number 6: City Phone Ext 7: State 8: Zip 9: Zip Ext 10: Notes
Third Normal Form: Move Nonrelating Fields
The third and final normal form outlined in this chapter involves tables that have a composite primary key. If a nonkey field relies on only part of the total key, the nonkey field should be moved to a separate table. This means that every field in the table must be directly related to all the key fields-not just some of them. For example, in a typical invoicing system, the child or detail table consists of at least the following:
Note that Description is related only to the Stock No field of the two-field composite index. Because Description does not directly pertain to Order No, it should be moved to another table and the two tables linked by the common field Stock No. The following structure accomplishes this:
Lineitem.db Stock.db 1: Order No* 2: Stock No* -> Stock No* 3: Selling Price Vendor No 4: Qty Description 5: Qty on Hand 6: Cost Price 7: Selling Price
If you study and learn the preceding three rules of normalizing data, you will be able to create larger databases that are optimized for disk space. In addition, the data will be better organized and therefore easier to analyze. I suggest that you reread this section-or this entire chapter-in a month or two in order to drive the point home. In addition, consider purchasing a book on normalizing databases.
Learn the Three Normal Forms in One Sentence
The three normal forms can be summed up in the following phrase: "All the fields of a table should relate to the key, the whole key, and nothing but the key."
dBASE Tables Versus Paradox Tables
After you decide to use Paradox as your front-end development tool, you still need to decide whether to use a local table structure, such as dBASE and Paradox, or move to a SQL server and use the client/server model for your software architecture. In general, use local tables when a smaller amount of data is being collected and use a SQL server when either a very large amount of data is being collected, a large number of concurrent users will be accessing the data, or when a SQL server offers a feature that Paradox and dBASE do not-for example, transactions, automatic backup, and recovery. After you decide on a local table structure, you still need to decide between Paradox or dBASE table structures. You can store tables on either your hard drive or a networked drive. Both have their advantages and disadvantages. Although single-user applications can store the data on either your local hard drive or the network, multiuser applications store the data Demo Fileswork. What follows is intended to help you decide, but I must tell you up front that my strong preference is for the Paradox table structure.
The primary key in Paradox promotes normalized table structures. The BDE and Paradox now supports dBASE 7 tables and their new features such as primary keys, min and max values, defaults, and required fields. However, dBASE has not always used primary keys, and it is still optional (you can index a table without using a primary key). On the other hand, dBASE allows for the flexibility of non-normalized tables because it does not require the concept of a primary key. Both dBASE and Paradox offer flexible table structures. In addition, the Paradox table structure enables you to use spaces, lowercase characters, and special characters in the field's name. This feature along with primary keys decided the issue for me. The Paradox table structure enables you to humanize field names. For example, in the past with other database products you might have represented someone's first name with FNAME; now you can use First Name complete with a space.
The Paradox table structure supports so many advanced features that the choice seems clear. For example, the Paradox table structure has referential integrity and supports advanced field types, such as Formatted Memo, Graphic, Time, and Autoincrement. In addition, Paradox allows for unique and descending secondary indexes. dBASE, however, has the benefit of being supported on almost every platform, including DOS, Windows, OS/2, Macintosh, Amiga, and UNIX. Many products support the dBASE standard on many platforms. dBASE index expressions also permit tremendous flexibility. Refer to the "Using dBASE Expressions" section, later in this chapter.
Whether you use dBASE or Paradox tables is up to you. Each one has its advantages and disadvantages. dBASE gives you more flexible indexes and allows for compatibility across platforms. Paradox is faster, promotes normalized data structures, has referential integrity, and has more flexible field types. If after reading this short section, you still are not sure which table structure to use, I suggest you read the rest of this chapter carefully and experiment with both structures until you decide.
Paradox and dBASE Field Types
Why talk about Paradox and dBASE field types in a book on ObjectPAL? Partially because ObjectPAL is the backbone language to Paradox, and understanding the field types into which you can store data is important. The main reason to discuss these field types, however, is so that you can become familiar with which types of data can go into which types of fields. Later in this chapter, table field types and ObjectPAL data types will be discussed. For now, browse and study the following field types. Pay attention to the field types that are new to you. In particular, study what data can go in what field, and study each field type's maximum and minimum value limits. Understanding the type of data that can go into fields will help you better understand the ObjectPAL data types. The following list describes each field type.
Alpha (A) A general-purpose field type that can contain up to 255 letters, numbers, special characters, and other printable ASCII characters.
Autoincrement (+) This is a special field type that contains unique noneditable numbers. Paradox begins with the number 1 and adds one number for each record in the table. You can specify the starting number by specifying the minimum value during the creation of a table. Deleting a record does not change the field values of other records. This field type can be very useful when designing tables, and is especially useful for adding an artificial key to a table. (See "Primary Keys," in this chapter.)
BCD (#) Paradox BCD-type fields contain numeric data in a binary coded decimal (BCD) format. Use BCD fields when you want to perform calculations with a higher level of precision than that available with the use of other numeric fields (up to 32 digits after the decimal point). Calculations on BCD fields are not performed as quickly as those on other numeric fields. In Paradox, the BCD field type is provided only for compatibility with other applications that use BCD data. Paradox correctly interprets BCD data from other applications that use the BCD type. When Paradox performs calculations on BCD data, however, it converts the data to the numeric float type, and then converts the result back to BCD.
Binary (B) This is a field type used by programmers to store custom data that Paradox cannot interpret. For example, a binary field can be used for sound and animation. You can store any type of binary data in a binary field type, but Paradox does not know what to do with data in a binary field; you have to add routines to your program to use the binary data. You can specify the amount of the binary data stored in the table (0-240 characters).
Bytes (Y) Only advanced users who need to work with data that Paradox cannot interpret should use the Bytes field type because Paradox does not know how to interpret bytes fields. You can read and write the data in Bytes fields with ObjectPAL-for example, to store bar codes or magnetic strips. However, you will have to rely on other routines to manipulate the data (for example, functions in DLLs). Unlike binary fields, bytes fields are stored in the Paradox table (rather than in the separate .MB file), allowing for faster access. You can specify from 1-255 bytes. This field type is valid for Paradox 5 and 7 tables.
Character (C) A dBASE character field can contain up to 254 characters (including blank spaces). This field type is valid for dBASE III+, IV, and V. This field type is similar to the Paradox Alpha field type.
Date (D) Paradox Date fields can contain any valid date from January 1, 9999 BC, to December 31, 9999 AD. This field type is valid for dBASE III+, IV, V, and Paradox 3.5, 4, 5, and 7.
Float (F) dBASE provides two ways to store numeric data. The float number type contains numeric data in a binary floating point format. Use the float number type on fields that will not require precise calculations to be performed on them; some degree of precision is lost during calculation. Float number fields are best used to contain whole numbers or numbers of up to two decimal places. The size of a dBASE float number field can be from 1 to 20. This field type is valid for dBASE IV and V.
Formatted memo (F) Paradox formatted memo fields are like memo fields except that you can format the text. You can alter and store the text attributes typeface, style, color, and size. You can also specify the amount of data stored in the table (0-240 characters).
Graphic (G) Paradox graphic fields contain pictures in .BMP, .PCX, .TIF, .GIF, .JPG, and .EPS file formats. Not all graphic variations are available. Currently, for example, you cannot store a 24-bit .TIF graphic. When you paste a graphic into a graphic field, Paradox converts the graphic into the .BMP format. You can specify how much of the data is stored in the table (0-240 characters).
Logical (L) Paradox logical fields contain values representing True or False (yes or no). By default, valid entries include T and F (case is not important).
Long integer (I) Paradox long integer fields are 32-bit signed integers that contain whole numbers in the range -2,147,483,647-2,147,483,647. Long integer fields require more space to store than short fields.
Memo (M) This is a special type of BLOB field used for storing text. BLOB is an acronym for binary large object. A BLOB is not a field type, but rather a data type. Field types that can contain BLOBs include binary, memo (both Paradox and dBASE), formatted memo, graphic, and OLE. You can specify how much of the binary data is stored in the table (1-240 characters).
Money ($) Paradox money fields, like number fields, can contain only numbers. They can hold positive or negative values. By default, however, money fields are formatted to display decimal places and a money symbol. Regardless of the number of decimal places displayed, Paradox recognizes up to six decimal places when performing internal calculations on money fields.
Number (N) This is a field that can contain only numbers from -10e307 to +10e308, with 15 significant digits. A number field can contain some valid non-numerical characters, such as a decimal point or a minus sign.
OLE (O) Use the OLE field to store data generated by an OLE server, such as images, sound, documents, and so on. The OLE field provides you with a way to view and manipulate this data without leaving Paradox. Since version 5, Paradox has supported OLE 1 and OLE 2. You can specify how much of the binary data is stored in the table (0-240 characters).
Short (S) This is a Paradox field type that can contain integers from -32,767-32,767. A short number field type uses less disk space than does the number field type. It's perfect for storing ages, years of employment, invoice numbers, item numbers, and so on.
Time (T) Paradox time fields contain times of day, stored in milliseconds since midnight and limited to 24 hours.
TimeStamp (@) Paradox TimeStamp fields contain a value comprised of both date and time values. Rules for this field type are the same as those for date fields and time fields.
Tip: To enter today's date, current time, or both into a date, time, or TimeStamp fields, press the spacebar repeatedly until Paradox enters the data. Educate your users about this handy interactive feature.
BLOB Field Types
The binary large object (BLOB) field types are binary, memo, formatted memo, graphic, and OLE. The value you specify in the Create Table dialog box refers to the amount of the BLOB data Paradox stores in the table. The entire BLOB is stored outside the table in a file with the same name as the table but with a .MB file extension. For example, the BLOB data for My Table.db is stored in My Table.mb.
Notes on the dBASE Table Structure
The following section contains notes on various important features of the dBASE table structure. The intent is to let you know about some of the key differences between the Paradox and dBASE table structures.
dBASE Tables Mark Records for Deletion
In dBASE, records are marked for deletion by the active index. They are deleted only after you pack the table. If you never pack the table, it will continue to grow. This is an advantage; it however, however, it is also a disadvantage-but one that is easily overcome. You can put routines in your application to retrieve and purge deleted records. Just remember that dBASE tables must be purged of deleted records or they will continue to grow.
dBASE Allows Duplicates in the Main Index
Another difference is that with dBASE, you have the option of having duplicate key values in all the indexes except unique indexes. Even with a unique index, however, duplicate values are allowed in the table; duplicate values are prevented only in the index. Once a Paradox table is keyed, it will not allow duplicates.
dBASE Record Numbers Are Not Dynamic
When you delete a record in dBASE, the record still exists in the table until you pack the table from the Restructure Table dialog box. The record number sticks with the record until the deleted records are purged. To purge deleted records from a dBASE table, restructure the table and select the Pack Table check box.
In Paradox, the record numbers are put on a heap stack, and therefore, are dynamic; in dBASE, they are not. With Paradox tables, the record numbers reorder whenever you delete a record. To accomplish this, Paradox uses the concept of a heap stack. A heap stack is a technique to order a set of items in ascending order. In Paradox, the record number is a unique number that identifies each record and always starts with 1.
When you select Format | Filter for a Paradox table, the record numbers are reordered according to the new heap stack. When you do this same operation on a dBASE table, the record numbers for each record remain the same as before.
There are other less substantial differences between Paradox and dBASE tables. For example, the status bar and scroll bar behave differently, depending on which type of table you are displaying. For dBASE tables, the status bar shows you only what record number you are on-not what record number of how many, as in Paradox tables. Finally, the scroll bar behaves oddly when you use it with dBASE tables. The scroll box appears to stick to the center of the scroll bar. You can attribute this final oddity to the fact that dBASE tables do not use a stack heap and it is difficult for Paradox to estimate where the record pointer is in relationship to the whole table.
Using dBASE Expressions
One advantage that dBASE tables have over Paradox tables is that dBASE indexes are very flexible. You can write elaborate expressions using dBASE keywords in the Define Index dialog box (see Figure 2-7).
Figure 7: Entering dBASE expressions
The dBASE expression engine allows you to code expressions that give you a unique view to a table. For a complete list of the dBASE functions that are allowed in expression indexes on dBASE tables, look in the Paradox help under "dBASE:expression indexes". You'll have to consult dBASE manuals and books for usage. What follows here are some common examples:
DEPT+UPPER(LNAME) lists records by DEPT and LNAME fields. In this case, the index ignores case in the LNAME field.
UPPER(Field1)+UPPER(Field2)+UPPER(Field3) lists records by the Field1, Field2, and Field3 fields. In the following index, case is ignored completely.
The preceding examples of index expressions show you how flexible dBASE indexes are. You are limited to expressions that have a length of no more than 220 characters and result in no more than 100 characters.
Notes on the Paradox Table Structure
As an ObjectPAL programmer, you must keep in mind the things that are better accomplished in interactive Paradox-for example, at the table level. This section talks about the various Paradox table properties. It concentrates on using pictures to aid data development and to control data. This section also discusses how various concepts of referential integrity apply to Paradox tables.
Paradox Table Properties
The Paradox table properties are important. They allow you to restrict data input, use lookup tables, add secondary indexes to sort and view your data differently, add referential integrity to ensure the data in two tables stay linked, add password security, change the table's table language, and view the dependent tables associated with a table.
Following is a quick review of table property options:
Validity Checks These options control and guide the values a user can enter in a field. They enable you to set up default values, data input checks, required values, and more.
Table Lookup This is a data entry tool that ensures that data from one table is entered correctly in another table.
Secondary Indexes As discussed previously, secondary indexes are very useful in interactive mode and in ObjectPAL. Secondary indexes are useful for speeding up operations and viewing data in different ways, and have the following properties: Unique and Descending.
Referential Integrity This enables you to ensure data across two tables. With referential integrity, you can make sure that ties between data will not be broken. For example, one option enables you to prohibit the deletion of a master table record if the child table has corresponding records.
Password Security Full-password encryption at the table and field levels guarantees the security of your data.
Table Language The language driver for a table determines the table's sort order and the available character sets. The default table language is set by the BDE administrator. When you create or restructure a table, you can override the default table language with the Table Language option in the Table Properties panel in the Restructure dialog box. Paradox offers many language drivers, including several ANSI language drivers, that enable you to store Windows ANSI high characters.
Dependent Tables This table property shows all the tables that are recognized as children in a referential integrity link.
Tip: In the Restructure dialog box, you can use the Default column of the field roster to add a field called ModifyTimeStamp to every table in your project and set it's Default value to NOW. These types of audit fields can be very helpful in business situations and are critical in many. Other types of audit fields you can add to the end of tables include CreateTimeStamp and EmployeeID.
Picture Strings
A picture string is a pattern of characters that defines what a user can type into a field during editing or data entry of a table or form. A picture string aids data entry and promotes-but does not ensure-consistent data. Picture strings are an input aid and a means of validating data.
Although picture strings can be implemented on a field at the table or form level, they are best implemented at the table level. When you add a picture to a table, any forms you create using that table will inherit the picture string. Every ObjectPAL programmer needs to keep this in mind. If you must limit what someone can enter into a field, implement the limiting factor at the table level with a validity check, if possible. You should use ObjectPAL on a form only when you determine that a picture won't work. Remember, ObjectPAL is on the form only. Unless you password-protect your table, nothing can prevent the user from selecting File | Open | Table and entering data directly into the table; in other words, bypassing your ObjectPAL code. This concept applies to all database backends, including SQL servers. One of the basic tenets of developing a good client/server application is to put as much processing on the database server as possible.
Use the Picture Assistant dialog box to enter new pictures (see Figure 2-8). To get to the Picture Assistant dialog box, select the Picture Assist button on the Restructure Table dialog box.
Figure 8: The Picture Assistant dialog box
Table 2-1 lists the picture string characters you can use to validate data.
Character
Description
#
A numeric digit
?
Any letter (uppercase or lowercase)
&
Any letter (converts to uppercase)
@
Any character
!
Any character (converts to uppercase)
;
Interprets the next character as a literal character, not as a special picture string character
*
The character that follows can be repeated any number of times
[xyz]
The characters between square brackets are optional
{x,y,z}
Optional characters separated by commas
{}
Grouping operator
Alternative choices of values
Table 2: Picture String Characters
Any other characters you use in a picture string are treated as a constant. Paradox automatically types the constant when the user comes to a point in the picture string in which that constant is specified. The exception is the first character, which figures since in life there are almost always exceptions. The user must tell Paradox that he or she wants to enter data by entering the first character or by pressing the spacebar for autofill.
Tip: Remember, autofill is a great feature that you should promote to your users. To automatically fill in a date field with today's date, for example, just press the spacebar a couple of times. Make sure you add this type of help to any manuals or help files you develop for your application. Autofill works with Date, Time, Timestamp, and Logical fields and does not require a picture statement.
Useful pictures include the following:
Phone with autofill = ###-####
Phone with autofill (area opt) = [(###)]###-####
Phone w/1-800 optional = [{1-800-,(###)}]###-####
Flexible phone number = [1 (*3{#}) ]*3{#}-*4#
US 5 or 9 ZIP Code = #####[-####]
US 5, US 9, or Canada ZIP code = *5{#}[*4#],@#@ #@#
SSN with autofill = ###-##-####
SSN no autofill = ###{-}##{-}####
Letters only (no spaces) = *?
Letters only (capitalize first) = &*?
Capital letters = *&
Capital first letter = !*@
Capitalize every word 1 = *[![*?][* ]]
Capitalize every word 2 = !*[ * !,@]
Capitalize every word 3 = *{ ,.}!*{{ ,.}*{ ,.}!,@}
Capitalize every word 4 = *[[*#[ ]]![*?][@][ ]]
Capital after = !*[{ ,.,(,;,}*{ ,.,(,;,}!,@]
Time (HH:MM:SSJ = {0#,1#,2{0,1,2,3}}:{0,1,2,3,4,5}#:{0,1,2,3,4,5}#
Time with SS optional = {0#,1#,2{0,1,2,3}}:{0,1,2,3,4,5}#[:{0,1,2,3,4,5}#]
Time (HH:MM AM) or (HH:MM PM) = {1{:,{0,1,2}:},{2,3,4,5,6,7,8,9}:} {0,1,2,3,4,5}# {AM,PM}
Date with autofill = {##/##/##,#/##/##}
Date 2 = {##/01/##,#/01/##}
Allow Miss, Ms., Mr., or Mrs. = M{iss,s.,r{.,s.}}
Allow Dr., Doctor, Father, Miss, Mrs., Mr., Mr. & Mrs., Ms., Msgr., Pastor, and Reverend: = {D{r.,octor},Father,M{iss,r{s.,.[ ;& Mrs.]}, s{.,gr.}},Pastor,Reverend}
Demo Files: Available on the Internet at the following is PICTURES.TXT. You can use Notepad to open it and paste the settings into the Picture Assistant dialog box. The file is available at http://www.prestwood.com/forums/paradox/books/official/files/pictures.txt.
Using Table Lookup Effectively
When the user presses ctrl-spacebar to display a lookup table, the Lookup Help dialog box respects the table properties set for the table. You can use this feature to jazz up the way the Table Lookup dialog box looks. For example, you can change the colors of a table in a table window and the changes will carry over to the Table Lookup dialog box. The table properties you alter are stored in the TableView file with the same name as the table and a .TV extension. When you use that table in the lookup, it will appear with the new property values.
Tip: When users press ctrl-spacebar to display a lookup table, remember to let them know that they can use ctrl-Q to locate a value. This is an interactive feature that can greatly enhance the user's perception of your application.
Table Language and Sorting
How do you get support for special character sets in Paradox? For languages whose characters are written with an alphabet-not languages such as Chinese and Japanese-a language driver that supports the character set needs to exist.
In addition, the sort order of a table depends on the language driver. The three main language drivers with which we are concerned here in the United States are Paradox ascii, Paradox intl, and Paradox ANSI INTL. The Paradox intl (international) and Paradox ANSI INTL drivers sort alphabetically, mixing uppercase and lowercase; for example, aAbBBccC. The Paradox ascii (ASCII) driver sorts by the ASCII table, putting all the lowercase characters first; for example, aabbbcdAAABCCCD. It is important to know that the way in which Paradox sorts a table depends on the language driver.
Secondary Indexes
Earlier in this chapter, the importance of primary keys was addressed. A secondary index is used to sort a table by any of the fields in the table (not just by the primary key). A secondary index is a field or group of fields that you define as an alternate sort order for a table. You can use secondary indexes to view a table in a separate sort order, link a table on a field other than the primary key, and to speed up search and locate operations. A table can have more than one secondary index and each secondary index can be a composite of fields. When you use a secondary index, you change only the view order of the records. The physical location of the records in the table does not change. Secondary indexes are flexible and include the following options, Composite, Unique, Case-sensitive, Maintained, and Ascending/Descending.
Note: You cannot create a secondary index on a memo, formatted memo, binary, OLE, graphic, logical, or bytes field.
Referential Integrity
Referential integrity (RI) is extremely important. Point-and-click referential integrity enables you to set up relationships between tables so that your data is always valid. Data integrityis a guarantee that the values in a table are valid. You can use various features of Paradox tables to protect data from misuse including the required field option, the minimum and maximum options, and picture statements. For example, data integrity for autoincrementing might mean that no two records have the same key values. For referential integrity, data integrity might mean that the records in one table will always match the records in another table.
There are various types of referential integrity. Paradox supports Prohibit, Cascade, and Strict RI update rules. Figure 2.9 shows a referential integrity link being set up between LINEITEM.DB and STOCK.DB. To get to this dialog box, restructure the child table of a one to many, select the Referential Integrity tab, and select Add. Note the options selected in the Update Rule panel.
Figure 9: The Referential Integrity tab showing a link between LINEITEM.DB and STOCK.DB
Setting Up Referential Integrity
You must be restructuring the child table to create a referential integrity link. In addition, you must make sure that the child table contains all key fields of the parent. Besides controlling data entry, the referential integrity link provides an update feature: either Cascade or Prohibit.
Cascadeenables you to update child tables when a value changes in the parent table. With Cascade referential integrity, any changes that are made to the parent table's primary key are automatically made to the child table. Therefore, if you make a change to the parent table's primary key, the child table's foreign key also is updated. Referential integrity links don't need to be made on key fields in the child table. Whenever a change is made to the parent key, the change is cascaded to all the child records that match that key. If you change the spelling of a customer's name from Smith to Smythe, for example, the data in the reference field of all the child table records that consist of this key also will change.
Prohibitreferential integrity prevents a change in the parent's key if any records match the value in the child table. You can use Prohibit in an invoicing system to prevent the invoice numbers in existing line-item entries from being changed or deleted.
Strict Referential Integrity Checkbox
Strict Referential Integrity should have been called "Prohibit Paradox 4.0" because it prevents Paradox for DOS 4.0 and its earlier versions from writing to the table because they don't support referential integrity. When the Strict Referential Integrity option in the Update Rule panel is checked, Paradox 4.0 for DOS can't alter data (refer to Figure 2-9). This feature is designed entirely for interoperatability with Paradox for DOS.
When strict integrity is selected, Paradox for DOS sees the table as being write-protected. Therefore, the data can be viewed in Paradox for DOS, but can't be changed. If you are going to use referential integrity, and data security is important, then you should also turn on Strict Referential Integrity.
The Parent Must Use a Primary Key
One of the requirements to use referential integrity is that the parent table has a primary key. The child table must use all the fields of the parent table's primary key.
A parent table holds the master values for the link. It has the power to change these values, delete them, and add new values. A child table depends on the values in the parent table for its matching fields. Only the ones that exist in the parent table are available to a user to alter or view in a form.
Automatic Secondary Index
A referential integrity link can be defined between the primary key in a parent table and any index in a child table. The fields must match in number, type, and size. In other words, a parent table with two fields in its primary key must link to an index-primary or secondary-with two fields of the same type in the child table. You can use composite secondary indexes on the child table in a referential integrity link.
If Paradox needs to, it will automatically create any secondary indexes needed to establish a referential link. If Paradox needs a single field secondary index, it creates it Case-sensitive and Maintained and names it the same name as the field. If more than one field is needed, it names it the same name as the name you gave the referential integrity link.
Blank Field Caution
In referential integrity, blank fields are permitted to exist in the child, even when no matching blank field exists in the parent. Blank fields are considered to be outside the referential integrity link. This feature comes in handy when you want to enter a child record without matching it immediately to a parent record; you can add the link value later.
You should not blank out a primary-key value in the parent table, however. If you do, the corresponding fields in the child records are made blank as the change cascades from the parent. When you change the blank primary key to a nonblank value, the child records that were linked to it will remain blank. This is known as orphaning records. Remember, blanks in the master are considered to be outside the link.
To prevent orphaning of child records, restructure the table and use the Required Field validity check for all the key fields. In fact, it generally is considered good database design to make all the fields in a Paradox primary key Required Fields.
Note: Referential integrity and other settings stored in .VAL files sometimes interfere with the development of a project. This particularly is a problem when you are restructuring a child table in a referential integrity link. If this happens to you and you don't mind losing the validity checks for a table or for a set of tables, go ahead and delete or, better yet, rename them.
Be careful, however. All the validity checks will be deleted. For example, the ORDERS.DB, CUSTOMER.DB, LINEITEM.DB, STOCK.DB, and VENDORS.DB tables in the SAMPLE directory that comes with Paradox are a wonderful starting point for an invoicing system. The .VAL files-or more precisely, the table validity checks and referential integrity-might interfere, however, with you using the tables.
Summary
No matter which table structure you choose, use aliases and make sure that your data model is correct. Aliases are your ticket to portability. No matter what type of relationship you set up, your data model is simply a series of 1:1, 1:M, M:1, and M:M relationships. When you study a complex data model, examine the relationship between any two tables.
The form is a wonderful tool, but it isn't the place where data is stored: the table is. Therefore, the table is a much better place to implement data integrity. Set up data validity checks at the table level if possible. You can use pictures, table lookups, and referential integrity to implement data integrity. Only after you determine that a data restriction can't be implemented at the table level should you move to the form level and use ObjectPAL to manipulate a user's input.
Paradox
Print This
Tags:
KB Post: 
