Thanks for the article. I'm interested in understanding different design schemes. My question may have already been answered, but I'm not sure.

For the first time, I'm considering an EAV setup, and I would be very interested to hear opinions. My staff need to do a "risk assessment", but you can also think of it as a "survey" problem. I think there is a very good chance that the questions in the assessment/survey will vary over time as we refine our assessment process. Here's a subset of some of the questions in the risk assessment to give you a sense of what I am talking about:

The answer to "every" (as least this is what is claimed is true not only now, but in the future) question is "none, minor, moderate, Severe, or n/a". Also note that the questions/assessment categories are grouped together. For example, there are questions under the categories of: physical condition of the client, availability of shelter, hazards, sanitation of the home, etc.

Each answer has a point/percentage implication for the final "risk assessment" for that client. I'm not sure yet of the formulas, but I think the application may have to do things like: "for every question in this category of questions (say for all the "hazards" questions), give 5 for every "minor" answer, 10 for every "moderate" answer, 15 for every "sever" answer. Then add it all up.

Taking all that in, I was thinking that the following type of schema might make sense. But I'm nervous about it in light of this article. Note that this is bare-bones. I'm not trying to define the schema completely. I'm just exploring the idea of the basic set-up.

Table: QuestionList

qID - number for each question

qStartDt - when question becomes valid

qStopDt - when question is no longer valid

CategoryCd - link to another "lookup" table that lets us group questions according to a category

qDescription - the actual question (examples are given above)

Table: Assessments (the main table, with EAV)

ClientID - link to the client getting the assessment. I would expect a set of qID records for every client getting assessed.

qID - link to above table

AnswerCd - link to another "lookup" table that gives us the answers listed above (minor, moderate, etc). That lookup table would also include info for calculations.

This is a simplified version of what I would do, but the basic idea is there. It seems to me that this would be easy to query, do formulas, and not be such a bad performer. Also since the answers all follow the same format, I can do good data validation/restriction. But I do not have much experience with this type of design, especially with the amount of usage the design could potentially get.

Which brings us to this note. I'm not sure of the usage for this data. I would think that worse-case would put us at about 4 million records after 10 years. But that is only if the program ends up getting much wider usage than my little agency.

Thoughts?