Creating your MySQL Database: Practical Design Tips and Techniques

Planning for Changes

When designing a data structure, we have to think about how to manage its growth and the possible implications of the chosen technique.

Let's say an unplanned car characteristic—the weight—has to be supported. The normal way of solving this is to find the proper table and add a column. Indeed, this is the best solution; however, someone has to alter the table's structure, and probably the UI too.

The free fields technique, also called second-level data or EAV (Entity-Attribute-Value) technique is sometimes used in this case. To summarize this technique, we use a column whose value is a column name by itself.

Note

Even if this technique is shown here, I do not recommend using it, for the reasons explained in the Pitfalls of the ...