Database Design Using Key-Value Tables - Drawback no. 1: No real use of database data types
(Page 3 of 5 )
When designing a database table, in most cases the design includes exact data types. This is important because the use of correct data types can help enforce data integrity.
In our EMPLOYEES example, the VALUE1 column is able to contain any value that is less than or equal to 100 charters length.
Consider the following sql statement:
UPDATE EMPLOYEE_ATTRIBUTES SET VALUE1 = 'AAA' WHERE EMPLOYEE_ID = 1 AND KEY1 = 'START_WORK_YEAR';
This statement will execute without success and the result would be as follows:
EMPLOYEE_ATTRIBUTES |
EMPLOYEE_ID | ATTRIBUTE_ID | KEY | VALUE |
… | … | … | … |
1 | 2 | START_WORK_YEAR | AAA |
… | … | … | … |
Of course, having our data describing that employee no. 1 (Dany) has started working on date ‘AAA’ is indeed a data integrity failure.
In the case of scenario 1, the equivalent statement would be:
UPDATE EMPLOYEES SET START_WORK_YEAR = 'AAA';
This of course will raise a database error saying that the data provided for column START_WORK_YEAR has a data type mismatch.
Another data type issue would arise when we will need to cast the string VALUE1 to a numeric data type in order to perform some mathematic computations with it.
These problems can be worked around by defining a column of NUMERIC data type for numeric values and another column of data type VARCHAR for alphanumeric values, but it will never be as good as having the exact data type that matches our data.
Next: Drawback no. 2: Awkward use of database constraints >>
More MySQL Articles
More By Tal Olier
