0

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.

SAMPLE DATA: USERS | 1 | John | null | Cena |

CONTACTS
| 1 | 1 | 1 | 123456 | abc@xyz.com
| 2 | 1 | 2 | null | xyz@mnp.com
| 3 | 1 | 3 | 987654 | null

vat69
  • 123
  • 1
  • 10
  • 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 Aug 24 '17 at 14:50
  • @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. – vat69 Aug 24 '17 at 14:57

2 Answers2

1

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).

Nicola Pellicanò
  • 469
  • 2
  • 5
  • User can have mutiple contacts, plus I need records of the communication, so I cannot delete the old contact details. So I cannot have 1:1 relation between user and contact as per your suggestion. Kindly see the Added Data. As for log, transitivity will not work in 1:1 as per your schema. Log needs to know if Message was sent to ContactID 1 or ContactID 2. Thanks. – vat69 Aug 24 '17 at 14:59
1

Get rid of your Contact table.

Create a new UserPhone table (PK - ID, FK - User.Id, Phone#, ActiveDate)

Create a new UserEmail table (PK - ID, FK - User.Id, Email, ActiveDate).

It looks like SerialNumber is just an incrementer for one User's Contact data. If it is just an incrementer, ActiveDate should suffice as a replacement.

When phone, email information changes do not update existing record, add new record with today's date instead.

Your Log table will look like (PK - LogID, FK - UserEmail.ID, FK - UserPhone.ID).
No need for the PhoneOrEmail field. That information can be determined by presence of the FKs.

You might have some other design issues but this answer should get you on the right track.

Community
  • 1
  • 1
EllieK
  • 259
  • 4
  • 14
  • This solution will not work if I have, say, too many contact information, like phone number, mobile number1,2, current address line1,2,3, current state, current city, current pin code same for permanent addresses... this wouldn't be feasible. So what I intended to do was to add null to the rest of the values and add a row, I have a way to find the latest detail. But is it ok with storage ? – vat69 Aug 25 '17 at 07:03
  • Where did addresses come from? Didn't see anything about that in your question. Question may need some refinement. – EllieK Aug 25 '17 at 12:59
  • If you want to log the actual phone number or email that was contacted, you must save those emails and phone numbers somewhere. There is no magic, only data. – EllieK Aug 25 '17 at 13:49
  • 1
    My apologies but I did say "for example"... I didn't say those ARE the two tables. You solution will work if I have such condition, but what if is what I wanna know. Any generic solution is what I am looking for. Not just (User/Contact) scenario but in any other scenario similar to this. Thanks. – vat69 Aug 25 '17 at 13:52