Tables store information in the database used by IC applications. The tables, table fields, and table relations in an application are defined by the data model. The data model is contained in the following definitions in the application ADL file:
● Tables ● Fields ● Keys
All out-of-the-box tables are locked because the tables contain system table fields. When you expand the Table entity in Database Designer, locked tables are designated with the following icon:
You cannot delete a locked table, nor can you delete or alter the system table fields and keys in locked tables. You can only:
● Add new fields.
● Modify the values and properties of out-of-the-box system table fields.
Tip:
Tip: The field type determines which values are available to be modified. For example,
you can modify the length of variable character fields and the enumerated and default values of enumeration fields.
For more information about the structure of locked tables in your application, see that the data model of the application on the IC product CD in the directory docs\data_models.
This section contains the following topics:
● Overview of database tableson page 60
● Overview of database fieldson page 67
● Overview of database keyson page 75
Overview of database tables
All table definitions in an ADL file are displayed beneath the Tables placeholder in the Database Designer tree view. Each table contains:
● Columns. That are unique and identify a specified segment of the record such as last name or the phone number.
● Fields. Where a row and a column intersect. Each field contains the segment of a database record that matches a column heading.
● Keys. That identify database records and segments, define 1 to N relations between tables, and improve search performance.
As shown in the following diagram, Database Designer uses Physical DB Connections, also defined in the ADL file, to connect to the database through the Data server.
For more information about tables and how the tables relate to other application components,
see Tableson page 35.
Relationship between tables in Database Designer and the
application database
A direct correlation between a table definition in Database Designer and the table in an
application database is there. The table definition contains the name of the table in the database and the name of the Logical DB Connection that points to the database where the table is located.
During database administration tasks, Database Designer uses the information in the table definition to locate and configure the table in the database.
Database tables store the information collected and used by an IC application. To ensure that application users can easily and quickly access, display, and save information, many
application components link to tables directly or through other components. Database Designer maintains some of these links, propagating changes to table definitions throughout the ADL file. For example, if you change the table name in the definition, Database Designer automatically updates the table name in all table aliases assigned to that table.
Table properties
Table Fields Connection Table RDBMS Data server● Table nameon page 62.
● Table descriptionon page 62.
● DB tableon page 62.
● Logical DB connectionon page 63.
● ORDER BY clauseon page 63.
● History fieldon page 63.
● Table locationon page 64.
● Keyson page 64.
● Minimum extent and maximum extenton page 64 (Oracle only)
Table name
The table name, required case sensitive less than 30 characters, is used by Database Designer to reference the table. Although you are not required to use this name to define the database name of the table in the configured application database see DB tableon page 62, Avaya recommends that you do so.
To change a table name, specify a name in the Name field.
Table description
The table description (optional) is a place to keep information that identifies the table and the type of information stored in the table.
To change the description for a table, type a description in the Description field.
DB table
The database table, which is case sensitive, is the internal database table used by Database Designer to configure the table. Although the database table name does not have to be the same as the table name see Table nameon page 62, Avaya recommends that you use the same name for both.
CAUTION:
!
CAUTION: Do not change the internal database table name for a table in your production
database. If you do so, you might have to modify the SQL commands and manually apply the SQL commands to the database to reflect changes to table names.
Logical DB connection
The Logical DB Connection is where the database table is located. When you create a table, you must associate the table with a Logical DB Connection. The Logical DB Connection must have a Physical DB Connection within a DB Connection Set definition.
An application must have one primary Logical DB Connection. Database Designer
automatically configures tables using the primary Logical DB Connection. An application might also have one or more secondary Logical DB Connections for tables that the application accesses, but does not manage.
To change the Logical DB Connection that is associated with the table, select a new Logical DB Connection in the Logical DB Connection box.
If the Logical DB Connection is not in the list, define the Logical DB Connection in the
appropriate DB Connection Set. For more information, see Overview of DB connection setson page 51.
ORDER BY clause
An ORDER BY clause (optional) is a SQL statement that specifies how to return the results of a query against the table. You can specify an ORDER BY clause to return search results in either ascending or descending alphabetic order.
For more information about ORDER BY clauses, see your database documentation.
To change the ORDER BY clause for the table, specify an SQL string in the ORDER BY field.
History field
The History field (optional) maintains transaction information for one or more fields in a table. The history field must have a Text data type.
History field transaction information is displayed in the application by a Long Text object see
Long text objectson page 122.
To select a field to store transaction information, select the field from the History Field box. The drop-down list contains all the fields in the table that are of type Text.
To record transaction information for a specified field in the History field, you must also select the History option see Historyon page 73 in the properties of the field.
Note:
Note: To ensure that the record name for a foreign field record appears in the History
field, instead of the key number of the record. For example, Reporter: John Milton instead of Reporter: 1 (Key), identify the foreign field as the primary info field see Primary infoon page 72.
Table location
The table location is the physical device where the table is configured. By default, all tables in an IC application are configured on a single device. However, you can use more than one physical device to reduce potential controller contention and improve database performance. To configure a table on a specific device, enter the device name in the Table Location field.
Note:
Note: This setting overrides the Table Location specified in the Physical DB Connection
of the application.
Keys
The Keys group contains the keys for the table. For more information, see Overview of
database keyson page 75.
Minimum extent and maximum extent
For an Oracle database, you can specify a range to determine the size of an extent that can be added by Oracle to a tablespace of a table.
The minimum extent is the minimum amount of space that can be dynamically added to the tablespace. The maximum extent is the maximum amount of space that can be dynamically added to the tablespace. Oracle uses the minimum and the maximum extent values to determine the size of the extent value added.
To change the minimum or the maximum amount of dynamic space, specify an integer value in megabytes (MB) in the Minimum Extent and Maximum Extent text boxes.
For more information about extents, see your Oracle documentation.
How to create a table
Before you create a table with Database Designer, you must establish a connection to the database by specifying a valid connection. For more information, see How to create a physical
DB connectionon page 45.
To create a table, perform the following: 1. Select New > Component.
2. In the New Component dialog box, select Table and then select OK. Database Designer displays the New Table Wizard.
a. Enter the name of the table in the Name field.
A table name is case-sensitive and must be less than 30 characters. For more information, see Naming requirements and restricted nameson page 224.
b. Enter a description of the contents and purpose of the table in the Description field. c. Select Next.
4. In the second Wizard dialog box:
a. Select the Logical DB Connection where the table must be located from the drop-down list.
If the Logical DB Connection is not in the drop-down list, you must create a Physical DB Connection for that Logical DB Connection before creating the table see How to
create a physical DB connectionon page 45.
b. Select Next.
5. In the third Wizard dialog box:
a. Enter the name of the table in the database in the top field.
By default, the Wizard enters the table name from the first dialog box. b. Enter an ORDER BY string, if required, in the bottom field.
c. Select Next.
6. In the fourth Wizard dialog box:
a. Enter the primary key name in the Primary Key Name field.
b. Enter the primary key field name in the Table Field for the Key field. c. Select Finish.
7. Review the properties of your new table and you can make any necessary modifications. For more information, see Table propertieson page 61.
8. Select the lock icon in the upper right of the table’s Properties tab to set the permissions for the table. For more information, see How to set permissionson page 31.
9. Select File > Save to save your new table definition.
Database Designer creates the table with a single field for the primary key. To add more fields to your table, see How to create a fieldon page 73.
How to change a table
You modify a table by changing the properties in the Table Properties tab, then reconfiguring your application database see Overview of reconfiguring a databaseon page 210.
Note:
Note: If your IC application includes Web Management, you must update the Persistent
Data Manager (PDM) XML file when you make changes to the structure of certain tables. For more information, see IC Administration Guide.
If you use the escalation capability in an IC application, you cannot change the properties of any of the following out-of-the-box tables: Employee, Owner alias of Employee, Groupmember, and Workgroup.
To change the properties for a table:
1. Select the table in the tree view of the Database Designer window. 2. Select the Properties tab.
3. Modify the required table properties in the Properties tab. For more information about specific table properties, see Table propertieson page 61.
How to delete a table
Before deleting a table, delete all references to the table from the following components: ● Table aliases ● Table sets ● Browser columns ● Objects ● Groups ● Forms ● Relations ● Relation sets To delete a table:
1. Select the table in the tree view of the Database Designer window. 2. Select Edit > Delete.
If the table is referenced by other components, Database Designer alerts you to remove the remaining associations before deleting the table.
Overview of database fields
In a database, a table field defines a single data item. The data item is grouped with other data items to form a record.
In the ADL file, the fields identify the columns and segments of a database row. Each field is named according to the heading of a column. All the fields in a table include a row and, therefore, contain all the segments in one database record.
You can specify a default value for a table field that is inserted in the database when you create a new record. However, the default value can be overwritten by the default or specified value of the corresponding object in an IC application.
Data types
Database Designer supports several data types and also displays the data types in one or more object types. For more information about object types see How to automatically create
objectson page 114.
Avaya recommends you use following data type formats for your application design:
Use the data type…
To display data in an object type of…
For example…
Binary not for use with an object an image
Note: Although the Binary data type can be used to provide a place in a database table to store, for example, a binary image, there is currently no IC object that can display or accept data of this type.
Byte Check Box or Text Box an integer value from 0 to 256
Character Text Box a fixed length string such as CA, NY, or TX
Date Date Time
Select the button to display the Calendar widget and specify a date or range of dates
Note:
Note: A char becomes nchar and varchar becomes nvarchar data type for unicode.
Field properties
The field properties are:
● Field nameon page 69.
● Field descriptionon page 69.
Date Time Date Time
Select the button to display the Calendar widget and specify a date and time or a range of dates
14 May 1999 10:02:02
Double Text Box 1.7e+/-5 (6 digits)
Enumeration Combo Box
Select a value for the field from the drop-down
a fixed list of alphanumeric values, such as:
Red Yellow Blue
Float Text Box 3E +/- 37 (7 digits)
Integer Text Box or Check Box 7500
Interval Text Box 14:30:00
Money Text Box $2.25
Serial Text Box 1, 2, 3, …
Note: The Serial data type is an Integer data type for which numbers are generated serially for use as key values. Only one field for each table can have a Serial data type.
Short Integer Text Box -32,768 to 32,767
Text Long Text
Select to add text to a long text field.
a string that might be more than 255 characters long
Variable Char Text Box or Multi-Line a string value of variable length
Use the data type…
To display data in an object type of…
● Database field nameon page 69. ● Typeon page 70.
● Labelon page 70.
● Lengthon page 70.
● Minimum and maximum valueson page 70.
● Default valueon page 71.
● Enumerated valueson page 71.
● Currency symbol and money scaleon page 71.
● Read onlyon page 72.
● Requiredon page 72.
● Primary infoon page 72.
● Case sensitiveon page 73.
● Left anchoredon page 73.
● Historyon page 73.
Field name
The field name, which is required case sensitive and must be less than 30 characters, is used by Database Designer to reference the field. Although you are not required to use this name to define the name of the field in the configured application database see Database field nameon page 69, Avaya recommends that you do so.
To change the field name, type the new name in the Name field.
Field description
The field description (optional) is a place where you can keep information that identifies the field and the information the field contains.
To change the description for a field, type the new description in the Description field.
Database field name
The database name, which is case sensitive, for a field is the internal database name used by Database Designer to configure the field. Although this name does not have to be the same as the field name see Field nameon page 69, Avaya recommends that you use the same name for both.
CAUTION:
!
CAUTION: Do not change the internal database name for a field in your production database.
If you change a field name in the production database, you might have to modify the SQL commands and manually apply the SQL commands to the database to reflect changes to field names.
To change the database name for a field, type the new name in the Database Field Name field.
Type
Database Designer supports several data types for storing data see Data typeson page 67. While you can change some data types for fields, you cannot change a Serial data type to another type.
CAUTION:
!
CAUTION: If you are modifying an existing application that contains data, ensure the new
data type supports the existing data. If the new data type cannot support existing data, the data is lost when you reconfigure.
To change the data type for a field, select the new data type from the Type box.
Label
The field label (optional) is displayed in the History long text field. The value of the field appears to the right of the label.
To change the label for the field, type the new label in the Label field.
Length
The length (optional) is the maximum number of characters that can be stored in fields with char and var char data types.
To set the maximum length for character and variable char data type, specify an integer value in the Length field.
Minimum and maximum values
The minimum and the maximum values (optional) specify a range of valid integer values for the following data types.
● Double ● Float ● Integer ● Short integer.
The application manages range checking, and configures the database field with these settings where possible.
To specify a range of valid values, specify a positive integer value in both the Minimum and
Maximum text boxes. Ensure that the minimum value is smaller than the maximum value.
Default value
You can specify a default value (optional) for a field. However, the value for the field might be overwritten by the default or specified value of the corresponding object for the field in an IC application.
To specify a default value for a non-enumerated data type, specify a value in the Default field. Leave the field blank if you do not want to specify a default.
To select a default enumerated value, select a value from the Default box. Select No Default if