Users sometimes need to enter invalid values or values they are not sure of. Flexible input controls allow invalid data values to be entered, they enable values that the user is not sure of to be marked as such, and enable the user to make explanatory annotations. This article describes a storage technique for relational databases that bridges the gap between the invalid and uncertain values used by flexible input controls and the strict data integrity and validation rules required by relational databases.
Users sometimes need to enter invalid values or values they are not sure of. Flexible input controls allow invalid data values to be entered and enable values that the user is not sure of to be marked as such, on the understanding that invalid and uncertain data will be corrected in the future, and enable users to make annotations that explain why such values were entered. This article describes a storage technique for relational databases that bridges the gap between the invalid and uncertain values used by flexible input controls and the strict data integrity and validation rules required by relational databases.
To demonstrate the implementation of the storage technique, the examples in this article use a relational database that stores customer order information. Table (a) below shows the
CUSTOMER relation that stores the name and telephone number of each customer, each uniquely identified by a customer number. Table (b) shows the
ORDERS relation that stores the product number and quantity of each order, each uniquely identified by an order number.
The following two scenarios provide an invalid value and a value that the user is not sure of, neither of which can be stored in the relations shown in tables (a) or (b). In the first scenario, the user wants to store the text “Contact John Smith at International Widgets” in the phone number field. The user will enter John Smith’s phone number at a later date when it becomes available. The user also wants to annotate the text with the reason that “the company secretary will call back with Mr. Smith’s phone number.” This string value and its annotation cannot be stored in the relation shown in table (a): the phone number field cannot accept string values and there is no field for storing annotations.
In the second scenario, the user wants to store the account number 319. This is a valid account number but the user is unsure whether it is the correct account to charge the bill to. The user wants to annotate this account number with the comment: “I need to confirm this account number with Marketing.” There is no way to store the user’s uncertainty about the account number in the relation shown in table (b).
One way to mark the user’s uncertainty about a field value is to add an uncertainty attribute for each attribute in the relation. The disadvantage of this method is that the number of attributes in each relation would be doubled, the vast majority of which would never be used. A more efficient method of storing flexible values, one that does not require changing existing relations, is to add new relations to store flexible values and to link them to existing relations.
A new relation called
FLEXIBLE_VALUES is created to store the invalid and uncertain values and their annotations. Table (c) shows a
FLEXIBLE_VALUES relation that stores the example invalid and uncertain values described in section 2. The
ATTRIBUTE_NO attribute stores the attribute number in a relation for which an invalid or uncertain value is to be stored. The first tuple in the
FLEXIBLE_VALUES relation stores the invalid telephone number. The value of the
ATTRIBUTE_NO attribute is 3 because
PHONE_NO is the third attribute of the
VALUE attribute stores the invalid or uncertain value as a string. The
ANNOTATION attribute stores an annotation to the string stored in the
VALUE attribute. The
STATUS field is a marker that records whether the flexible input value is an invalid (1) or an uncertain value (2).
A new relation is created for each existing relation that needs to store flexible values. The new relations map a tuple in an existing relation to a tuple in the
FLEXIBLE_VALUES relation. A flexible attribute value is marked in a relation by storing the
NULL value and an SQL query retrieves the value of the attribute from the
FLEXIBLE_VALUES relation. The following diagram shows the
FLEXIBLE_CUSTOMERS relation that maps tuples in the
CUSTOMER relation to tuples in the
The following SQL query retrieves the invalid value in the
CUSTOMER relation. The nested
SELECT statement retrieves all the tuples in the
FLEXIBLE_CUSTOMERS relation that contain the specified customer number. The outer
SELECT statement uses the
FLEXIBLE_NO values to retrieve the corresponding tuples from the
The diagram below shows the
FLEXIBLE_ORDERS relation that maps tuples in the
ORDERS relation to tuples in the
The following SQL query retrieves the uncertain value in the