Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2006
    Posts
    3

    Smile Table Design - One to One?

    Hi,

    I have been pondering this one for a long time now and would really appreciate your expertise.

    I have a table called user, it has an auto increment id column. It also contains columns for the username, password etc. AND columns for the users profile, about, interests etc.

    Now I am not sure whether this profile data should go in the user table or if would be a problem splitting it into a profile table with a one to one relationship.

    I would quite like to seperate the profile as I do see some logic. Right now I have a couple of other tables profile_comment, and profile_rating. A one (user) to many comments or ratings.

    I am thinking about setting it up with a user table, and profile table, and have the relationship for comments and ratings pointing at the profile table.

    Also, what about key names? For the comment or rating table, user_id could be named better probably? profile_id or profile_user_id. It is a bit odd with the one to one between user and profile.

    Hopefully some sense can be made from this. Please check out the attachment. (Another area which has a similar problem is blogs. A user can have one blog, with multiple comments, a blog table? a blog_comment table. What would you name the keys?)

    Thanks for your time
    Attached Thumbnails Attached Thumbnails er_question.JPG  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in your diagram, if each user can have only one profile, i would go with option 1

    as far as the column names are concerned, name them whatever you like, whatever makes sense
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Avoid 1-1 relationships. They are vestiges of a time when data storage was less efficient and data space was more expensive. These days, there is seldom a need for 1-1 relationships.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2006
    Posts
    3
    Thanks for the prompt replies.

Posting Permissions

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