The concept is OK but your implementation is not

Introduction

Control Values

Extra Values

Conclusion

Comments

Introduction

I recently came across a blog post at Database Modelization Anti-Patterns in which he described the use of Entity Attribute Values (EAV) in a relational database. He called this an anti-pattern as it presents the following problems:

1. The value attribute is of type text so as to be able to host about anything, where some parameters are going to be integer, interval, inet or boolean values.
2. The entity and parameter fields are likewise free-text, meaning that any typo will actually create new entries, which might not even be used anywhere in the application.
3. When fetching all the parameters of an entity to set up your application's object, the parameter names are a value in each row rather than the name of the column where to find them, meaning extra work and loops.
4. When you need to process parameter in SQL queries, you need to add a join to the params table for each parameter you are interested in.

Because of these problems the author reaches the following conclusion:

Never implement an EAV model, this anti-pattern makes everything more complex than it should for a very small gain at modeling time.

He suggests that the need for EAV in your database points to a fault in its design, but there may be a genuine reason for its use. For example, I have developed an ERP application which is a package used by many different organisations, and anybody who has ever worked on a software package will instantly tell you that while the package will cover the most common needs of a large number of users in a standard fashion there will ALWAYS be a customer who does something differently and wants the package to be customised to suit his specific needs. This may be because they have a non-standard requirement, or that they deal with a common situation in a non-standard way. Dealing with each customisation may require some different program logic, or it may require some additional fields in the database. It is generally not a good idea to modify the core code of the application for each customisation, which is why I built into my package the ability to deal with each customisation as a plugin which is kept separate from the core code. In this way the core code is kept pristine, and each customer only gets the customised plugins which are relevant to him.

When it comes to changes in the data requirements I have two approaches:

• If the changes are relatively small, such as adding new columns to existing database tables, then I use the EAV approach using tables which are built into the standard database but whose use is entirely optional.
• If the changes are significant and would best be served with additional database tables then I build a custom subsystem for that customer which has its own database, user transactions and application logic. This subsystem and database is never delivered to other customers, so it can be programmed to do whatever the requesting customer wants.

In this article I shall ignore the building of a custom database and concentrate on the two different ways in which I have implemented EAV - Control Values and Extra Values.

Control Values

This deals with a problem which I first wrote about in 2001 for UNIFACE and updated in 2006 for PHP in which the solution is to have a separate record in the database for each value instead of a separate column on a single control record. As my application is broken down into a series of separate yet interconnected subsystems this allows a different set of control values to be maintained for each subsystem. It makes it easy to add a new control value as all I do is add a new record to the control table instead of a new column to the control record. The database schema is as follows:

CREATE TABLE `mnu_control` (
  `record_id` VARCHAR(16) NOT NULL ,
  `field_id` VARCHAR(32) NOT NULL,
  `field_value` VARCHAR(255) NULL,
  PRIMARY KEY (`record_id`, `field_id`)
);

Here the record_id field holds the subsystem identity, so this single table can be used by any number of subsystems.

I have a standard class file called mnu_control.class.inc which encapsulates all the logic required to access the MNU_CONTROL table in the MENU database. This enables me to treat each value as it it were contained in a separate column in a single table instead of being in a separate record in that table. All the other subsystems in the application reference the same table, but this is how I handle the differences:

• Each subsystem has its own version of this class file which inherits the common code so need only supply the differences as required by that subsystem.
• Each class file has its own version of the _cm_changeConfig() method which converts the physical database structure into the conceptual structure required by that subsystem. This allows each control value to have its name, type and size specified correctly, which in turns means that the standard framework code can validate the user's input against these specifications.
• When executing the getData() method on this object it will return the data as if each row were a separate column on the same table.
• Each subsystem has its own "Maintain Control Data" task which, as well as pointing to its own version of mnu_control.class.inc, also has its own screen structure file which shows all the control values for that subsystem in a single screen.

This particular type of transaction has its own Transaction Pattern.

Extra Values

Sometimes there may be a need to hold additional pieces of data for different objects within a subsystem instead of just once for the entire subsystem, so this is where I create a pair of tables for each of those objects.

First there is an XXX_EXTRA_VALUES table which is associated with each object, where XXX can be something like 'party', 'product' or 'order'. Note that the specifications for XXX_id should match the primary key for that object.

CREATE TABLE `XXX_extra_values` (
  `XXX_id` int NOT NULL,
  `extra_id` VARCHAR(40) NOT NULL,
  `extra_value` VARCHAR(80) NOT NULL,
  PRIMARY KEY (`<object>_id`, `extra_id`)
);

Then there is an XXX_EXTRA_NAMES table which provides the specifications for each extra value.

CREATE TABLE `XXX_extra_names` (
  `extra_id` VARCHAR(40) NOT NULL,
  `extra_name` VARCHAR(80) NOT NULL,
  `extra_desc` VARCHAR(255) NULL,
  `extra_type` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`extra_id`)
);

On this table the extra_type can be set to one of the following:

• STRING - the default
• DATE - a value in the format 'CCYY-MM-DD'
• BOOLEAN - TRUE or FALSE
• NUMBER(P,S) - a number with precision P and scale S.

When a value is added to the XXX_EXTRA_VALUES table it is then possible to validate the following:

• That the value for extra_id exists on the XXX_EXTRA_NAMES table.
• That the contents of extra_value matches the specifications in extra_type.

This then takes care of problems #1 and #2 identified in the introduction.

The contents of these two tables can be maintained using the standard LIST1 forms family.

It is also possible to include data from these extra fields in the output from a standard call to getData() on the main table by modifying its _cm_pre_getData() method using code similar to the following:

function _cm_pre_getData ($where, $where_array, $parent_data=null)
{
    ....
		
    // create mapping details: 'external_name = internal_name'
    $mapping['length']     = 'LENGTH';
    $mapping['width']      = 'WIDTH';
    $mapping['height']     = 'HEIGHT';
    $mapping['weight']     = 'WEIGHT';
    $mapping['weight_uom'] = 'WEIGHT-UOM';
    $mapping['plan_date']  = 'PLAN-DATE';
		
    foreach ($mapping as $ext_name => $int_name) {
        // obtain values from PRODUCT_EXTRA_VALUES table
        $pattern = '/\bAS[ ]+'.$ext_name.'\b/i';
        if (!preg_match($pattern, $this->sql_select)) {
            $this->sql_select .= ", (SELECT extra_value FROM product_extra_values AS pev"
                                  ." WHERE pev.product_id=product.product_id"
                                    ." AND pev.extra_id='$int_name'"
                                    ." LIMIT 1"
                                  .") AS $ext_name";
        } // if
    } // foreach
    
    return $where;
		
} // _cm_pre_getData

It is also possible to have this extra data included in the standard call to updateRecord() by putting code into the _cm_post_updateRecord() method similar to the following:

function _cm_post_updateRecord ($fieldarray, $old_data)
{
    $product_id = $fieldarray['product_id'];
		
    // create mapping details: 'external_name = internal_name'
    $mapping['length']     = 'LENGTH';
    $mapping['width']      = 'WIDTH';
    $mapping['height']     = 'HEIGHT';
    $mapping['weight']     = 'WEIGHT';
    $mapping['weight_uom'] = 'WEIGHT-UOM';
    $mapping['plan_date']  = 'PLAN-DATE';
		
    // convert field names from 'external' to 'internal' and add/update/remove from the PRODUCT_EXTRA_VALUES table
    foreach ($mapping as $ext_name => $int_name) {
        if (array_key_exists($ext_name, $fieldarray) AND array_key_exists($ext_name, $old_data)) {
            if ($fieldarray[$ext_name] != $old_data[$ext_name]) {
                if (!$this->updateExtraData($product_id, $int_name, $fieldarray[$ext_name])) {
                    return $fieldarray;
                } // if
            } // if
        } // if
    } // foreach
		
    return $fieldarray;
		
} // cm_post_updateRecord

// ****************************************************************************
function updateExtraData ($product_id, $extra_id, $extra_value)
// add, update or remove a record from the PRODUCT_EXTRA_VALUES table.
{
    $dbobject = RDCsingleton::getInstance('product_extra_values');

    if (strlen($extra_value) > 0) {
        $data['product_id']  = $product_id;
        $data['extra_id']    = $extra_id;
        $data['extra_value'] = $extra_value;
        $data = $dbobject->insertOrUpdate($data);
    } else {
        $data['product_id']  = $product_id;
        $data['extra_id']    = $extra_id;
        $data = $dbobject->deleteRecord($data);
    } // if
    if ($dbobject->errors) {
        $this->errors[$dbobject->getClassName()] = $dbobject->errors;
        return false;
    } // if

    return true;

} // updateExtraData

Note here that a blank value will result in the record being deleted.

Conclusion

As you should see problems 1 and 2 identified in the introduction can be eliminated by a small amount of extra coding. Problems 3 and 4, while requiring another bit of extra coding, need only be defined once within a separate customised class file, and when dealing with a software package which is used by multiple customers this is the safest way to keep each customer's customisations outside of the core package so that they have no impact on other customers.

Just because you discover problems with a particular implementation of an idea does not necessarily mean that the entire idea is bad. You should be aware that an idea could be implemented in several ways, and that the problems could be alleviated by making slight adjustments to your implementation. I have written about this idea before - in Singletons are NOT evil I showed that each of the arguments against using a singleton was in fact due to a particular implementation. By using a different implementation I achieved the objective of using a singleton but without those nasty side effects.

When you come across a particular problem you should remember the following piece of ancient wisdom:

You cannot move mountains if you believe them to be mountains.
You must think of them as collections of small stones,
Which can be moved one at a time, and then reassembled.

-- The Tao of Meow

If you can break the huge problem (the mountain) into a collection of much smaller problems (individual stones) then you have a much better chance of solving each of these small problems. Solve enough of these and, in time, the huge problem simply disappears. This is the approach that I have used successfully for over 30 years, so I advise you to try it.

Here endeth the lesson. Don't applaud, just throw money.