#1
  1. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22

    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!
    Attached Images
    Discontent is the first necessity of progress. - Edison
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22
    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?
    Discontent is the first necessity of progress. - Edison
  6. #4
  7. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22
    A coworker just suggested that I look into the PIVOT statement in SQL Server. Any thoughts on that?
    Discontent is the first necessity of progress. - Edison
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by mateoc15
    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?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by mateoc15
    A coworker just suggested that I look into the PIVOT statement in SQL Server. Any thoughts on that?
    none that i could repeat without using NSFW adjectives
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22
    Originally Posted by r937
    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
    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!
    Discontent is the first necessity of progress. - Edison
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by mateoc15
    Ok, then let's simplify. Let's say that the lead must match all characteristics.
    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

    Originally Posted by mateoc15
    Why not the pivot?
    because it's a hammer in search of a screw
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22
    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.
    Discontent is the first necessity of progress. - Edison

IMN logo majestic logo threadwatch logo seochat tools logo