Designing the Tastrade Database

Article
10/23/2006

The key tasks in designing the database were:

Creating table structures and relationships.
Implementing security.
Maintaining data integrity.

Creating Table Structures and Relationships

The process of creating table structures and relationships in Tastrade is discussed in Designing Databases. To see the tables and relationships in the Tastrade database, open the Database Designer.

Because the central entity in this application is an order, the table involved in the most relationships is the Orders table. Other entities directly or indirectly support an order:

A customer places an order.
An employee creates or "takes" an order.
A shipper delivers the order.
Orders contain line items, which, in turn, contain products.

Two tables, Suppliers and Category, support the Products table although they do not directly supply information for an order. Suppliers are the source of the products while the Category table provides information that can be used to manage the products. In Tasmanian Traders these entities aren't very significant. In other applications, for example, an inventory management application, they would be.

In addition to the logical design of the database, security and data integrity are important when creating the database.

Implementing Security

Database security involves restricting access to data. Unauthorized users should not have access to any data while other users should not have full access to certain data. For example, you might not want order-entry personnel to be able to change employee information or customer credit limits.

To implement security, two fields were added to the Employee table: Group_ID and Password. To gain access to information in the database, a user must complete a login form and supply the appropriate password. When the user logs in, Tasmanian Traders can determine the access level of that user from the value in the Group_ID field.

Because a login form is a common element in many applications, a class was created for the login form: Login in Login.vcx. This form class contains text boxes for a user to enter a user ID and a password. Properties of the class are used to specify the table and the field for the password. Generic code is called in the Click event of the OK command button to validate the password the user entered. A property of the form (uRetVal) is set on whether a user entered the right password.

For this application, a subclass, LoginPicture, was created for the Login class. LoginPicture includes all the functionality of the Login class, plus a picture of the user, a text description of the user, and a hint for the password. None of these objects would be desirable in a login form outside the context of Tasmanian Traders.

A login form provides a level of security in a stand-alone application, but because any user with a copy of Visual FoxPro can open the database and view or change data outside of the application, the data isn't really very secure. In a real-world context, network security and/or data encryption would be used to increase security. In the world of this sample application, however, all Visual FoxPro users should have access to the data from inside or outside the application. Therefore, the login user names and passwords need to be apparent to anybody logging in to the application, and the data is not encrypted by the application.

Maintaining Data Integrity

Protecting data integrity involves making sure that authorized users don't add, change, or delete data that would make parts of the database invalid or inaccurate.

Data integrity management at the database level applies whether a user is changing values through the forms in the application or directly in a Browse window. Thus, data integrity, in the form of referential integrity and enforcing business rules, is handled in the database.

Referential Integrity

Referential integrity was implemented at the database level using the Referential Integrity Builder. For all parent tables in the database, when a key value is changed, that change is also made in the child tables (Cascade on Updating). When a user deletes a record in a parent table, the associated records in the child tables are also deleted (Cascade on Deleting). Users cannot delete records in other parent tables if there are related records in a child table (Restrict on Deleting). A user cannot insert records into child tables that don't match records in the parent tables (Restrict on Inserting).

Each of the tables in Tastrade (except for Order_Line_Items) has a primary key field. Because Visual FoxPro requires a non-null and not empty value for this field and automatically prevents a duplicate value from being entered in the field, no code has to be written to prevent duplicate primary keys. To make it even easier for a user, however, NewID( ), a stored procedure, generates a new value for the primary keys of most tables. The next primary key value for each appropriate table is stored in the Setup table. NewID returns this value and calculates a new value for the next available key. NewID( ) is the default value (set in the Table Designer) of Orders.Order_id, Products.Product_id, and other primary key fields in the database.

For the Customer table, however, it would not be worth the time and effort to generate new Customer_id values for the user. The user must supply the primary key value for a new record added in the Customer or Add Customer form. The primary key for the Customer table is Customer_id. The naming scheme for customer identification is based on the name of the company. For example, the Customer_id for the B's Beverages company is BSBEV.

Business Rules

The Tastrade database needed a rule to ensure that orders couldn't be saved without at least one associated line item. The stored procedure ValOrder( ) manages this requirement. In addition, ValOrder( ) makes sure that the order total is not above the customer's Max_order_amt or below the customer's Min_order_amt. In Tasmanian Traders, if an order total is above the minimum or maximum order amounts for a customer, a message box gives the user the option to override the rule. In a stricter data scenario, you wouldn't let a user to override the rule.

ValOrder( ) calls the RemainingCredit( ) and CalcMinOrdAmount( ) stored procedures. Because the database doesn't store information about which orders a customer has or has not paid for, it isn't possible to get information on a customer's outstanding balance. The RemainingCredit( ) procedure assumes that all orders are outstanding.

Form-Level Validation

Form-level data validation includes restricting user access to data by enabling and disabling appropriate controls. Optimistic row buffering is set at the form level for all data-bound forms. The Order Entry form has optimistic table buffering set for the table displayed in the grid. The optimistic row and table buffering is used to revert records when users decide that they don't want to save their changes.

Database Maintenance

To make it possible for a user to reindex the tables and check the validity of the database, the Rebuild form was added to the application. The Rebuild form uses the REINDEX and the VALIDATE DATABASE commands.

As an alternative to using the REINDEX command, you could extract the index expressions using the TAG( ) and KEY( ) functions, delete all the tags with DELETE TAG ALL, and recreate all the indexes using the INDEX ON command. This method would minimize the .cdx file.

Comments on Creating the Database

During development, some of the table names and field names were changed to illustrate the long name capabilities of Visual FoxPro. Consequently, views and indexes had to be recreated, and the ControlSource property of bound controls on forms and reports had to be manually changed.

After login, users originally had to select an action from a menu on an otherwise blank screen. Later in the development process, the Startup_action field in the User_level table was added. Information in this field is used to specify the part of the application that should be run right after login.

There are two free tables (not included in the database) in the Tastrade project: Behindsc and Repolist. Behindsc is the table used to store Behind the Scenes information, which describes how specific functionality in the application was implemented. It does not contain data for use by the application. Repolist is a table listing available reports, along with a descriptive name and a type for each report. These tables are used internally by the application; they do not constitute part of the data stored in the database by the user. Either of them could have been included in the database, but were not because they work independently of the data that is stored in the Tastrade database.