HomeMySQL Page 3 - Database Design Using Key-Value Tables
Drawback no. 1: No real use of database data types - MySQL
In the following article we are going to cover the use of key-value tables in a database design. They can help you with flexibility in design, but will trip you up in other areas (such as data filtering) if you are not careful. I assume that the audience for the following article is familiar with basic traditional relational database design and SQL language; nevertheless, someone that is not familiar with it should be able to comprehend it from the walkthrough example of this article.
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.
/ 34 
