Home
Designing databases
The first step in creating a relational database is to construct a data model, which is a precise, complete definition of the data you want to store. After you prepare your data model, you must implement it as a database and tables. To implement your data model, you first select a data type for each column and then you create a database and tables and populate the tables with data. You can also implement fragmentation strategies and control access to your data.
Designing and Implementing a Database
The Informix® Database Design and Implementation Guide provides information to help you design, implement, and manage your Informix databases. It includes data models that illustrate different approaches to database design and shows you how to use structured query language (SQL) to implement and manage your databases.
Object-relational databases
Create and use user-defined casts
Cast distinct data types
Explicit casts with distinct types

Designing databases
The first step in creating a relational database is to construct a data model, which is a precise, complete definition of the data you want to store. After you prepare your data model, you must implement it as a database and tables. To implement your data model, you first select a data type for each column and then you create a database and tables and populate the tables with data. You can also implement fragmentation strategies and control access to your data.
- Designing and Implementing a Database
  The Informix® Database Design and Implementation Guide provides information to help you design, implement, and manage your Informix databases. It includes data models that illustrate different approaches to database design and shows you how to use structured query language (SQL) to implement and manage your databases.

Explicit casts with distinct types

To compare or substitute between values of a distinct type and its source type, you must explicitly cast one type to the other. For example, to insert into or update a column of a distinct type with values of the source type, you must explicitly cast the values to the distinct type.

Suppose you create a distinct type, int_type, that is based on the INTEGER data type and a table with a column of type int_type, as follows:

CREATE DISTINCT TYPE int_type AS INTEGER;
CREATE TABLE tab_z(col1 int_type);

To insert a value into the tab_z table, you must explicitly cast the value for the col1 column to int_type, as follows:

INSERT INTO tab_z VALUES (35::int_type)

Suppose you create a distinct type, num_type, that is based on the NUMERIC, data type and a table with a column of type num_type, as follows:

CREATE DISTINCT TYPE num_type AS NUMERIC;
CREATE TABLE tab_x (col1 num_type);

The distinct num_type inherits none of the system-defined casts that exist for the NUMERIC data type. Consequently, the following insert requires two levels of casting. The first cast converts the value 35 from INT to NUMERIC and the second cast converts from NUMERIC to num_type:

INSERT INTO tab_x VALUES (35::NUMERIC::num_type)

The following INSERT statement on the tab_x table returns an error because no cast exists to convert directly from an INT type to num_type:

INSERT INTO tab_x VALUES (70::num_type) -- returns error