This follows on from
Learning Clarion (Part 1)
Lesson 3: Adding Tables and Columns
This lesson teaches you how to:
- Create new table definitions.
- Create a pool of column definitions from which new column definitions can be easily derived.
- Create additional column definitions for all tables.
A
label may only contain letters, numbers, and the underscore(_) or colon (:) characters, and must begin with a letter or underscore. Executable code statements use this label to refer to the table or field.
Defining New Tables
We have already copied and modified the three tables defined in the Getting Started dictionary. Now we are ready to add a new table from scratch. Please open the LCLesson dictionary file.
Click on the "Add Tables" button and fill in the details as shown for the
Phone table. The prefix is automatically derived from the first 3 letters of the table name. Since the other prefixes are all in caps, change this one to caps too.
The prefix is one way to uniquely identify columns of the same name in different tables. For example, PHO:CustNumber is the CustNumber column in the Phone table, while CUS:CustNumber is the CustNumber column in the Customer table. You can also uniquely identify columns by using Field Qualification syntax (discussed in the Language Reference).
Click on the "Comments" tab and add in some further information about this table: "Customer Phone table". Comments are a helpful way of documenting your data structure. A description of what the table is for can be very helpful for when you return to the table for maintenance programming. Click "OK" to add the table.
Use the "Add Table" button to create the
Detail table. Because it is a child table, it is sometimes useful to include the parent table name in the description, as shown here: "Order Details". We do not have to stick to the default prefixes, and they are not restricted to 3 letters. You could change it to "DTL" as shown, but we will leave it as "DET" for future lessons. Click "OK" when done.
Use the "Add Table" button to create the
Product table as shown. Leave the prefix as "PRO". Click "OK" when done.
Save your work using the "Save" button.
Defining the Columns
Several tables use related fields, so it makes sense to create a pool of field definitions that we can use in multiple tables, to ensure that they are all consistent. Fields like "CustNumber" and "OrderNumber" are good candidates because they occur in several tables.
Click on the "Pools" folder, and use the "Add Pools" button to create "ColumnPool" with a prefix "POOL" as shown above. Click "OK". Pools do not generate any code.
We are going to copy the pool columns from their existing tables and add them to the pool by pasting them.
Select the CustNumber field, right-click and choose "Copy".
Click on "ColumnPool", then right-click on "Columns" and choose "Paste".
Return to the "CustNumber" field in the "Customer" table, and fill in the "Derived From" field as "POOL:CustNumber". Then click on the ellipsis button to the right.
The Set Freeze Checkbox dialog pops up. Press "Don’t Freeze" to
allow changes in the Derived Column to cascade changes in the ColumnPool column if they are made.
This means that the CUS:CustNumber column is now derived from the POOL:CustNumber column. The term "derived from" means that the POOL:CustNumber column’s definition is the "parent" and all "children" columns which are "derived from" that column automatically share all the attributes of the parent.
Deriving column definitions from existing columns gives you the ability to make changes in only one place, then cascade those changes to all derived columns. For example, if the definition of the CustNumber column needs to change in all tables using it, simply make one change to the POOL:CustNumber column definition, then cascade that change to all the derived CustNumber columns in all tables.
Go to the Order table, click on "CustNumber", click on the ellipsis next to the empty "Derived From" field. This will allow you to choose any field, so go to the "ColumnPool" and choose "CustNumber". Click "Select", and "Don't Freeze".
Return to the "ColumnPool" and click the "Add" button. Fill in the details for OrderNumber as shown. These are a bit different from the original "Order" table, so pay close attention. (Alternatively, you could use the ellipsis button to "import" the details from the "Order" table's "OrderNumber" column, and then remove the "Derived from" information and change the Data Type to "LONG" and the Screen Picture to "@n_6".) Click "OK".
Fill in the details for "ProdNumber", which we will use as the PK in the "Product" table, as well as part of the "Detail" table. Click "OK". You should get an empty "Column Properties" dialog box. We don't need to add any more columns to the pool, so click "Cancel".
Go to the "Order" table and click on the "OrderNumber" column. Note how its current Data Type is "SHORT" and its Screen Picture is "@n-7". Click in the "Derived From" field and type in "POOL:OrderNumber" and press the Tab key. Choose "Don't Freeze". Notice how the Data Type and Screen Picture have both changed to the properties of POOL:OrderNumber. Click on the "Save Current File" button (Ctrl-S).
The Phone Table
Click on the "Phone" table, then on the "Columns" folder. Click the "Add" button and then the ellipsis button to the right of the "Derived From" field. Choose the "
CustNumber" field in the column pool and click "Select", "Don't Freeze", "OK".
The next field is "
PhoneType" and is a string field of 6 characters. It will contain words like "Home", "Work", "Fax", "Cell", etc.
In the "Attributes" tab, set the "Initial Value" to 'Home' (including the single quotes) to create a default value.
Go to the "Validity Checks" tab, click on "Must be in List" and type in the following in both the "Values" and "Choices" fields: "Home|Work|Fax|Cell|Mobile|Other". Each choice is separated by a vertical bar, and the user will select from a list.
When you specify a "Must be in List" option, the default window control for the column is an OPTION structure with RADIO buttons. These appear by default in the Window Controls list. In the "Controls" tab, change the "Control Type" to "LIST". Click "OK".
The next field is "
OutsideUSA". Change the "Data Type" to "BYTE".
Set the "Initial Value" to 0 in the "Attributes" tab.
Choose "Must be True or False" in the "Validity Checks" tab. Set the True Value to 1 and the False Value to 0. The default control is a checkbox. Click "OK".
Type "
PhoneNumber" in the "Column Name" entry. The "Data Type" is "STRING" of 20 Characters (to allow for the occasional international number). Type in "@P(###)###-####P" (without the quotes) in the "Screen Picture" field. This will set a "mask" for domestic US phone numbers. We’ll use the OutsideUSA column in our application to change the picture later to accommodate international phone numbers. Click "OK".
The last column for this table is "
Extension", and it is 10 characters long. Click "OK", then "Cancel". Remember to save your work.
The Detail Table
Go to the "Detail" table, click on "Columns", click the "Add" button, then the ellipsis on the right of the "Derived From" field, and select "
OrderNumber" from the column pool and click on "Select", "Don't Freeze", "OK".
Click on the ellipsis button and choose "
ProdNumber" this time. Click "Select", "Don't Freeze", "OK".
Create the column "
Quantity" as a "SHORT" data type (Two byte signed integer, with a range from -32,768 to 32,767) and click "OK".
Create the column "
ProdAmount" as a "DECIMAL" data type with 5 characters and 2 decimal places. Click "OK".
Create the column "
TaxRate" as a "DECIMAL" data type with 2 characters and 2 decimal places. Click "OK", then "Cancel". Save your work.
The Product Table
Go to the "Product" table, click on "Columns", click the "Add" button, then the ellipsis on the right of the "Derived From" field, and select "
ProdNumber" from the column pool and click on "Select", "Don't Freeze", "OK".
Create the column "
ProdDesc" as a "STRING" data type with 30 characters. Click "OK".
Create the column "
ProdAmount" as a "DECIMAL" data type with 5 characters and 2 decimal places. Click "OK".
Create the column "
TaxRate" as a "DECIMAL" data type with 2 characters and 2 decimal places. Click "OK", then "Cancel". Save your work (Ctrl-S).
Make a backup of your work.
OK, What Did I Just Do?
Here’s a quick recap of what you just accomplished:
- You created new table definitions.
- You created a pool of column definitions from which new column definitions can be easily derived.
- You created the column definitions for all the tables.
Next we’ll go on to add keys and table relationships.
Lesson 4: Adding Keys and Relations
Now that all the tables are defined, we can add keys then specify the table relationships. You already have defined the keys for the two tables you created in the GSLesson application in the Getting Started lessons. In this chapter, we’ll define keys for the remaining tables.
The columns in the "
Order" table that relate to other tables in the database are the "OrderNumber" and "CustNumber" columns. The "
CustNumber" column relates to the "Customer" table (purple line above). There will be duplicate values in the CustNumber column of the "Order" table that relate to rows in the "Customer" table. The key we defined in the "Order" table is a foreign key. The "Customer" table key does not allow duplicates and nulls, and was defined as the primary key for that table.
Multiple "Order" rows can exist for each "Customer", making this a Many-to-One relationship: the "Order" table is the
child of the "Customer" table. The "Order" table was imported from the Getting Started lessons, and there are two keys already defined that satisfy the above requirements. They are both called "KEYCUSTNUMBER"
Now let's turn to the relationship shown in red in the first graphic. The "
OrderNumber" column is also the primary key (PK) of the "Order" table. Each Order row is uniquely numbered by the "OrderNumber" field, irrespective of which customer placed the order. Similarly, every row in the "Detail" table must belong to one and only one "Order". So we can say that the "OrderNumber" column relates to the "Detail" table. There should be no duplicate or null order numbers in the "Order" table; this is a primary key.
There may be multiple "Detail" rows for a single matching OrderNumber. Therefore, this is a One-to-Many relationship: the "Order" table is the
parent of the "Detail" table.
Also, there is a relationship between the "Product" table and the "Detail" table (blue line). The "
ProdNumber" column relates to the "Product" table. There will be duplicate values in the "ProdNumber" column for the rows in the "Detail" table because a product can be sold multiple times on different orders, assuming there is sufficient stock. There may be more than one Detail row containing a single ProdNumber. Therefore, this is another Many-to-One relationship, with the "Detail" table the
child of the "Product" table.
Defining the Keys
The "Order" table already has the "KEYORDERNUMBER" (primary) key, but we need to define keys for the "Product" and "Detail" tables. Let's start with the "Product" table.
Select the "Product" table, click on "Keys", and then the "Add" button. The key label is "KEYPRODNUMBER", and it must be unique, and it is the primary key, as shown. Then click on the "Add Field" button and choose "
ProdNumber". Click on "Select", "Cancel", "OK", and "Cancel". We now have a primary key for "Product".
Select the "Detail" table, click on "Keys", and then the "Add" button. The key label is "KEYORDERNUMBER", and its description is "Order Number", as shown. Then click on the "Add Field" button and choose "
OrderNumber". Click on "Select", "Cancel", "OK".
The second key is "KEYPRODNUMBER" and its description is "Product Number". Click on the "Add Field" button to choose "ProdNumber". Click "Select", "Cancel", "OK", and "Cancel".
Select the "Phone" table, click on "Keys", and then the "Add" button. The key label is "KEYCUSTNUMBER", and its description is "Customer Number", as shown. Then click on the "Add Field" button and choose "
CustNumber". Click on "Select", "Cancel", "OK", and "Cancel".
Defining the Relationships
The relationships for the "Customer", "Order", and "State" tables were defined in the
Getting Started exercises
3 and
5. Now we are going to turn our attention to the "Phone" and "Detail" tables.
Select the "Phone" table, click on "Relations" folder, then the "Add" button to add a new relationship. Since the "
Phone" table is the
child table in its relationship with the
parent "Customer" table, select "MANY:1" because we are looking at it from the "Phone" table point of view.
Select "KEYCUSTNUMBER" as the foreign key. Select "Customer" from the drop-down list of parent tables, and choose "KEYCUSTNUMBER" since it is the parent's primary key. Click on "Map by Name" to get the key fields to match up, as shown. For "On Delete", choose "Cascade". That means that if you delete a Customer row, all the related Phone rows will be deleted as well. After all, you shouldn't have Phone rows for a non-existent Customer. Click "OK", then "Cancel", since there are no other relationships to define for this table.
The "
Detail" table is a child of both the "Product" table and the "Order" table. Select the "Detail" table, click on "Relations" folder, then the "Add" button to add a new relationship. Select "MANY:1" and choose "KEYPRODNUMBER" as the foreign key. Choose "Product" as the parent table, and "KEYPRODNUMBER" as the primary key. Choose "Map by Name" and "Restrict" for the "On Delete" constraint. You can't delete a product that has already been ordered. Choose "Restrict" for the "On Update" constraint. You can't change a product number for a product that is already ordered. Click "OK".
The "Detail" table is also the child of the "Order" table, so select "MANY:1" and "KEYORDERNUMBER" as the foreign key, and "Order" as the parent table, and "KEYORDERNUMBER" as the primary key. Choose "Map by Name" and "Cascade" for the "On Delete" and "On Update" constraints. We must delete all the related Detail rows if the Order is deleted. Click "OK", "Cancel".
Save your work using the "Save" button on the main toolbar.
Defining Relationship-Dependent Validity Checks
Now that all the table relationships are defined, we can set the Validity Checks for three columns that we expect to use on update forms.
- When entering a new "Order" row, we can specify that the "CustNumber" must match an existing row in the "Customer" table. You can't create an order for a non-existent customer.
- When entering a new "Detail" row, we can specify that the "OrderNumber" must match an existing row in the "Order" table. You can't add a detail row to an order that doesn't exist.
- When entering a new "Detail" row, we can specify that the "ProdNumber" must match an existing row in the "Product" table. You can't order a product that doesn't exist.
Select the "CustNumber" column in the "Order" table. Click on the "Validity Checks" tab and select the "Must be in Table" option. The IDE has selected the "Customer" table from the drop-down box for "Table Label". Click "Save".
Select the "OrderNumber" column in the "Detail" table. Click on the "Validity Checks" tab and select the "Must be in Table" option. The IDE has selected the "Order" table from the drop-down box for "Table Label".
Click on the "ProdNumber" column and agree to save the changes. Click on the "Validity Checks" tab and select the "Must be in Table" option. The IDE has selected the "Product" table from the drop-down box for "Table Label". Click "Save".
Two Extra Keys
Click on the "Product" table and notice that there is only a primary key. We also need to be able to sort the records by product description. Click on "Keys" and then on the "Add" button.
The key label is "KEYPRODDESC" and its description is "Product Description". Click on the "Add" button and select the "ProdDesc" field. Click "Select", "Cancel", "OK", "Cancel". The new key will show in the list of Product Keys.
Now do the same for the StateName field in the State table, as shown.
Save all your work by using the green "Save and Close" button on the DCT Explorer toolbar.
Make a backup of your work.
OK, What Did I Just Do?
Here’s a quick recap of what you just accomplished:
- You created the keys for all the new table definitions.
- You defined the relationships between the new tables.
- You defined three relationship-dependent validity checks to require that foreign key column values always have related primary key rows in a parent table.
The data dictionary is now complete at this point.
More lessons to follow.
No comments:
Post a Comment