Data types for attributes

A data type must be specified for each attribute.

Most organizations have well-defined guidelines for using the different data types. Here is an overview of the main data types that you can use for the attributes of your entities.

String
Data that contains a combination of letters, numbers, and special characters. String data types are listed below:
  • CHARACTER: Fixed-length character strings. The common short name for this data type is CHAR.
  • VARCHAR: Varying-length character strings.
  • CLOB: Varying-length character large object strings, typically used when a character string might exceed the limits of the VARCHAR data type.
  • GRAPHIC: Fixed-length graphic strings that contain double-byte characters.
  • VARGRAPHIC: Varying-length graphic strings that contain double-byte characters.
  • DBCLOB: Varying-length strings of double-byte characters in a large object.
  • BINARY: A sequence of bytes that is not associated with a code page.
  • VARBINARY: Varying-length binary strings.
  • BLOB: Varying-length binary strings in a large object.
  • XML: Varying-length string that is an internal representation of XML.
Numeric
Data that contains digits. Numeric data types are listed below:
  • SMALLINT: for small integers.
  • INTEGER: for large integers.
  • BIGINT: for bigger values.
  • DECIMAL(p,s) or NUMERIC(p,s), where p is precision and s is scale: for packed decimal numbers with precision p and scale s. Precision is the total number of digits, and scale is the number of digits to the right of the decimal point.
  • DECFLOAT: for decimal floating-point numbers.
  • REAL: for single-precision floating-point numbers.
  • DOUBLE: for double-precision floating-point numbers.
Datetime
Data values that represent dates, times, or timestamps. Datetime data types are listed below:
  • DATE: Dates with a three-part value that represents a year, month, and day.
  • TIME: Times with a three-part value that represents a time of day in hours, minutes, and seconds.
  • TIMESTAMP: Timestamps with a seven-part value that represents a date and time by year, month, day, hour, minute, second, and microsecond.
Examples: You might use the following data types for attributes of the EMPLOYEE entity:
  • EMPLOYEE_NUMBER: CHAR(6)
  • EMPLOYEE_LAST_NAME: VARCHAR(15)
  • EMPLOYEE_HIRE_DATE: DATE
  • EMPLOYEE_SALARY_AMOUNT: DECIMAL(9,2)

The data types that you choose are business definitions of the data type. During physical database design you might need to change data type definitions or use a subset of these data types. The database or the host language might not support all of these definitions, or you might make a different choice for performance reasons.

For example, you might need to represent monetary amounts, but DB2® and many host languages do not have a data type MONEY. In the United States, a natural choice for the SQL data type in this situation is DECIMAL(10,2) to represent dollars. But you might also consider the INTEGER data type for fast, efficient performance.