Great discussion here.

I'm having a similar dilemma. We are building a social component to our website and the requirements are that customers and or external application would be able to create a profile in our system with user define fields.

I've created a profile table with the basic info. firstname, lastname, photo etc...

Now I'm trying to figure out the best way to include the additional user defined fields and is where the EAV model came up. I've read in numerous blogs/sites to not do it unless absolutely necessary.

From these readings, I have not found that anyone has a good alternative. There is always some major deficiency in it.

Would it make sense to store the user defined fields in an xml column? or in my case would EAV work efficiently where i would only store a subset of the data in an EAV model?

The other option I have is making the middle layer do the column manipulation. So if I went with the EAV model, my select would only be ...

select ProfileID, Entity, value

from EAV_Profile

where Profileid = 1

an not the horrible select with numerous joins and case statements.

The rest of the formatting and logic would happen in the mid layer. Does this seem like

Any additional advise would be greatly appreciated.