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 Free Fields Technique section below.
The difference between this technique and our car_event
table is that, for car_event
, the various attributes can all be related to a common subject, which is the event. On the contrary, free fields can store any...