Home arrow MySQL arrow Page 5 - Database Design Using Key-Value Tables

Drawback no. 3: Problematic use of AND operator when relating to more than one attribute in the query criteria - 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.

TABLE OF CONTENTS:
  1. Database Design Using Key-Value Tables
  2. Traditional database design vs. key-value table
  3. Drawback no. 1: No real use of database data types
  4. Drawback no. 2: Awkward use of database constraints
  5. Drawback no. 3: Problematic use of AND operator when relating to more than one attribute in the query criteria
By: Tal Olier
Rating: starstarstarstarstar / 34
February 13, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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:

NAME

START_WORK_YEAR

Dany

2004

Brit

2004

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';

NAME

START_WORK_YEAR

Dany

2004

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:

NAME

START_WORK_YEAR

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:

E.ID

E.NAME

E.SALARY

EA.EMPL-
OYEE_ID

EA.KEY

EA.VALUE

1

Dany

4000

1

HAS_COMPANY_VEHICLE

1

1

Dany

4000

1

START_WORK_YEAR

2004

1

Dany

4000

1

SEX

MALE

2

Brit

5000

2

HAS_COMPANY_VEHICLE

0

2

Brit

5000

2

START_WORK_YEAR

2004

2

Brit

5000

2

SEX

FEMALE

3

Sharon

1000

3

HAS_COMPANY_VEHICLE

1

3

Sharon

1000

3

START_WORK_YEAR

2005

3

Sharon

1000

3

SEX

FEMALE

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:

E.
ID

E.
NAME

E.
SALARY

EA1/2.
EMPL-
OYEE_ID

EA1.
KEY

EA1.
VALUE

EA2.
KEY

EA2.
VALUE

1

Dany

4000

1

SEX

MALE

SEX

MALE

1

Dany

4000

1

SEX

MALE

START_WORK_YEAR

2004

1

Dany

4000

1

START_WORK_YEAR

2004

SEX

MALE

1

Dany

4000

1

START_WORK_YEAR

2004

START_WORK_YEAR

2004

2

Brit

5000

2

SEX

FEMALE

SEX

FEMALE

2

Brit

5000

2

SEX

FEMALE

START_WORK_YEAR

2004

2

Brit

5000

2

START_WORK_YEAR

2004

SEX

FEMALE

2

Brit

5000

2

START_WORK_YEAR

2004

START_WORK_YEAR

2004

3

Sharon

1000

3

SEX

FEMALE

SEX

FEMALE

3

Sharon

1000

3

SEX

FEMALE

START_WORK_YEAR

2005

3

Sharon

1000

3

START_WORK_YEAR

2005

SEX

FEMALE

3

Sharon

1000

3

START_WORK_YEAR

2005

START_WORK_YEAR

2005

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. 



 
 
>>> More MySQL Articles          >>> More By Tal Olier
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: