Traditional database design vs. key-value table - MySQL

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:

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:

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:

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.