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

Drawback no. 2: Awkward use of database constraints - 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

Database constraints help us keep our data correct.

In the above (EMPLOYEES) example, assuming that our company was founded in 2004, let's figure that we want to keep our data integrity in such a way that the employee start working date will always be between 2004 and 2005.

All we have to do in scenario no. 1 is add a check constraint that will enforce this rule. Check constraints place a value restriction on the contents of a column, allowing the table owner to define which exact values are permitted for this specific column. Such a constraint creation would look like this:

ALTER TABLE EMPLOYEES ADD CONSTRAINT START_WORK_YEAR_CK CHECK (START_WORK_YEAR >= 2004 AND START_WORK_YEAR <= 2005);

By defining such a constraint, a DML attempting to update a value of the column START_WORK_YEAR with values above 2005 or below 2004 would fail, with an error indicating that the update failed due to our check constraint (START_WORK_YEAR_CK).

In scenario 2, there is also an option to add such a database constraint. Consider the following constraint added:

 

ALTER TABLE EMPLOYEE_ATTRIBUTES ADD CONSTRAINT START_WORK_YEAR_CK
CHECK (     1 = CASE KEY1
WHEN 'START_WORK_YEAR' THEN
CASE VALUE1
WHEN '2004' THEN 1
WHEN '2005' THEN 1
ELSE 0
END
ELSE 1
END
);

As you can see, the above constraint use is possible but cumbersome, and most important, it will force us to add a constraint for each new ATTRIBUTE, which means that the model is no longer generic.



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