| **Navigation:**  [[welcome to my product .htm|User's Guide and Tutorials]] > The Dictionary >====== Clarion's support of Database Design ====== | [[the basics of database design.htm|{{btn_prev_n.gif|Previous page}}]][[welcome to my product .htm|{{btn_home_n.gif|Return to chapter overview}}]][[dictionary editor.htm|{{btn_next_n.gif|Next page}}]] |
|  ||

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 - 2****nd**** 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)               !delete the ShipTo record** |
| **   IF ERRORCODE() THEN STOP(ERROR()).** |
| **   END** |
| **   DELETE(Order)                  !and delete the Order record** |
| **  IF ERRORCODE() THEN STOP(ERROR()).** |
| ** END                              !End Order table loop** |

**Nullify the Foreign Key**

Nullify the Foreign Key means that when the user attempts to delete the Primary Key record, or change the Primary Key value, the Foreign Keys that reference that Primary Key are changed to null values (if the Foreign Key fields allow null values).

Again using the tables defined previously, here is an example of how the executable code would look to Nullify the Foreign Keys on delete 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 ChangeNullify           !and nullify the Child records** |
| **     ELSIF Action = DeleteRec     !Check for Delete Action** |
| **       DO DeleteNullify           !and nullify the Child records** |
| **     END** |
| **       !other executable processing statements** |
| **  END** |
| ** END ** |
|  |
| **ChangeNullify  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 = 0                 !Change to null value** |
| **   PUT(Order)                     !and put the record back** |
| **  IF ERRORCODE() THEN STOP(ERROR()).** |
| ** END** |
|  |
| **DeleteNullify  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 = 0                 !Change to null value** |
| **   PUT(Order)                     !and put the record back** |
| **  IF ERRORCODE() THEN STOP(ERROR()).** |
| ** END** |

The Nullify option does not require as many changes as the Cascade option. This is because the Cascade has to delete all the related records in as many tables as are related. Nullify only needs to null out the individual Foreign Keys that reference the Primary Key being changed or deleted.

**Summary**

' Each data item should be stored once.

' Separate tables are used to eliminate data duplication.

' Tables are related by Primary and Foreign Keys.

' A Primary Key is a unique (and non-null) index into a table which provides for individual record access.

' A Foreign Key contains a reference to the Primary Key of some other table.

' One-to-Many table relationships are the most common. They are also referred to as Parent-Child and Many-to-One (same relationship, reverse view).

' One-to-One table relationships are most commonly created to hold data that is not always needed in every record.

' Many-to-Many relationships require a "Join" table which acts as a broker between the two tables. The Join table inserts two One-to-Many relationships between the Many-to-Many relationship.

' Only those Primary and Foreign Keys that the application needs (as a practical consideration) for specific access to the tables need to have Clarion KEYs declared.

' Referential Integrity means that all Foreign Keys contain valid references to Primary Keys.

' Maintaining Referential Integrity requires executable code that tests for Update or Delete of the Primary Key values.

' The three common solutions to maintaining Referential Integrity are: Restricting (update/delete not allowed), Cascading (also update/delete the Foreign Key), or Nullifying the Foreign Key (assign null values to the Foreign Key).

Continue to the [[dictionary editor.htm|Dictionary Editor]]