Users vs. People - where to keep the email address?

Question

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.

Answer 1

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.

Answer 2

There's no need to have one table Users and another table Persons. I've never seen a social network that allows a single user to create more than one profile. True, there are people that keep multiple profiles, but these are done by opening multiple accounts (sometimes against the TOS).

You could use this model:

Table users:
user_id (PK)
username (IDX, unique)
firstname
lastname
password 
date_of_birth
(... other personal data ...)

Table user_emails:
email_id (PK)
user_id (FK)
email_address  
is_main 

An user would login to the site via his username and main email address (user_emails.is_main = TRUE).

Answer 3

I'll make an assumption first. A real world person could have more than one user in your system? If a real world person (persons table) can have (and should have) only one user, why do you allow a one to many relationship between those two tables? Or I've misunderstood your model or I would create in the Logical Data Model only one table for users AND persons with all the information related to that person, including the email address. If you want to store two different email addresses you can use two different columns, as far as you don't intent to store an unlimited number of email addresses (like it happens with the delivery addresses you can have associated to your profile in online shops). I would use a user_login NOT NULL column (what you call John user, which has to be an email address used for validation upon signing up) and another column email_contact (what you call John person), with an additional flag for allowance to be contacted or not. Anyway you may want to validate this second email as well.

Beside this, you can maintain a person_to_person relationship table for the dates and use it as an indicator to control if a person in publicly available or not and display its profile or not in the application layer.