The problem we have is where to compromise. We have a very dynamic system, where the types can be changed at different points in time, and static db schemas. How do we maintain the mapping between the two and keep it efficient without major sacrifices in either?
For my own two cents, I think the long term solution lies in making the schemas dynamic (maybe not core schemas but definitely module schemas). If we finish implementing a solid data api then adding fields to a user_profiles table would simply execute the appropriate alter column statements. RAILS sort of works this way as well. The API is really in charge of the schema. I sort of have the feeling that when we try and store to much in these generic field extending (like profile fields) that we usually give up performance and make it really harder to get at the data. And certainly hard to optimize performance it with indexes. In my dream drupal, we would have node_<content_type> tables that would store all the added fields to a content type, and I as a conrib developer could get at those tables with simple SQL queries. I've just been bit to many times by scalability problems with the one table fits all approach.