2

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?

  • 1
    Can a user have several email addresses (in your database)? – stefan May 26 '17 at 5:09
1
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.

0

You may want to add 3 additional tables (2 of them are linking tables), so it will be:

  1. Users
  2. Users_Contacts (to address multiple users in a single contact)
  3. EmailAddress (to address multiple users in a single email address)
  4. Users_EmailAddress (to address multiple users in a single email address)
  5. Contacts

On Users_Contacts table, it will the primary key of users and contacts table. On Email Address table, unique email can still be enforced here. On Users_EmailAddress table, it will the primary key of users and emailaddress table.

This way, you can have - multiple users on a single emailaddress, and - multiple users on a single contact

Also, you may want to remove first_name and last_name columns in contact table since information is already in users table.

0

Our database has the in-between table called not "User_Contact_Mapping" but User2Contact", because we think that's cute - for the word "to", we used a numeral 2! Also, it saves a few keystrokes.

Also, our "User" table has a "MainContact" column which holds a contact id. This has good and bad consequences; think about whether you need something like that. I think we've been regretting that decision for about 10 years. Enforcing that every user has a contact and every contact has a user, without making it impossible to input any new records... is tricky. Then a system user has the bright idea to type a new contact's details into an existing contact record...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.