IBM® Informix® 12.10

Define tables, rows, and columns

You are already familiar with the idea of a table that is composed of rows and columns. But you must respect the following rules when you define the tables of a formal data model:

Rows must stand alone.
Each row of a table is independent and does not depend on any other row of the same table. As a consequence, the order of the rows in a table is not significant in the model. The model should still be correct even if all the rows of a table are shuffled into random order.

After the database is implemented, you can tell the database server to store rows in a certain order for the sake of efficiency, but that order does not affect the model.
Rows must be unique.
In every row, some column must contain a unique value. If no single column has this property, the values of some group of columns taken as a whole must be different in every row.
Columns must stand alone.
The order of columns within a table has no meaning in the model. The model should still be correct even if the columns are rearranged.

After the database is implemented, programs and stored queries that use an asterisk to mean all columns are dependent on the final order of columns, but that order does not affect the model.
Column values must be unitary.
A column can contain only single values, never lists or repeating groups. Composite values must be separated into individual columns. For example, if you decide to treat a person's first and last names as separate values, as the examples in this chapter show, the names must be in separate columns, not in a single name column.
Each column must have a unique name.
Two columns within the same table cannot share the same name. However, you can have columns that contain similar information. For example, the name table in the telephone directory example contains columns for children's names. You can name each column, child1, child2, and so on.
Each column must contain data of a single type.
A column must contain information of the same data type. For example, a column that is identified as an integer must contain only numeric information, not characters from a name.

If your previous experience is only with data organized as arrays or sequential files, these rules might seem unnatural. However, relational database theory shows that you can represent all types of data with only tables, rows, and columns that follow these rules. With a little practice, these rules become automatic.

Examples exchange | Troubleshooting

To find the PDF, see Publications for the IBM Informix 12.10 family of products.
For the release notes, documentation notes, and/or machine notes, see the Release Notes page.

Release date: March 2014