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

Traditional database design vs. key-value table - 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

Consider the following two scenarios:

Scenario no. 1 traditional database design method

The DBA will use the following SQL statements in order to create two additional columns:

ALTER TABLE EMPLOYEES ADD HAS_COMPANY_VEHICLE INT NULL;
ALTER TABLE EMPLOYEES ADD START_WORK_YEAR INT NULL;

In our example, we will update the data that already exists in the table with the following update sql statements:

UPDATE EMPLOYEES SET HAS_COMPANY_VEHICLE = 1, START_WORK_YEAR = 2004 WHERE ID = 1;
UPDATE EMPLOYEES SET HAS_COMPANY_VEHICLE = 0, START_WORK_YEAR = 2004 WHERE ID = 2;
UPDATE EMPLOYEES SET HAS_COMPANY_VEHICLE = 1, START_WORK_YEAR = 2005 WHERE ID = 3;

This will make our table look like this:

EMPLOYEES

ID

NAME

SALARY

HAS_COMPANY_VEHICLE

START_WORK_YEAR

1

Dany

4000

1

2004

2

Brit

5000

0

2004

3

Sharon

1000

1

2005

We might want to be able to make a new descriptive change without altering the database schema structure. In order to be able to do that, we can use key-value tables as described in the following scenario.

Scenario no. 2 use of key-value table

The following scenario describes a generic approach to the challenge.

The DBA can create an additional table which will hold any additional information that describes the employee better. Such a table can be produced by the following sql statements:

CREATE TABLE EMPLOYEE_ATTRIBUTES (
EMPLOYEE_ID       INT               NOT NULL,
KEY1              VARCHAR (100)     NOT NULL,
VALUE1            VARCHAR (100)     NOT NULL);

ALTER TABLE EMPLOYEE_ATTRIBUTES ADD CONSTRAINT EMPLOYEE_ATTRIBUTES_PK PRIMARY KEY (EMPLOYEE_ID, KEY1);

Updating existing data in our example can be done using the following insert sql statements:

INSERT INTO EMPLOYEE_ATTRIBUTES (EMPLOYEE_ID, KEY1, VALUE1) VALUES (1, 'HAS_COMPANY_VEHICLE', '1');
INSERT INTO EMPLOYEE_ATTRIBUTES (EMPLOYEE_ID, KEY1, VALUE1) VALUES (1, 'START_WORK_YEAR', '2004');
INSERT INTO EMPLOYEE_ATTRIBUTES (EMPLOYEE_ID, KEY1, VALUE1) VALUES (2, 'HAS_COMPANY_VEHICLE', '0');
INSERT INTO EMPLOYEE_ATTRIBUTES (EMPLOYEE_ID, KEY1, VALUE1) VALUES (2, 'START_WORK_YEAR', '2004');
INSERT INTO EMPLOYEE_ATTRIBUTES (EMPLOYEE_ID, KEY1, VALUE1) VALUES (3, 'HAS_COMPANY_VEHICLE', '1');
INSERT INTO EMPLOYEE_ATTRIBUTES (EMPLOYEE_ID, KEY1, VALUE1) VALUES (3, 'START_WORK_YEAR', '2005');

This would yield the following table:

EMPLOYEE_ATTRIBUTES

EMPLOYEE_ID

KEY1

VALUE1

1

HAS_COMPANY_VEHICLE

1

1

START_WORK_YEAR

2004

2

HAS_COMPANY_VEHICLE

0

2

START_WORK_YEAR

2004

3

HAS_COMPANY_VEHICLE

1

3

START_WORK_YEAR

2005

This information is stored in a more generic structure, allowing the programmer to add employee attributes on his own as well as create a mechanism that adds employee attributes at run-time.

The data type of the two new columns, KEY1 and VALUE1, should be VARCHAR so it can hold both character or numeric data. It is possible to limit the data type only to numeric data if we want to for some reason.

When a new description item for an employee will be required, the programmer would simply need to add this attribute by inserting a new record to EMPLOYEE_ATTRIBUTES table.

Consider the following insert statement as a new description item, describing the sex type of the employee named Dany.

INSERT INTO EMPLOYEE_ATTRIBUTES (EMPLOYEE_ID, KEY1, VALUE1) VALUES (1, 'SEX', 'MALE');

For completion of the above example you can use the following SQL statements:

INSERT INTO EMPLOYEE_ATTRIBUTES (EMPLOYEE_ID, KEY1, VALUE1) VALUES (2, 'SEX', 'FEMALE');
INSERT INTO EMPLOYEE_ATTRIBUTES (EMPLOYEE_ID, KEY1, VALUE1) VALUES (3, 'SEX', 'FEMALE');

The benefits of such a model are:

-     High flexibility - as we can see, adding a new attribute at a late design phase is very simple. We can even create a mechanism that adds attributes according to real time configuration.

-     We do not need to deal with data types when designing the schema.

-     Some database types has a record length limitation, e.g. MS SQL Server's 2000 maximum record length is 8K. This means that, if we have five attributes that are each 2000 characters long, our only option is to add those attributes to an additional table, as we did with EMPLOYEE_ATTRIBUTES table.

Regarding the drawbacks, they are a little complex, so we are going to drill down into each one of them in the following paragraphs of this article.



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