Drawback no. 3: Problematic use of AND operator when relating to more than one attribute in the query criteria - MySQL

This is the most problematic issue that needs to be taken into consideration. Since it is complex, let’s start with an example. Consider the following report (report 1):

Fetch all employee names and their start working date, that had started working in 2004. The result of course should look like the following table:

In scenario 1, the following query will provide the required results:

SELECT NAME, START_WORK_YEAR
FROM EMPLOYEES
WHERE START_WORK_YEAR = 2004;

In scenario 2, the following query will provide the required results:

SELECT E.NAME, EA.VALUE1 AS START_WORK_YEAR
FROM EMPLOYEES E INNER JOIN EMPLOYEE_ATTRIBUTES EA
    ON (E.ID = EA.EMPLOYEE_ID)
WHERE KEY1 = 'START_WORK_YEAR' AND VALUE1 = '2004';

But in case we want to check more than one attribute in the criteria, and the relation between the two attributes would need to be defined by an AND operator, we need to be careful not to fall into the following trap. Consider the following report (report 2):

Fetch all employee names, and their start working date that are males AND had started working in 2004. The result of course should look like the following table:

In scenario 1, the following query will provide the required results:

SELECT NAME, START_WORK_YEAR
FROM EMPLOYEES
WHERE START_WORK_YEAR = 2004 AND SEX = 'MALE';

In scenario 2, many developers fall into the trap and introduce the following query as the report implementation:

SELECT E.NAME, EA.VALUE1 AS START_WORK_YEAR
FROM EMPLOYEES E INNER JOIN EMPLOYEE_ATTRIBUTES EA 
    ON (E.ID = EA.EMPLOYEE_ID)
WHERE (EA.KEY1 = 'START_WORK_YEAR' AND EA.VALUE1 = '2004') 
    AND (EA.KEY1 = 'SEX' AND EA.VALUE1 = 'MALE');

The above query will provide the following results:

We get an empty result-set!

The reason for it is that no record in the Cartesian product of EMPLOYEES and EMPLOYEE_ATTRIBUTES fulfill both conditions of

(EA.KEY1 = ‘START_WORK_YEAR’ AND EA.VALUE1 = ‘2004’)
AND (EA.KEY1 = ‘SEX’ AND EA.VALUE1 = ‘MALE’)

In order to better understand this issue, let's look at what the database engine is doing:

First the engine creates the following Cartesian product of EMPLOYEES and EMPLOYEE_ATTRIBUTES:

Then the database engine removes records which do not comply with the where criteria. As we can see, there is no record which complies with both:

(EA.KEY1 = ‘START_WORK_YEAR’ AND EA.VALUE1 = ‘2004’)
AND (EA.KEY1 = ‘SEX’ AND EA.VALUE1 = ‘MALE’)

The above pitfall can be workaround using the following query:

SELECT E.NAME, EA1.VALUE1 AS START_WORK_YEAR
FROM EMPLOYEES E INNER JOIN EMPLOYEE_ATTRIBUTES EA1 
    ON (E.ID = EA1.EMPLOYEE_ID) 
    INNER JOIN EMPLOYEE_ATTRIBUTES EA2 
        ON (EA1.EMPLOYEE_ID = EA2.EMPLOYEE_ID)
WHERE (EA1.KEY1 = 'START_WORK_YEAR' AND EA1.VALUE1 = '2004') 
    AND (EA2.KEY1 = 'SEX' AND EA2.VALUE1 = 'MALE');

In this case the database engine will create the following Cartesian product and select the marked record using the criteria clause:

Conclusion

We can use key-value tables in a database design, but we should keep in mind that the same flexibility which has been enabling us to add attributes in a quick and easy way can also cause data integrity errors.

Furthermore, using key-value tables is recommended mostly with attributes which are required for long descriptions, rather than attributes which are used for data filtering.