Contacts table with multiple entries

database-designduplicationrelations

Here is a part of our Database: 

Table: Users
ID            INT
First Name    VARCHAR
Last Name     VARCHAR
Email Address VARCHAR
...

Table: Contacts
ID            INT
User_id       INT (Foreign Key)
First Name    VARCHAR
Last Name     VARCHAR
Email Address VARCHAR (Unique)
Phone Number  VARCHAR
Address       VARCHAR
...

A user can have multiple contacts (One-to-many relationship).

It is possible that a contact of one user is also a contact of another user. And the second user may try to enter different phone number/address (but same email address). I cannot allow duplicate email addresses.

One possible solution is to split contacts table into 2 tables:

Table: Contacts
ID            INT
Email Address VARCHAR
...

Table: Contact_attributess
ID            INT
User_id       INT (Foreign Key)
Contact_id    INT (Foreign Key)
First Name    VARCHAR
Last Name     VARCHAR

Is this the best way to reorganize my DB? Do you have other solutions?

Best Answer

user table
---------
USERID
EmailAddress

User Details
-------------
USerDetailID
USERID (FK)
FirstName
-----

USER_Contact_Mapping
--------------------
USERcontactMapID 
UserID
contactID

User table hold info of both User and contact.This table can by utilise to hold other info which won't be duplicated.

User Details table hold info of both User and contact.This table contain multiple detail of same user. Example

USER_Contact_Mapping contain relation between USERID and ContactID.This table is must.

Related Question