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:
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:
For MySQL, which is better as the tables get very big?