Parameter Enumeration Tables.

Usually when designing a database there are a few fields in various tables that define things such as type, category, group. Normally these would be sufficiently important to justify their own entity within the database – so for example you’d have a category table. This is a standard approach and makes for a flexible system.

Sometimes this approach isn’t sufficient, and the Entity-attribute-value model is a good choice when, and only when, you have lots of possible values that could be associated with a single entity record.

I’m a big fan of the first approach, it’s simple to understand, to draw and to implement.

However on a system that I inherited there were a number of tables containing lots of fields that were enums. I knew this was wrong, and started off converting to the standard approach, however it soon became clear that this was going to result in a lot of new tables as the parameters did not fall into convenient groups (such as gender, colour, status). However each entity would almost always have a full set of these, so the EAV was not the right approach.

So, I designed another approach. Upon reflection it’s close to EAV, but much more suited to my case.

Parameter Enumeration Tables (PET)

I wanted something that would allow

Referential integrity
Value to attribute mapping
Prevention of invalid values.

So after some head scratching I came up with the following.

CREATE TABLE `params`(
    `id`         int(11) NOT NULL, /* Unique ID used to reference the field in the entity*/
    `entity`     varchar(30) ,              /* entity (table) */
    `field`      varchar(30) ,              /* table field */
    `value`      varchar(30) ,              /* value */
    `symbol`     varchar(100) default '',   /* DEFINE for use in code - not to be changed lightly */
    `definition` varchar(100) default '0',  /* Can be anything - but usually text */
    `validate`   int(2) default 1,          /* Enforce validation */
    PRIMARY KEY  (`id`)
);

Parameter Enumeration Tables sample schema

The following is a real example of how I used this concept to provide a booking and ordering system, including airport transfers.

The simple approach is often the best

It may seem a pretty obvious solution and that’s because it is simple. Equally I’m sure it’s not revolutionary and isn’t going to win me an award for excellent design skills. However it does free up code from a lot of binding and logic that belongs elsewhere, especially with the `Parmams Typles` (see below).

Example table contents

The following defines the permitted values for the table `transaction` field `type`. The last entry is the PHP define.

INSERT INTO `params` VALUES (190, 'transaction', 'type', 'Order',       'TRANSACTION_TYPE_ORDER');
INSERT INTO `params` VALUES (191, 'transaction', 'type', 'Payment',     'TRANSACTION_TYPE_PAYMENT');
INSERT INTO `params` VALUES (192, 'transaction', 'type', 'Refund',      'TRANSACTION_TYPE_REFUND');
INSERT INTO `params` VALUES (193, 'transaction', 'type', 'Exchange',    'TRANSACTION_TYPE_EXCHANGE');
INSERT INTO `params` VALUES (194, 'transaction', 'type', 'Reservation', 'TRANSACTION_TYPE_RESERVATION');
INSERT INTO `params` VALUES (195, 'transaction', 'type', 'Pending',     'TRANSACTION_TYPE_PENDING');

So in the database all that’s left is to add a constraint to ensure the referential integrity (see note).

alter table `transaction` add constraint `FK_transaction_type` FOREIGN KEY (`type`) REFERENCES `params`(`id`) ON DELETE CASCADE  ON UPDATE CASCADE;

NOTE: The referential integrity at the database level is flexible as it only ensure that a valid param is used – within the client code when the validate field is set then I validate the contents during the set method of a field. It’s a comprismise but it works well.

Linking together entities with PET

In PHP the way I use it is with the static method CbfParams::setup which will define constants for all of the elements in the params table based on their definition record.

Once I’ve got all of the defines it becomes a breeze to pull out related elements for certain entities; e.g.

class CbfParamsAirportIterator extends CbfParamsIterator
{
    function __construct()
    {
        parent::__construct("SELECT * FROM params WHERE id IN (SELECT params1 FROM paramstuple WHERE params2=".TRANSACTION_GROUP_TRANSFERS.")"); 
    }
}

Params Tuples

This is something that was originally used to solve a requirement – to be able to ensure that a combination of parameters across tables was valid. Normally this would be inline code – but this way leaves the definition in the database which is where it should be.

CREATE TABLE `paramstuple` (
    `id` int(11)  NOT NULL auto_increment,
	params1 INT NOT NULL,
	params2 INT NOT NULL,
	value VARCHAR(200) NOT NULL,
	PRIMARY KEY (id)
);
INSERT INTO `paramstuple` (`params1`, `params2`, `value`) VALUES 
/* p1 is transaction p2 is item group */
 ('10', '200', '1') 
,('11', '200', '1') 
,('12', '200', '1') 
,('13', '200', '1')

Ok, the table is unreadable, and obviously some sort of lookup – but basically what it is doing is defining a tuple (x,y) value.

I’ve used the params tuple to do things such as defining which parts of the system can be accessed by which user level, for pricing, etc.. The beauty is that it’s a really simple way of getting a lookup table with definitions into my code in a flexible way that can let things be setup properly within the database.

Code to use the Parameter Enumeration Tables

Inside my database class I have a method that will decode the numeric value to that which is defined in the params table. This has the advantage that if need be this could be translated into other languages – whereas the awful enum approach doesn’t allow this.

    function get_field_as_text($f)
    {
        return params_decode_id($this->get_field($f));
    }

Validation of the PET enumeration value

During entity operations i.e. set the database layer will automatically perform validation (providing that the params definition has the `validate` attribute)

The param_validate function ensures that the table / field combination can contain the value passed – again here is another place that you can add flexibility by adding certain conditions and referring to the params tuples if needed

    function set_field($f, $v)
    {
        if (CbfParams::is_param_field($this->table, $f))
        {
            if(!param_validate($this->table,$f, $v))
                return cbf_error_value("EC0001","Entity: ".$this->table." - Invalid value [$v] for [$f]");
        }

Complete code for PET

The complete PET module source code This is also interlinked with the dbentity, which you can see in the db entity source code look for ‘params’.

Printer-friendly version