Home arrow MySQL arrow 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.

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

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.



 
 
>>> 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: