SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2005
    Posts
    436
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    One to Many Best Practice

    I'm writing an application that requires me to keep track of each user's multiple email addresses. I have one table for users, and one table for email addresses.

    Often times, I will need to select all of the email addresses for a single user. What is the best way to set this up?

    I can't decide between two options, but if there's something better I'd like to hear that as well:

    I can keep an email_addresses column in the user table that holds the email_address_id's of the email_addresses table. So after I query the user table, I can use the email_addresses column (let's say it's comma separated) to run the second query. In this case, the email_address_id would be the only column included in the email_addresses table's primary key. So:

    Code PHP:
    $query = mysql_query("SELECT * FROM user WHERE user_id=1");
    $row=mysql_fetch_assoc($query);
    $query2 = mysql_query("SELECT * FROM email_addresses WHERE email_address_id IN (".$row['email_addresses'].")");
    while($row2 = mysql_fetch_assoc($query2)){
        //Use my email addresses
    }

    Or, I can keep a user_id in the email_addresses table. For this case, I would include both the email_address_id and the user_id in the primary key for the email_addresses table. Note, that I'll still need to query the user table for other information about the user, so I still need to run two queries. The code for this case:

    Code PHP:
    $query = mysql_query("SELECT * FROM user WHERE user_id=1");
    $row=mysql_fetch_assoc($query);
    $query2 = mysql_query("SELECT * FROM email_addresses WHERE user_id=1");
    while($row2 = mysql_fetch_assoc($query2)){
        //Use my email addresses
    }

    For MySQL, which is better as the tables get very big?

    Thanks,
    e39m5

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    comma-separated is wrong if you ever need to search by email address

    the email addresses in the 2nd table don't need an id, just the email itself and the user_id that it belongs to
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if one user have only one email,you can create one table include id,username ,email column;if one user have many diffrent emails,you should create another table email includes user table id.when you nee to query singal user's email you can user SQL JOIN two tables.select..from...left join...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
  •