One to Many Best Practice

Databases
1

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:


$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:


$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

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

3

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…