SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    User attributes values in db table - 20 joins?

    I have a dating site where each user has own profile with more than 20 attributes like age, gender, hobbies etc. For this I have table user_profile where all attributes are in 1 row.

    For searching and match calculator, it would be probably better to have attributes values in int, better than having it in varchar. But now I am not sure how to print all attributes names if name is not also a value.

    So I was thinking to create two tables:
    Code:
    user_profile
    user_id | gendre | like_pets | drinks | body_type | eyes | hair_color | ...
    1       | 1      | 3         | 2      | 4         |  5   | 5          | ...
    2       | 2      | 4         | 2      | 3         |  3   | 5          | ...
    Code:
    user_attributes
    id | attr_group | value_name | attr_value
    1  | 1          | male      | 1
    2  | 1          | famale    | 2
    3  | 2          | Like pets | 1
    4  | 2          | Have pets | 2
    5  | 2          | Don't like pets | 3
    6  | 3          | Blue      | 1
    7  | 3          | Gray      | 2
    8  | 3          | Green     | 3
    9  | 3          | Brown     | 4
    ...

    But that means I need to create 20 queries to print all value names for all attributes. For example to print eyes color I would need
    SELECT * FROM user_attributes WHERE attr_group=3
    then again the same for gender, and for each other attribute. Or I can use joins, but I would need 20 joins in 1 query. Is that a correct way?
    Dilster.com - Dating blog where you can find new dating tips, advices and ideas or your partner.

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    three tables
    1) users
    2) attributes
    3) user_attributes

    table three has userid, attributeid and has a new row for each attribute a user has.

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    tnx, it makes sense
    Quote Originally Posted by guelphdad View Post
    three tables
    1) users
    2) attributes
    3) user_attributes

    table three has userid, attributeid and has a new row for each attribute a user has.
    Dilster.com - Dating blog where you can find new dating tips, advices and ideas or your partner.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •