• I would suggest that the EAV is where the types are taken out of the database entirely and only the application context would be able to decipher the significance of what entity type id x means.

    Which is fine when the application doesn't want to 'view' the data sorted by something other than the order that IS in the data model.(eg clientid order). Getting the answers for a customer; doesn't need the question types in the schema and has the advantage that if the customer has another set of 'typed' data that needs to be stored that there is no schema level change. (or change to SQL to extract for this customer survey)

    For example entity response type id 1-32 could be the original question responses that the application stored about the client. You could just as easily create response entity ids 33-66 that store a physicians responses to a different set of questions that had a contextually different meaning determined by the application (but about the same client). This would be to say that the database schema did not describe the applications data model or rather the bit of it where 1-32 means client responses and 33-66 means physicians and the application / stored procedure could then extract the two contextually different data sets internally knowing by id what the responses meant.

    Great so we have just allowed for the application to have the flexibility to change its data model without any change to db schema. This is at the cost of needing to recreate the part of the data model that is not in the schema each time we present it logically to the user. For simple parts like a flat list there is little effort or even no schema to be reconstructed in producing a flat list for a specific range of a larger flat list. For more abstracted types the reconstruction becomes non trivial.

    As you add more and more abstracted types (and layers of abstraction) you end up reducing the selectivity of the index (and increasing the number of seeks by the level of abstraction). For example the selectivity of the and client-question-response index by question id. At some number of layers of abstraction and amount of data being indexed the work reconstructing the actual types becomes measurable. I doubt a single abstracted type is going to do anything measurable.

    An EAV becomes a target for a change to a indexed denormalized relational structure with schema defined types (tables) similar to your proposal, if the application ends up with time critical grouping demands on the E's for a large number of V's in a grouping order very different to the order indexed (or initial grouping). Ie the ability to view clients by their id is as important as clients by their response to question 1 a survey done in 2005.

    Alternatively if you are happy that the relationships between the client type the question type and the survey type are unlikely to change then putting it into the db schema only adds the risk of needing to change the schema (as well as the application change which would be mandated anyway) if these relationships change in their actual as use.