Use EAV when the number of possible attributes is large, but the number of actual values is relatively small and cannot be predicted with any certainty.

An example of this is symptom capture for medical records. There can literally be hundreds if not thousands of possible symptoms, but the actual number of symptoms that any one patient might have is small relative to the total symptom pool.

In this scenario, it is impractical to have one column in the database for every possible symptom; many databases are not even capable of storing that many columns. Nor can you predict with any certainty how many symptoms any given patient might have, so you can’t just add an arbitrary number of columns to the database for actual symptoms.

If you do either of these things, you will find that most of your database is empty, because you will have a vast swath of fields that are not used. It’s a sparse matrix, basically. This is why spreadsheets are not actually stored as an array but as a sparse matrix, because there are so many empty cells otherwise.

The difficulty with EAV is in flattening the sparse matrix into a conventional format for reporting and other purposes, which is why such reports are typically done vertically, rather than horizontally. An invoice is an example of such a report: the product names are never listed out as columns, but rather appear as rows in the line item detail.

This is why sales inventory can also be considered a form of EAV (your customer is not going to buy every one of your products, but only a select few).