I am using the ASP.NET membership database table structure where it has a Users table, Profile table and ProfilePropertyDefinition Table. The Profile table just has userID, propertyDefinitionID and propertyValue and then the property definition table defines the property type.
This structure basically allows me to add a new profile property to the database without having to add a physical column to the profile table. The idea behind it is really awesome and makes it much more dynamic.
My problem comes in when I need to query the users based on profile values. If I want to get all users in my database who are from California and are Male and make more than $100k/yr, how would I do that without using three subqueries? It seems like this database structure is efficient and dynamic other than the fact that I can't do complex queries on it easily.
Am I missing something in terms of how I can query this data easily without taking a huge performance hit in SQL? ...
Go to the complete details ...