MySQL
  Home arrow MySQL arrow Page 4 - Database Design Using Key-Value Tables
Dev Shed Forums 
Administration  
AJAX  
Apache  
BrainDump  
DHTML  
Flash  
Java  
JavaScript  
Multimedia  
MySQL  
Oracle  
Perl  
PHP  
Practices  
Python  
Reviews  
Security  
Style-Sheets  
Web Services  
XML  
Zend  
Zope  
Forums Sitemap 
IBM® developerWorks 
Sun Developer Network 
E-Commerce Hosting 
Linux Web Hosting 
Managed Hosting 
Small Business Hosting 
Mobile Linux 
App Generation ROI 
VPS Hosting 
Weekly Newsletter

 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
MYSQL

Database Design Using Key-Value Tables
By: Tal Olier
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 3 stars3 stars3 stars3 stars3 stars / 25
    2006-02-13

    Table of Contents:
  • Database Design Using Key-Value Tables
  • Traditional database design vs. key-value table
  • Drawback no. 1: No real use of database data types
  • Drawback no. 2: Awkward use of database constraints
  • Drawback no. 3: Problematic use of AND operator when relating to more than one attribute in the query criteria

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    Database Design Using Key-Value Tables - Drawback no. 2: Awkward use of database constraints


    (Page 4 of 5 )

    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


       · i rated this article 4 out of 5 because it does an excellent job of describing and...
       · This design flaw is called EAV (Entity-Attribute-Value) and it does not work. It...
       · But I have to echo the sentiments listed here. If you do not exercise iron-clad...
       · In my previous comment, I stated that I have NEVER seen meta-data done right. Take...
       · This design is a wonderful thing for holding global data of multiple types or for...
       · Thanks.Did you read it all?I’ve mentioned all drawbacks I know, do you have...
       · Having been a dba over the past 36 years for DB2, Oracle, SAS, MySQL, and a few...
       · There are many systems that work on EAV for recording observations and...
     

       

    MYSQL ARTICLES

    - MySQL Server Tuning Tips and Tricks
    - MySQL Query Optimizations and Schema Design
    - MySQL Benchmarking Tools and Utilities
    - MySQL Benchmarking Concepts and Strategies
    - Take Some Load off MySQL with MemCached
    - MySQL Table Prefix Changer Tool in PHP
    - Using the SIGNAL Statement for Error Handling
    - Error Handling Examples
    - Error Handling
    - Completing a Search Engine with MySQL and PH...
    - Paginating Result Sets for a Search Engine B...
    - Building a Search Engine with MySQL and PHP 5
    - Using Boolean Operators for Full Text and Bo...
    - PHP, MySQL and the PEAR Database
    - Working with PHP and MySQL





    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 1 hosted by Hostway
    Stay green...Green IT