SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Many to Many relations...I think

    Hello, I have an issue.

    I have a users table (id,name)
    Eg. 1, cmircea

    Another table called features (id,feature)
    Eg. 1,hair colour; 2,eye colour; 3,interests

    Another table called attributes (id,feature_id,attribute)
    Eg.
    1,2,blue; 2,2,green; 3,2,black
    3,3,computers; 4,3,music; 5,3,reading


    How do I make the connection between users and attributes table?
    So basically I want to set for every user hair colour, eye colour, and
    interests?

    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    create another table called userattributes with columns userid and attributeid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I got that table.
    But there is another issue.
    For example, a user can have only 1 eye colour (which is a select with eye colours), and also can have multiple interests (music,reading,etc) which should be checkboxes, right?
    Do I have to do that manually?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    with this structure, yes, you'd have to do that manually
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there another easier way to build the structure?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    depends on how "generalized" you want it to be

    i would put eye colour and hair colour right into the users table, since these attributes can have only one value per user

    that means your only remaining feature is interest, so i wouldn't have a generalized attributes table, i'd have an interests table, with a userinterests many-to-many table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, I could do that, but the problem is these are not the only attributes...I wanted to make it as dinamically as possible, I'll think of something, thanks for your time.

  8. #8
    SitePoint Addict
    Join Date
    Feb 2000
    Location
    Vilnius, Lithuania
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can create 2 feature tables instead of one. One for unique features (like eye color) and other for multiple choice features (like hobbies). Then you can combine them for selecting in one view (depending on you db server). Just an idea.
    SPAW Editor v.2 - web based wysiwyg editor for PHP and ASP.NET.
    Opera and Safari support, tabbed interface, floating toolbar...

  9. #9
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Having a table for users and a separate table for things like, say, hair-colour, eye-color, weight, height and other similar things, is forcing a join via a foreign key when it is not needed.

    User 1's hair-color, eye-color, height, weight, are totally and solely dependant on user 1, and nothing else. So they belong in the same table as user 1's name. Hair-color has no other attribute other than the color. Nothing is functionally dependant on it, and it is only functionally dependant on the user id.

    Interests - music, reading computing, databases are different. A user has an unspecified number of interests, and many users share the same interest. So this is a many to many relationship, and thus should be split off into another table or two.
    user---<user-interests>---interest

    user
    idU, name, other attributes
    1 fred, blue, brown
    2 john, green, black
    3 rose, blue, purple

    interest
    id, topic, description
    1 databases, relational
    2 reading, books
    3 forums, webdesign
    4 databases, flatfile
    5 reading, magazines

    user-interests
    idU , id
    1, 1
    1, 3
    2, 3
    2, 2
    3, 3
    3, 2
    3, 5

    and so on.


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
  •