Users vs. People - where to keep the email address? I'm designing an app similar to Facebook, called Headtome.  I want to have users that create public profiles, including email addresses.  Then, Mary can choose to message John, and that message goes straight to his publicly available email.  More creepily, if John and Mary mark themselves as in a relationship, then only John can view Mary's profile.

Here is (part of) the current database structure:

Table users:

id | username | password | person_id |
--------------------------------------


Table persons:

id | first_name | last_name | nickname |
----------------------------------------


Table person_emails:

id | person_id | email_address | is_main |
------------------------------------------


[I use a separate table for emails because a person may decide to show multiple email addresses.]

I argue that the users table should also have an email_address column.

The Pros


If a user forgets their password, we need an email address to reset it
A user might use a different email for logging in than the one they show on the profile
Or, they may choose not to show an email at all (don't message me)
A user's profile data is still available (through person_id)
A user email might be forever, whereas the public ones are temporary (job-related, etc.)
When Mary messages John, she's messaging John the Person, not John the User


The Cons


Managing two separate email addresses might get confusing for users
Or it could lead to duplication
The whole point is to create a profile, so persons is the main table
If John and Mary are dating, isn't it John the User who is allowed to see her profile?


What is the right approach here? Not that it should make a difference, but this is in MySQL.  Also, this is a fictional example exemplifying a real dilemma.
I would create a distinct emails table and use a surrogate key to instantiate the reference of the email address to the person and to the user.  The model would look like this:



This handles the need to associate the email address with the users as well as the persons, but eliminates the redundancy of using the actual email address text to instantiate the relationship.  It has the additional benefit also of ensuring you understand who is actually using a given email address, as you can place an alternate unique index on the email address text as a given email address in unique.  I think this kind of solution would eliminate the cons while still retaining all of the pros.