Database Design Using Key-Value Tables
(Page 1 of 5 )
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.
Key-value tables - what is all about?
Consider the following example table:
CREATE TABLE EMPLOYEES (
ID INT NOT NULL,
NAME VARCHAR (10) NOT NULL,
SALARY INT NOT NULL);
ALTER TABLE EMPLOYEES ADD CONSTRAINT EMPLOYEES_PK PRIMARY KEY (ID);
INSERT INTO EMPLOYEES (ID, NAME, SALARY) VALUES (1, 'Dany',
4000);
INSERT INTO EMPLOYEES (ID, NAME, SALARY) VALUES (2, 'Brit',
5000);
INSERT INTO EMPLOYEES (ID, NAME, SALARY) VALUES (3, 'Sharon',
1000);
The above script produces the following table:
EMPLOYEES |
ID | NAME | SALARY |
1 | Dany | 4000 |
2 | Brit | 5000 |
3 | Sharon | 1000 |
This table holds employee description, specifically employee name and salary.
You might have noticed that there is an additional column called “ID.” We use this column as a primary key, i.e. a unique identifier of each row in the table, which can never be set with a NULL value.
In a general development process, after collecting the requirements and designing the model, the DBA would create this table, and the programmer would start coding with it.
In case the requirement changes, we will need to change the table design. For example, what happens if the customer adds a new requirement? We will look at the case of a customer wanting to include additional information which describes whether the employee has a company vehicle and the year that the employee started working with the company.
Next: Traditional database design vs. key-value table >>
More MySQL Articles
More By Tal Olier
