Comparing Apples to Apples ~ User Defined Types
The relational database has been one of the most successful technologies deployed by Information Technology. Each year, billions of dollars are spent on relational database systems and applications. Much of the world’s business data is stored in relational form. When IBM, Oracle, Informix, and other database vendors released object relational versions of their respective databases, they added to the types of business rules that can be actively enforced by the database.
Many people still limit their understanding of business rules and database technology to mean "Referential Integrity." Today’s relational and object relational databases support a multitude of ways to actively enforce an application’s business rules. In this article, we’ll look at how User Defined data Types (UDT) can be used to actively enforce a business rule.
It is the norm for most of individual data items stored in databases to be relatively small and simple. In storing these simple data items, database systems support a set of predefined data types such as integers, real numbers, and character strings. The operations defined over these data types, such as arithmetic and comparison, are also simple and predefined.
Partially because of the Internet, many organizations need to store and manipulate objects that are neither small nor simple, and to perform operations on these objects that are not predefined. For example, an Internet retailer might need to store maps, photographs, written documents with diagrams, audio and video recordings. A city planner may need to find the parcels of property that intersect a proposed highway or to find the minutes of all meetings in which construction of new schools was discussed. Multimedia applications are among the fastest-growing segments of the database industry. Because of the very large amounts of data that they require, we can expect the requirements of these applications to become increasingly important.
The traditional data types and search capabilities of SQL are usually not sufficient for multimedia database applications. It is apparent that the requirements of these applications are so diverse that they could not be satisfied by a set of predefined language extensions. This is why ANSI’s SQL standards committee and database vendors have added facilities to let users define new data types and functions of their own.
The built-in SQL data types such as Number, Decimal and Double are often used to represent data that has a specialized meaning. For example, a column defined with a Decimal data type may contain amounts in US Dollars. Another column defined with a Decimal column may contain amounts in Japanese Yen. Similarly, a column defined with a Double (floating point) data type may contain measurements in feet, and another column with the same data type may contain measurements in meters.
The database designer may have some rules in mind about the kinds of operations that make sense on various types of data. For example, it may make sense to add two US Dollar amounts, but it is probably an error to add a US Dollar amount to a Yen amount. It may make sense to multiply a dollar amount by an integer or floating point number, but it is probably an error to multiply a US Dollar amount by another US Dollar amount.
If you use only the built-in data types, a database has no way of knowing what specialized rules may apply to the data. Therefore, many databases allow you to create new data types of your own and specify the operations that apply to them. In most databases, a user-defined data type is called a "type." Each type shares a common representation with one of the built-in types, called its "base type," but it has its own set of valid operations.
The following statements create two types named DOLLARS and YEN, each based on the built-in Decimal type. The phrase WITH COMPARISONS is a DB2 UDB extension that means that two dollar values may be compared, and that two Yen values may be compared. However, the database can prevent a dollar value from successfully being