Best way to design Users and Contacts

Question

I have two tables (e.g.):
Users (ID, firstName,middleName, lastName)
Contacts (ID, userID, serialNo, phoneNumber, eMail).

I shall be communicating (sending messages) to Users via phoneNumber or eMail or both and save it in Database (e.g.).
Log (ID, userID, contactID, message, onPhoneOrEmail) where, say, last field stores, say, 'p','e' or 'b', for phoneNumber, eMail, or both.

So, when I check logs, I can get to know that which message was sent to which email/phonenumber.

Problem: What to do when Users change their contact details?
If I update the Contacts table, I lose Log, because the messages were not sent to the new number. If I store the number or email in Logs, it would be to much of data to store (on large scale as compared to just one character).
Last: If I add new Contact with +1 serial number (serialNo - field), will it be feasible ? What about performance issues ? (uniqueness is not required, Users can changes number or email as many as times as they want - these are just for communcation).

I read this and this, but could not get an approriate answer regarding performance/methodological issues.

Please guide.

CONTACTS
| 1 | 1 | 1 | 123456 | [email protected]
| 2 | 1 | 2 | null | [email protected]
| 3 | 1 | 3 | 987654 | null

Not quite enough information to respond. Why are users and contacts separated into two tables? If you need to know which messages were sent to each each phone number, you will need to ensure phone number does not change. — EllieK
@EllieK Log table : to know how many messages have been sent to a User and on which phone number/email id or both. Users can have multiple contact details. I have added sample data for more info. — V A Thomas

Nicola Pellicanò Nicola Pellicanò · Accepted Answer · 2017-08-24T14:48:56

If you say that a User can change his contact detail this means that you inverted the dependency. The User has the Contact, so it is reasonable to associate to a user a contactID and not the opposite. Now, a User can change e.g. phone whenever he wants, and at the same time it make no sense for the same phone number to change its user at some point.

So it would be turned like this:

User(ID, firstName,middleName, lastName, contactID)

Contact(ID,serialNo, phoneNumber, eMail)

Log (ID, userID, message, onPhoneOrEmail).

You don't need both userID and contactID on Log. Remember that one is foreign key for the other (transitive dependency).

EDIT If you need to store multiple contacts per User, keep your schema but change the Log in

Log (ID, contactID, message, onPhoneOrEmail)

From my point of view, when you need to change contact of a user it means that you will remove one and add another. If you have never sent any message to that contact you are removing, you have no reason to keep it in memory, otherwise, if you need a record you have to maintain the contact information in memory even after you have replaced it (maybe a column saying it is invalid is preferable). This is already the default behavior in mySQL (ON DELETE RESTRICT).

Best way to design Users and Contacts

2 Answers