Case Study: the MAST company

By default, Paradox includes a set of sample files, stored in the SAMPLE directory under the main Paradox directory. These files manage information for the fictitious Marine Adventures & Sunken Treasures (MAST) company. MAST sells diving equipment and arranges diving expeditions. Like most companies, MAST tracks information about customers, orders, inventory stock, and vendors. The company also tracks information about marine life.

MAST's customers are dive shops around the world. MAST sells supplies to these shops. This section of topics describes how MAST grew as a company and how Paradox filled MAST's information management needs at each stage of development. The purpose of this section is to give you an overview of the kinds of solutions Paradox can provide.

It describes six stages of the MAST company's growth:

  "Stage 1: a single table"
  
  "Stage 2: the need for a key"
  
  "Stage 3: organizing information into smaller tables"
  
  "Stage 4: creating multi-table documents"
  
  "Stage 5: creating and saving queries"
  
  "Stage 6: creating a report from a query"
  
Stage 1: a single table

Bill Budd founded MAST knowing that how he managed company information could mean the difference between success and failure. He needed a powerful database program for his personal computer, but did not want to invest a lot of time and money in learning a complex product. So, he bought Paradox, and found all the power he needed in an easy-to-learn Windows application.

The first table Bill created was all he thought he needed. He listed all the information he wanted to track, and created a table with a structure that looked like this:

Field name	Field type	Field size	Explanation

Customer	A	30	Name of dive shop

Address	A	50	Customer's address

Phone	A	15	Customer's phone number

Item	A	50	Item ordered

Qty	N		Quantity ordered

Price	$		Price of item

Date	D		Date of order

Delivery	D		Promised delivery date

Terms	A	6	Terms of payment

Part No	A	15	Vendor's part number

List Price	$		Vendor's price

Vendor	A	30	Vendor's company name

V Address	A	50	Vendor's address

V Phone	A	15	Vendor's phone number

Bill understood that different types of data should be defined as different field types, and he kept field sizes to a minimum to conserve disk space.

When a customer placed an order, Bill filled in the fields of the table, took the item from stock, shipped it, and ordered a replacement from the vendor. It all seemed to work pretty well.

At the end of each month, Bill performed a query on the table, and got an Answer table listing the orders placed during the month. He then created a report based on the Answer table to bill each customer.

But it soon became apparent that there were some problems with this system. He started getting phone calls from angry customers. A dive shop in Florida had received three separate bills for the three items they had placed on the same order. Another shop got a bill late because it had been sent to the wrong address. A customer in Hawaii got billed twice for the same order.

  When Bill began to get regular complaints, he realized it was due to inefficient information management. So, the MAST company moved to "Stage 2" and Bill created a key for the table.
  
Stage 2: the need for a key

A key ensures that each record of the table is unique, so customers could never be billed twice for the same item. Since no one field of his table was unique, Bill created a composite key. A composite key combines the values in two or more fields for the key's identification.

Bill decided that no two records would ever have the same combination of Customer, Item, and Date values. The combination of these values (the composite key) would have to be unique for each record. This made sense. How many times would a customer ever place more than one order for the same thing on the same day?

Bill also created a smarter report. He created a group band that grouped the records according to Customer. Using this design, even if a customer placed more than one order during the month, they received only one bill, listing each item.

So two of the problems with MAST's information management system were solved - a customer would never receive two bills for the same order, or separate bills for individual items on the same order.

  To solve the problem of an incorrect address, Bill needed a way to make sure the values he entered were accurate every time. It was time for MAST to move to "Stage 3" and organize the data into smaller tables.
  
Stage 3: organizing information into smaller tables

Bill realized that each time he entered information in the table, he risked making a mistake. He was entering the same information over and over again. A customer who placed 20 orders had been entered in the table 20 times. That meant 20 chances for error in the customer's name, address, or phone number. After reading the Paradox User's Guide, Bill realized he could work with his data more easily and maintain its integrity if he divided the original table into several smaller ones. When necessary, he could link them together.

This time Bill did some planning before he structured his tables. As he planned his tables, he realized that

  his database could be broken down into discrete subjects such as customers, orders, lineitems, stock, vendors,and contacts
  
  each table should contain an obvious field to use as a key
  
  to link tables, he'd need to duplicate some fields (and data) between his tables, but he should duplicate only the fields (and data) needed to link the tables
  
  fields that are duplicated between tables should use referential integrity to make sure their values match in all tables
  
  a table must have a key or secondary index defined on the linking field before it can be linked to another table
  
When finished planning, Bill created six tables to manage the data of his original table. These tables are some of the sample files included with Paradox.

  As Bill completed these tables, the MAST company continued to grow and moved into "Stage 4".
  
The Customer table

When planning the Customer table, Bill split the information about a customer's address into separate fields. This let him perform queries about specific states or countries. Bill learned that it is easier to work with small pieces of information separately than with large pieces of information. The Customer table looked like this:

Field name	Type	Size	Linking	Explanation

Customer No.	N		K*	Unique customer number

Name	A	30		Name of dive shop

Street	A	30		Street address

City	A	15		Customer's city

State/Prov.	A	20		State or province

Zip/Postal Code	A	10		Zip code or postal route

Country	A	20		Customer's country

Phone	A	15		Customer's phone number

First Contact	D			Date of first contact with customer

*	K = Key

Next, Bill completed the "Orders table".

The Orders table

When he created the Orders table, Bill knew he would need to link it to the Customer table so that he could easily associate an order with the person who ordered it. So, he duplicated the key from Customer (Customer No.) in Orders, then defined a secondary index on it. This meant the two tables could be sorted in the same order, and that would allow them to be linked.

When he did this, Bill realized that the value he entered as a Customer No. in Orders had to match a record in Customer; otherwise, the new Orders record was meaningless. It would have no customer associated with it. So, he defined referential integrity on the Customer No. field; this made Paradox check to find a valid customer before accepting a new order.

Bill also wanted to keep his order information separate from the item(s) being ordered. So, he included only information about the order itself in the Orders table. It looked like this:

Field name	Type	Size	Linking	Explanation

Order No.	N		K*	Unique order number

Customer No.	N		S**, R***	Secondary index and referential integrity to Customer No. in Customer

Sale Date	D			Date of order

Ship Date	D			Date to be shipped

Ship VIA	A	7		Cargo carrier used

Total Invoice	$			Cost of total order

Amount Paid	$			Amount paid so far

Balance Due	$			Due after partial payment

Terms	A	6		Terms of payment

Payment Method	A	7		Cash, charge, etc.

Month	A	3		Month order was placed

*	K = Key

**	S = Secondary index

***R = Referential integrity

Next, Bill completed the "Lineitem Table".

The Lineitem table

Bill wanted his customers to be able to order as many items at a time as they wanted. To do this, he created a separate table, called Lineitem, for the items being ordered.

The key of Lineitem is a composite of the Order No. and Stock No. fields; these fields also have secondary indexes, allowing Bill to sort the data by their values if he wanted to. This lets Lineitem link to Orders (using Order No.) and to Stock (using Stock No.). To protect the integrity of the Lineitem information, Bill defined referential integrity to the tables he planned to link to. Lineitem looked like this:

Field name	Type	Size	Linking	Explanation

Order No.	N		C*, S**, R***	Unique order number

Stock No.	N		C, S, R	Unique stock ID number

Selling Price	$			Price charged to customer

Qty	N			Number of items ordered

Total	$			Total of Selling Price * Qty

*	C = Composite key. In this case, the composite key is the combination of Order No. and Stock No.

**	S = Secondary index

***R= Referential integrity

Next, Bill completed the "Stock Table".

The Stock table

Bill also wanted a clear understanding of his stock on hand, so he could order products more efficiently. To do this, he created Stock so it could be linked to Lineitem and Vendors.

Stock No. is the key for this table. Since Stock No. is a secondary index in Lineitem, the two tables can be linked. Vendor No. is the primary key of Vendors, so it had to be a secondary index in Stock, and needed referential integrity to check against values in Vendor No. in Vendors. The Stock table looked like this:

Field name	Type	Size	Linking	Explanation

Stock No.	N		K*	Unique stock number

Vendor No.	N		S**, R***	Secondary index and referential integrity to Vendor No. in Vendors table

Equipment Class	A	30		Category of stock

Model	A	20		Vendor's model name

Part No.	A	15		Vendor's part number

Description	A	30		Quick description of item

Catalog Description	F	10		Full catalog description of item

Qty	N			Quantity in stock

List Price	$			Vendor's list price

*	K = Key

**	S = Secondary index

***R = Referential integrity

Next, Bill completed the "Vendors Table".

The Vendors table

Next, Bill created a table for his vendors. Here, as in Customer, he remembered to divide addresses across fields. Vendors looked like this:

Field name	Type	Size	Linking	Explanation

Vendor No.	N		K*	Unique vendor ID number

Vendor Name	A	30		Company name

Street	A	30		Street address

City	A	20		Vendor's city

State/Prov.	A	20		State or province

Country	A	15		Vendor's country

Zip/Postal Rt	A	10		Zip code or postal route

Phone	A	15		Phone number

FAX	A	15		FAX number

Preferred	L			Indicate preferred status

*	K = Key

Using this system, Bill knew that a customer's information was entered only once, and then referred to by other tables. Likewise, order, item, stock, and vendor information was entered only once. The chance for errors was substantially reduced.

When all the tables were created, Bill used INSERT queries to move his existing data into the new structures. He knew he would probably never have to link all the tables, only a few at a time, but he wanted to see what his company's data model looked like with all the changes he had made. He quickly linked the tables, and saw the data model shown in the following figure:

Finally, Bill completed the "Contacts Table".

The Contacts table

Finally, Bill needed a way to keep track of who to reach at each customer site. So, he created a Contacts table and filled it with the name and telephone number of the person he talked to the most often at each site. Contacts looked like this:

Field name	Type	Size	Linking	Explanation

Last Name	A	10		Contact's last name

First Name	A	20		Contact's first name

Company	A	30		Contact's company name

Phone	A	15		Contact's direct phone

With Contacts, Bill was able to keep track of information that did not need to fit into his data model. He kept its information separate and was able to protect the privacy of his contacts and their direct phone numbers.

  The MAST company continued to grow and moved into "Stage 4", creating multi-table documents.
  
Stage 4: creating multi-table documents

MAST was really taking off, and Paradox supported its growing information management needs. Bill needed some powerful data entry forms and reports to maximize his data model.

A data entry form

To enter new orders, Bill created a form linking Customer, Orders, and Lineitem. Paradox used the keys and secondary indexes to figure out how to link the tables. The form Bill created is shown in the following figure:

Bill let Paradox calculate the value of Total Invoice in Orders from the Selling Price and Qty fields of Lineitem. He right-clicked the Total Invoice field, defined it as a calculated field, and set up the calculation Lineitem.Selling Price * Lineitem.Qty. From then on, Paradox totaled the invoice for him.

A multi-table report

Besides being able to enter orders more efficiently, Bill found he could communicate more effectively with his customers. For example, he created a multi-table report to show his customers their buying practices. The report linked Customer to Orders and presented information from both tables in a standard letter format.

  At this point, the MAST company moved into "Stage 5": creating and saving queries.
  
Stage 5: creating and saving queries

As MAST grew and evolved, Bill needed to know more about the information in his tables. He found he was constantly using queries to ask questions about his data. For example,

When he considered opening a new branch, he asked questions such as these:

  How many customers do we have in each state?
  
  Which state generates the most revenue for us? 
  
  Where are our vendors most centrally located? 
  
  Is there a correlation between vendor location and customer location?
  
When Bill considered changing his domestic long-distance telephone service, he asked these questions:

  How many customers do we have in this state?
  
  How many in this time zone? 
  
  How many in this country? 
  
  What percentage of our phone calls are international?
  
Each month Bill performed the same queries, to see how sales compared to the previous month, the previous quarter, and the previous year. He could then calculate forecasts for the coming months. He learned early that he could save a query and reuse it month after month.

  Then the MAST company moved into "Stage 6".
  
Stage 6: creating a report from a query

Bill had always run a query and then created a report based on the Answer table to print his results. One day, he noticed he could create a report based on a saved query. This was interesting. He opened a new report, chose Queries from the File Type list box in the Data Model dialog box, chose the query he ran each month, designed the report, and then saved it. Each time Bill printed his new report, Paradox automatically ran the query and printed the results. Bill found he could print the latest information almost effortlessly; Paradox was now doing even more work for him.









