Clarion Community Help

As a general rule, every minute you spend planning your application beforehand saves you ten later. This topic informally describes the planning process for the application you'll create in the subsequent chapters. In the real world, you'll probably create a bona fide functional specification for your important applications. This informal description defines:

·The tasks the application performs.

·The data the application maintains, and how it stores it.

As a starting point, this Application Generator lesson application uses the data dictionary from the applications you created in the Getting Started manual. It extends the concept to a simple Order/Entry system, using the data dictionary for keeping track of customers.

Defining Application Tasks

This application will maintain the customer and billing tables for a manufacturing company. The first task in planning just what the application will do is to assess what the company expects it to do.

For the purpose of the subsequent lessons, the application we'll create is a simple order entry system. Customers typically phone in orders for one or more products at a time. A salesperson takes the order. The billing department prints an invoice that night.

The application therefore must provide:

·Entry dialogs for taking the order, or modifying the data in it later.

·Access to the customer list from within the order entry dialogs. The customer list is the one you created in the Getting Started lessons, stored in the Customer table.

·Access to the list of part numbers (items) that the company manufactures, from the order entry dialogs.

·Browse windows for listing sales transactions.

·Procedures that will maintain the Products list and Customer information.

·Printed reports.

Designing the Database

The first task in planning the table structure is to assess what data the application needs, and how to store it with the minimum amount of duplication.

Good database management maintains separate data tables for each “entity” or group of discrete data elements. The data “entities” this application maintains are:

The Customer Table

The Customer table stores “constant” data such as customer names and addresses. It's most efficient to store this data in one place, allowing for a single update when the information changes. This also saves space by eliminating redundant customer information in the Orders table; otherwise, if there were 1000 orders for company XYZ, the address information would be repeated 1000 times. Reducing storage requirements by storing the data only once is called normalization.

The customer data requires a column to uniquely identify the customer. The company name is unsuitable because there could be duplicates. There may be, for example, multiple rows for a customer called “Widget Depot,” if it has multiple locations. The CustNumber column is a good candidate for an auto-number key which automatically creates and stores unique customer numbers.

The CustNumber column also serves as the primary key for the data table. Any other data tables which are related to the Customer table must declare the CustNumber column as a foreign key. A primary key is a column, or combination of columns, that uniquely identifies each row in a data table. A foreign key is a column, or combination of columns, in one table whose value must match a primary key's value in another related table.

Because there may be many orders for each customer number, the relationship between the Customer table and the Orders table will be a one to many (1:Many) relationship. We say the customer data table is the parent table, and the Orders data table is the child table.

 

The Phones Table

The Phones table stores telephone numbers'each customer could have several. Each row includes a CustNumber column to relate back to the Customer table.

The Phones table also includes a text column in which we can indicate whether the phone number is an office, fax, mobile or home number. Using the data dictionary, we'll specify that the control for entering data for this column should be a drop-down list with the choices already loaded.

 

The Orders Table

The Orders data table gathers information for each sales transaction from all the other data tables (such as the customer data). Because much of the basic data in this table prints in the “header” area of the invoice, this is sometimes called the Order Header.

Every sales transaction requires one row in the Orders table. The row relates to the customer information by referencing the unique customer number. Because some order rows may reference one product, and others may reference ten, you'll create a separate Detail table which relates back to a unique order number. This creates a one to many relationship, with the Orders table as parent and Detail as child. The actual products ordered are identified by their product codes, in the Detail table.

The Orders row thus holds a customer number to relate back to the customer data (the foreign key), and a unique order number to relate to the Detail. You'll create a multi-component primary key on the two columns, so that you can easily create a browse sorted by customer and invoice number.

The Detail Table

The Detail table stores the products ordered by their product codes (a foreign key into the Product table), their individual prices, the quantity of each, and the tax rate. An additional column holds an invoice number, which relates back to the Orders table in a many to one relationship.

The Detail table duplicates the price information with the columns in the product table; this is because prices may change. It's important to store the price column within the detail table row because if the price increases in six months, today's paid in full invoice would reflect a balance due.

The Product Table

The Product table stores unique product numbers, descriptions, and prices. When the sales person looks up a product by name, the application inserts the product number into the Detail row. The product code is the primary key'no two items can have the same code, and every product must have a code. An additional column contains the tax rate for the product.

Referential Integrity

Referential Integrity refers to the process of checking all updates to the key column in a given table, to ensure that the validity of parent-child relationships is correctly maintained. It also refers to ensuring that all child table rows always have associated parent rows so that there are no “orphan” rows in the database.

Because the data for a given transaction resides across several tables, this application must enforce referential integrity. This is critical, yet many database application development tools require you to hand code procedures to enforce this. The Application Generator's templates implement this automatically in your generated source code when you select a few options in the Data Dictionary.

It is essential that the application not allow an update to a row that leaves a blank or duplicate value in a primary key column. For example, we need to restrict the ability of the end user to update a row in a way that could cause a duplicate Customer number. If two different companies shared a duplicate Customer number, you could send a bill to the wrong company.

The Complete Database Schematic

The schematic below provides an overview of the entire database. If you look at it from the point of view of the sales agent taking a phone order, the Orders table stores who's ordering, the Detail stores what they're ordering, and the Customer and Product tables store constant information about the customers and products.

The item code looks up the description and price. The customer code looks up the customer's name and address. Other data, such as the transaction date, fill in automatically (by looking up the system date, for example).

Finally, the following lessons will create a brand new data dictionary, and you will copy and paste the tables that Getting Started defined for you into the new dictionary.

As for the actual application you create, because the lessons are a teaching tool more concerned with showing what Clarion can do for you, it won't create a full-scale order entry system. However, you will find that some parts of the application will be very “showy,” so that you can quickly learn how to do equivalent procedures in your applications.

Application Interface

The next major task before coding is to plan the user interface. For a business application like this, it's crucial that a salesperson quickly locate the data they need, so that they can record the sale and move on to the next phone call. Therefore, the application should put all the important data “up front” by default, and no entry or maintenance dialog should be more than a button or menu command away.

Additionally, the company uses many other Windows applications; therefore, it's especially important that the application have a standard windows “look and feel.” End users learn a familiar interface more quickly.

To implement the tasks the application must execute in a consistent manner with our guidelines, we can plan for the items listed below. Though the following is no substitute for a real program spec, it should suit us for the upcoming lessons.

·Because the application will handle the maintenance for the customer, item, and billings tables on different forms, the Multiple Document Interface (MDI) is necessary.

·The application should have a toolbar with buttons to load forms and browse windows, and to control the browsing behavior.

·To maintain a consistent “look and feel,” the main menu choices will be File, Edit, View, Window, and Help. The File menu accesses the printing and exit procedures. The Toolbar buttons call the form dialogs for editing a current row (if highlighted in a browse) or adding/deleting rows, and for browsing through the tables. The Browse menu calls the procedures for browsing tables. Window and Help perform standard actions.

·When adding new orders, the sales people should be able to pick customers and products from scrolling lists. Pertinent data in the order dialog'addresses, item descriptions and prices'should automatically “fill in” as appropriate.

OK, What Did I Just Do?

Here's a quick recap of what you just accomplished:

Now that the application description is fairly complete, we're ready to begin work. The first step is to create the data dictionary.

 

Click here to jump to the next lesson