Clarion's support of Database Design

Top  Previous  Next

There are a number of methods of database organization in use today. The Inverted List Model, the Hierarchical Model, and the Network Model are three that have been widely used in the past. Mostly, these models have been used on mainframe computers, and have not been implemented on PC systems on a widespread basis. The Clarion language has the tools to allow you to utilize any of these methods, if you so choose.

By far, the most common method of database organization on PC systems today is the Relational Model, as defined by E. F. Codd. There is no database program that completely implements all of Codd’s rules regarding relational database, because it is an extremely complex mathematical model. However, most database programs implement a sufficient sub-set of Codd’s rules to allow practical use of the principles of the Relational Model. This essay is a very brief overview of the most fundamental aspects of relational database design as they impact business programming.

 

Relational Database Design

One basic principle of Relational Database involves the database design—a data item should be stored once—not duplicated in many places. There are two benefits to this: lowered disk space requirements, and easier data maintenance. To achieve this end, a relational database design splits the data into separate, related entities called tables. For example, assume a very simple order-entry system that needs to store the following data:

Customer Name

Customer Address

ShipTo Address

Order Date

Product Ordered

Quantity Ordered

Unit Price

This data could all be stored in each record of one table, but that would be very inefficient. The Customer Name, Address, ShipTo Address, and Order Date would be duplicated for every item ordered on every order. To eliminate the duplication, split the data into separate tables.

 

Customer Table:

Customer Name

 

Customer Address

Order Table:

ShipTo Address

 

Order Date

Item Table:

Product Ordered

 

Quantity Ordered

 

Unit Price

 

With this table configuration, the Customer Table contains all the customer information, the Order Table contains all the information that is pertinent to one order, and the Item Table contains all the information for each item in the order. This certainly eliminates duplicate data. However, how do you tell which record in what table relates to what other records in which other tables? This is the purpose of the relational terms "Primary Key" and "Foreign Key."

A Primary Key is an index into a table based on a field (or fields) that cannot contain duplicate or null values. To translate this to Clarion language terms: a Primary Key would be a unique KEY (no DUP attribute) with key components that are all REQuired fields for data entry. In strict relational database design, one Primary Key is required for every table.

A Foreign Key is an index into a table based on a field (or fields) which contain values that duplicate the values contained in the Primary Key fields of another, related, table. To re-state this, a Foreign Key contains a "reference" to the Primary Key of another table.

Primary Keys and Foreign Keys form the basis of table relationships in Relational Database. The matching values contained in the Primary and Foreign Keys are the "pointers" to the related records. The Foreign Key records in "Table A" point back to the Primary Key record in "Table B", and the Primary Key in "Table B" points to the Foreign Key records in "Table A."

Defining the Primary and Foreign Keys for the above example requires that you add some fields to the tables to fulfill the relational requirements.

 

Customer Table:

Customer Number – Primary Key

 

Customer Name

 

Customer Address

 

 

Order Table:

Order Number - Primary Key

 

Customer Number - Foreign Key

 

ShipTo Address

 

Order Date

 

 

Item Table:

Order Number - 1st Primary Key Component and Foreign Key

 

Product Ordered - 2nd Primary Key Component

 

Quantity Ordered

 

Unit Price

 

In the Customer Table, there is no guarantee that there could not be duplicate Customer Names. Therefore, the Customer Number field is added to become the Primary Key. The Order Number has been added to the Order Table as the Primary Key because there is no other field that is absolutely unique in that table. The Customer Number was also added as a Foreign Key to relate the Order Table to the Customer Table. The Item Table now contains the Order Number as a Foreign Key to relate to the Order Table. It also becomes the first component of the multiple component (Order Number, Product Ordered) Primary Key.

The Relational definitions of Primary Key and Foreign Key do not necessarily require the declaration of a Clarion KEY based on the Primary or Foreign Key. This means that, despite the fact that these Keys exist in theory, you will only declare a Clarion KEY if your application actually needs it for some specific table access. Generally speaking, most all Primary Keys will have a Clarion KEY, but fewer Foreign Keys need have Clarion KEYs declared.

 

Table Relationships

There are three types of relationships that may be defined between any two tables in a relational database: One-to-One; One-to-Many (also called Parent-Child) and its reverse view, Many-to-One; and Many-to-Many. These relationships refer to the number of records in one table that are related to some number of records in the second table.

In the previous example, the relationship between the Customer Table and the Order Table is One-to-Many. One Customer Table record may be related to multiple Order Table records. The Order Table and the Item Table also have a One-to-Many relationship, since one Order may have multiple Items. In business database applications, One-to-Many (Parent-Child) is the most common relationship between tables.

A One-to-One relationship means that exactly one record in one table may be related to exactly one record in another table. This is useful in situations where a particular table may, or may not, need to have data in some fields. If all the fields are contained in one table, you can waste a lot of disk space with empty fields in those records that don’t need the extra information. Therefore, you create a second table with a One-to-One relationship to the first table, to hold the possibly unnecessary fields.

To expand the previous example, an Order may, or may not, need to have a separate ShipTo Address. So, you could add a ShipTo Table to the database design.

 

Order Table:

Order Number - Primary Key

 

Customer Number - Foreign Key

 

Order Date

 

 

ShipTo Table:

Order Number - Primary Key and Foreign Key

 

ShipTo Address

 

In this example, a record would be added to the ShipTo Table only if an Order has to be shipped to some address other than the address in the Customer Table. The ShipTo Table has a One-to-One relationship with the Order Table.

Many-to-Many is the most difficult table relationship with which to deal. It means that multiple records in one table are related to multiple records in another table. Expand the previous example to fit a manufacturing concern, which buys Parts and makes Products. One Part may be used in many different Products, and one Product could use many Parts.

 

Parts Table:

Part Number – Primary Key

 

Part Description

 

 

Product Table:

Product Number - Primary Key

 

Product Description

 

Without going into the theory, let me simply state that this situation is handled by defining a third table, commonly referred to as a "Join" table. This Join table creates two One-to-Many relationships, as in this example:

 

Parts Table:

Part Number – Primary Key

 

Part Description

 

 

Parts2Prod Table:

 

 

Part Number - 1st Primary Key Component and Foreign Key

 

Product Number - 2nd Primary Key Component and Foreign Key

 

Quantity Used

 

 

Product Table:

Product Number - Primary Key

 

Product Description

 

The Parts2Prod Table has a multiple component Primary Key and two Foreign Keys. The relationship between Parts and Parts2Prod is One-to-Many, and the relationship between Product and Parts2Prod is also One-to-Many. This makes the Join table the "middle-man" between two tables with a Many-to-Many relationship.

An advantage of using a Join table is that there is usually some more information that logically should be stored there. In this case, the Quantity Used (of a Part in a Product) logically only belongs in the Parts2Prod table.

 

Translating the Theory to Clarion

In practical relational database design, a Clarion KEY may not need to be declared for the Primary Key on some tables. If there is never a need to directly access individual records from that table, then a KEY definition based on the Primary Key is not necessary. Usually, this would be the Child table (of a Parent-Child relationship) whose records are only needed in conjunction with the Parent record.

A Clarion KEY also may not need to be declared for a Foreign Key. The determination to declare a KEY is dependent upon how you are going to access the table containing the Foreign Key. If you need to access the Foreign Key records from the Primary Key, a Clarion KEY is necessary. However, if the only purpose of the Foreign Key is to ensure that the value in the Foreign Key field value is valid, no Clarion KEY is needed.

Take the previous theoretical examples and create Clarion table definitions (Note: if this is the first time that you are looking at Clarion table definitions, you will notice that the FILE entity is used to define tables. This is there for legacy code modules, and the terms are often used throughout the documentation interchangeably):

 

Customer  FILE,DRIVER('Clarion'),PRE(Cus)

CustKey   KEY(Cus:CustNo)     !Primary KEY

Record      RECORD

CustNo       LONG             !Customer Number - Primary Key

Name         STRING(30)       !Customer Name

Address      STRING(30)       !Customer Address

           END

         END

 

Order     FILE,DRIVER('Clarion'),PRE(Ord)

OrderKey   KEY(Ord:OrderNo)       !Primary KEY

CustKey      KEY(Ord:CustNo),DUP  !Foreign KEY

Record      RECORD

OrderNo      LONG                 !Order Number - Primary Key

CustNo       LONG                 !Customer Number - Foreign Key

Date         LONG                 !Order Date

           END

         END

 

ShipTo    FILE,DRIVER('Clarion'),PRE(Shp)

OrderKey   KEY(Shp:OrderNo)       !Primary KEY

Record     RECORD

OrderNo     LONG                  !Order Number - Primary Key and Foreign Key

Address     STRING(30)            !ShipTo Address

          END

         END

 

Item     FILE,DRIVER('Clarion'),PRE(Itm)

OrderKey  KEY(Itm:OrderNo,Itm:ProdNo) !Primary KEY

Record    RECORD

OrderNo    LONG                   !Order - Primary Component and Foreign Key

ProdNo     LONG                   !Prod. - Primary Component and Foreign Key

Quantity   SHORT                  !Quantity Ordered

Price      DECIMAL(7,2)           !Unit Price

         END

        END

 

Product    FILE,DRIVER('Clarion'),PRE(Pro)

ProdKey     KEY(Pro:ProdNo)       !Primary KEY

Record      RECORD

ProdNo       LONG                 !Product Number - Primary Key

Description  STRING(30)           !Product Description

           END

          END

 

Parts2Prod FILE,DRIVER('Clarion'),PRE(P2P)

ProdPartKey KEY(P2P:ProdNo,P2P:PartNo) !Primary KEY

PartProdKey KEY(P2P:PartNo,P2P:ProdNo) !Alternate KEY

Record      RECORD

PartNo       LONG                 !Part - Primary Component and Foreign Key

ProdNo       LONG                 !Prod. - Primary Component and Foreign Key

Quantity     SHORT

           END

          END

 

Parts     FILE,DRIVER('Clarion'),PRE(Par)

PartKey    KEY(Par:PartNo)        !Primary KEY

Record     RECORD

PartNo      LONG                  !Part Number - Primary Key

Description STRING(30)            !Part Description

          END

         END

 

Notice that only one Foreign Key (in the Order table) was explicitly declared as a Clarion KEY. A number of Foreign Keys were included as part of Primary Key declarations, but this was simply good fortune.

The Primary Key (Itm:OrderKey) defined on the Item table is there to ensure that an order does not contain duplicate Products Ordered. If this were not a consideration, Itm:OrderKey would only contain Itm:OrderNo, and would have the DUP attribute to allow duplicate KEY values. This would make it a Foreign Key instead of a Primary Key, and the table would not have a KEY defined for the Primary Key.

The Item table and the Product table have a Many-to-One relationship, which is One-to-Many looked at from the reverse perspective. This reverse view is most often used for data entry verification look-up. This means the Product Number entered into the Item table’s data entry procedure can look-up and verify the Product Number against the records in the Product table.

 

Referential Integrity

There is one more fundamental issue in the Relational Model that should be addressed: "Referential Integrity." This is an issue that must be resolved in the executable source code for an application, because it involves the active, run-time inter-relationship of the data within the database.

Referential Integrity means that no Foreign Key can contain a value that is not matched by some Primary Key value. Maintaining Referential Integrity in your database begets two questions which must be resolved:

• What do you do when the user wants to delete the Primary Key record?

• What do you do when the user wants to change the Primary Key value?

The three most common answers to each of these questions are: Restrict the action, Cascade the action, or (less commonly) Nullify the Foreign Key values. Of course, there may also be application-specific answers, such as copying all information to history tables before performing the action, which should be implemented as required in individual programs.

 

Restrict the action

Restrict the action means that when the user attempts to delete the Primary Key record, or change the Primary Key value, the action is only allowed if there are no Foreign Keys that reference that Primary Key. If related Foreign Keys do exist, the action is not allowed.

Using the tables defined previously, here is an example of how the executable code might look to Restrict deletes or a change of the Primary Key value.

 

 

ChangeRec EQUATE(2)               !EQUATE Change Action

DeleteRec EQUATE(3)               !EQUATE Delete Action value for readability

SaveKey   LONG                    !Primary Key save variable

 

CODE

SaveKey = Cus:CustNo             !Save Primary Key value

OPEN(window)

ACCEPT            

CASE ACCEPTED()                  !Process entry

                                 !individual control processing

OF ?OKButton                     !Screen completion button

 IF Action = ChangeRec AND Cus:CustNo <> SaveKey !Check for changed Primary Key value

  DO ChildRecordCheck

  IF ChildRecordExists

   Cus:CustNo = SaveKey         !change it back

   MESSAGE('Key Field changes not allowed!')  !tell the user

   SELECT(1)                                  !to start over

   CYCLE

  END

 ELSIF Action = DeleteRec      !Check for Delete Action

  Ord:CustNo = Cus:CustNo      !Initialize Key field

  GET(Order,Ord:CustKey)       !and try to get a related record

  IF NOT ERRORCODE()           !If the GET was successful

   MESSAGE('Delete not allowed!') ! tell user

   SELECT(1)                   !to start over

   CYCLE

  ELSE                         !If GET was unsuccessful

   DELETE(Customer)            !go ahead and delete it

   BREAK                       !and get out

  END

  !other executable processing statements

 END

END

END

 

 

Cascade the action

Cascade the action means that when the user attempts to delete the Primary Key record, or change the Primary Key value, the action cascades to include any Foreign Keys that reference that Primary Key. If related Foreign Keys do exist, the delete action also deletes those records, and the change action also changes the values in the Foreign Keys that reference that Primary Key.

There is one consideration that should be noted when you Cascade the action. What if the table you Cascade to (the Child table) is also the Parent of another Child table? This is a situation which you must detect and handle, because the Cascade action should affect all the dependent table records. When you are writing source code to handle this situation, you need to be aware of the table relationships and write code that Cascades the action as far it needs to go to ensure that nothing is "left hanging."

Again using the tables defined previously, here is an example of how the executable code might look to Cascade deletes or a change of the Primary Key value.

 

ChangeRec EQUATE(2)               !EQUATE Change Action

DeleteRec EQUATE(3)               !EQUATE Delete Action value for readability

SaveKey  LONG                     !Primary Key save variable

CODE

SaveKey = Cus:CustNo             !Save Primary Key value

OPEN(window)

 ACCEPT

  CASE ACCEPTED()                !Process entry

     !individual control processing

  OF ?OKButton                   !Screen completion button

    IF Action = ChangeRec AND Cus:CustNo <> SaveKey

                                 !Check for changed Primary Key value

      DO ChangeCascade           !and cascade the change

    ELSIF Action = DeleteRec     !Check for Delete Action

      DO DeleteCascade           !and cascade the delete

    END

      !other executable processing statements

 END

END

 

ChangeCascade  ROUTINE

Ord:CustNo = SaveKey             !Initialize the key field

SET(Ord:CustKey,Ord:CustKey)     !and set to process all of one

LOOP                             !customer’s orders

  NEXT(Order)                    !one at a time

  IF Ord:CustNo <> SaveKey OR ERRORCODE() THEN BREAK.

                                 !Check for end of cust. and get out

  Ord:CustNo = Cus:CustNo        !Change to new value

  PUT(Order)                     !and put the record back

 IF ERRORCODE() THEN STOP(ERROR()).

END

 

DeleteCascade  ROUTINE

Ord:CustNo = SaveKey             !Initialize the key field

SET(Ord:CustKey,Ord:CustKey)     !and set to process all of one

LOOP                             !customer’s orders

  NEXT(Order)                    !one at a time

  IF Ord:CustNo <> SaveKey OR ERRORCODE() THEN BREAK.

                                 !Check for end of cust. and get out

  CLEAR(Itm:Record)              !Clear the record buffer

  Itm:OrderNo = Ord:OrderNo      !Initialize the key field

  SET(Itm:OrderKey,Itm:OrderKey) !and set to process all of one

  LOOP                          !order’s items

   NEXT(Item)                   !one at a time

   IF ERRORCODE() = 33 THEN BREAK. !End of file check

   IF Itm:OrderNo <> Ord:OrderNo OR ERRORCODE() THEN BREAK.

                                 !Check for end of order

                                 !and get out of Item loop

   DELETE(Item)                  !and delete the Item record

  IF ERRORCODE() THEN STOP(ERROR()).

  END                            !End Item table loop

  Shp:OrderNo = Ord:OrderNo      !Check for ShipTo record

  GET(ShipTo,Shp:OrderKey)

  IF NOT ERRORCODE()             !If GET was successful

    DELETE(ShipTo)