Creating a Profiles model

This is both a question about a query to return the information and/or the model that I have laid out. The white parts of the ERD are already in place and cannot be changed. The yellow items are what I have come up with so far. See the attached image please.

As it stands now (again, cannot be changed) a lead is a sales lead and a sales lead may have characteristics tied to the person in the lead. These are things like age, location, etc. The characteristics table is already in place and does not need to be recreated for what I'm trying to do here.

I want to create groups of characteristics called a profile. For instance, a lead may have characteristics A, B, and C. A profile may be defined as containing characteristics A and C only, or A, B, and C, etc. I want to find all profiles (both of the above would work) that fit this lead.

I considered a table containing a ProfileID and a column for each characteristic, then a true/false for each of the columns for each profile. The problem with that is that if we add another characteristic the ProfileCharacteristics table itself must be modified and possibly queries rewritten, etc.

So, if what I have suggested in the attached ERD works can someone help me with linking a profile back to a lead in a query? If not, how do you suggest that I model this?

This is in SQL Server 2008, although I doubt it matters.

Thanks so much!

the ERD looks okay except that the characteristic table does not actually have a foreign key

am i right that you will feed into the query a leadid value? and you want to find all profiles that "fit" this lead?

please explain in more detail what "fit" means

i'm having a hard time understanding why a profile with A,C fits a lead with A,B,C

Sure, no problem.

The characteristics table is just a list of info about a lead. I'm also using it the same way for profiles.

Yes, the lead ID will be part of the query. Really the guts of it is that I want a set of ProfileCharacteristics that are a subset (or full matching set) of the LeadCharacteristics for a given Lead.

The profiles are just combinations of characteristics. The reason A, C fits a lead with A, B, C is because the lead does include A and C. In this case the lead would fit two profiles. Does that help?

A coworker just suggested that I look into the PIVOT statement in SQL Server. Any thoughts on that?

not really

if i was looking for a love interest, and the characteristics i was looking for were cute, smart, and female, i would ~not~ be interested in a profile which was cute and smart but belonged to a guy named bruce

none that i could repeat without using NSFW adjectives

Ok, then let's simplify. Let's say that the lead must match all characteristics. A lead with A, B, and C doesn't match a profile of just A, C. It does match a profile with A, B, and C. Does that make it easier or any different?

Why not the pivot? I did some research and it looks like you have to have an aggregate function and I'm not sure what the aggregate would be in this case. Can you help me there?

Thanks!

that's not a simplification, that's a change in your specs

just because somebody (me) doesn't understand why your specs are the way they are, doesn't make them wrong

i'm still trying to figure out what a lead is, and why you're trying to match a lead to a profile instead of the other way around

because it's a hammer in search of a screw

A lead is something that we have generated through a much more detailed mining process that has been used for a long time. The profile is something we're considering using in the mining process if we can tie groups of characteristics to our success rates. We want to evaluate success rates by profile or group of characteristics. I should have clarified here.