Interesting discussion. I certainly see where EAV design is effective for front-end systems which expect to receive large numbers of unknown values from multiple sources or when the query objectives are for display of a single response set (to display a risk score on the screen, for example). The time and resource savings for redesign of data models are evident. Great for "getting data in". However, what this practice really does is delay the integration decisions to a later step in the process.

We data warehouse developers struggle with name value pair design in our front end (source) systems. One of the primary purposes of a good data model is communication of the business relationships among the logical entities about which the business has an interest. The EAV model cannot be used as such a communication tool. On the data warehousing side and the downstream reporting side the intent is to make the data available to end report users and decision makers in an unambiguous easily queryable form that is tuned to return millions of rows in aggregated form. We are tasked with analyzing and dissecting the value pairs and creating a logical business model that is representative of the integrated content. Its really a case of pay now or pay later.

We took an approach in our datamart (star schema) where each fact record represented an assessment and had a FK to our question table for each question. This of course reduced the number of storage rows by raising the grain from a single response to a set of responses, but had the negative and limiting effect of creating multiple joins, which at 24 currently is slowing performance not to mention confusing our OLAP Reporting tool developers.

We will now create a view that performs the the joins for them, but I am still searching for an approach for the survey problem that combines reporting performance and business clarity with the lowest possible storage requirements. Look forward to T.Asfaw's second article and hope you will address the impact of EAV design on the downstream Data Warehousing and Reporting arenas.