-1

I am writing DB schema for my application. App's users have unique phone number. Every user can have multiple contacts. I have made user_contact table to have mapping between user and contacts. contact is itself a user. Now I came to know that every contact can have multiple phone numbers. Now I am thinking how this will be managed in DB. Do I need a new table? P.S. we are using mysql database with java 8.

gaurav
  • 189
  • 2
  • 6
  • how about a person table. each person can be a type (user or contact). then each person can have phone numbers. wanna see that in an Answer ? – Drew Aug 22 '15 at 18:08
  • As written, your question is vague: what do you mean when you say that a "contact is itself a user?" What is the purpose of a "contact?" You've sprinkled your question with a little bit of a solution and a little bit of requirements, but it would be easier to answer if you clearly stated your goals and requirements (why am I doing this/what do I need) separate from your previous attempts. – skeggse Aug 22 '15 at 18:54
  • @skeggse Let me try to make it more clear: It is an android application in which user can register using its phone number. Wen a user registers, its mobile contacts are pulled by the app and stored in DB. However, there could be a contact with multiple phone numbers. So how ill it be maintained in DB. Later this contact can also register into our application using any of its mobile number. I need do design DB schema for it. – gaurav Aug 22 '15 at 19:57
  • Don't forget to upvote, downvote, accept any answers with the Green checkmark whose ever it is if appropriate. That is our feedback for our efforts. – Drew Jul 03 '16 at 23:20

1 Answers1

0

There can be different ways. If you assume there will be limited phone numbers for each contact, you can add multiple fields one for each phone number, e.g. PhoneNo1, PhoneNo2, PhoneNo3.

If you want to keep it flexible, you can add a table contact_phoneNos with foreign key of Contact, and keep a record for one phone number.

I suggest the first solution as its commonly implemented, like Home No. Office No, etc in each field

Abdul Qayyum
  • 35
  • 6
  • Thanks for the reply. However, the problem is the uniqueness of user is the phone number. And lets suppose, now the contact tries to register into the application using any one of the phone numbers. Then how it will be managed in DB? – gaurav Aug 22 '15 at 18:19
  • You need to implement second option, when user registers, add a record in user_contact, and then in contact_phoneNo also at the same time. Maintain uniqueness in PhoneNo field, and check if there is already a user with the registering phone number. – Abdul Qayyum Aug 22 '15 at 18:21