CODEWITHSUNDEEP
mysqlsqldatabase-design
Sunjay Varma
Sunjay Varma

Reputation: 5115

Create Contacts Database Which Refers to Users Without Duplicates

My question is similar (but at the same time completely different) than this question: Contacts Database

The question is simple: How can I create a Contacts database table which stores a user id and contact id without duplicating keys.

For example, if I have a table called Contacts, it would have a column user_id, and a column contact_id.

Once I do that, it should be as simple as inserting the user and the added contact. Once that is done though, how do I select all of a user's contacts? Also, how do I narrow down the contact entry enough to delete it if need be?

Upvotes: 0

Views: 948

Answers (3)

Sunjay Varma
Sunjay Varma

Reputation: 5115

I ended up just creating a table with two foreign keys and then selecting them based on either of the fields.

For example (pseudo code--no specific language, just english):

Table Contact:
    user = ForeignKey(from user table)
    contact = ForeignKey(from user table)

Then whenever I need something from them, I'll check if the user field contains what I want and then I'll check if the contact field has what I want. This way I don't have to repeat records and I can still find what I need.

Thanks for your answers.

Upvotes: 1

Richk
Richk

Reputation: 131

there are two questions.

  1. " how do I select all of a user's contacts?"

So you have a table tbl_contacts(user_id, contact_id) both them are your primary key, so you won't get duplicated data.

I you want to list all contacts for user_id = ?

SELECT * FROM tbl_contacts WHERE user_id = ?

  1. You might want to clarify your second question "Also, how do I narrow down the contact entry enough to delete it if need be?"

You probably have some other properties belong to the user's contact and you will need to use those properties to search for.(eg.: contact_name or contact_number) and when you have 1 record as a result of a query you can -> DELETE FROM tbl_contact WHERE contact_id = ?

If this is not the answer you wanted please clarify your question.

Upvotes: 0

Jan S
Jan S

Reputation: 1837

Similar to the question in the link. You would have 3 tables.

Table 1
User_ID
Name
PK(User_ID)

Table 2

Contact_id
Address
Phone_Number
etc...
PK(Contact_id)

Table 3
User_ID
Contact_id
PK(User_ID, Contact_id)
Here you would have ContactID in table 2 as an autoinc column.
Also, when inserting in Table 3, MySQL would throw an error if there is a duplicate.

To select all of a users contacts, use:

SELECT * 
FROM Table_2 join Table_3 
   ON Table_2.Contact_id = Table_3.contact_id
WHERE Table2.User_id = <userid>

Or if you need it for a particular name, then 

SELECT * 
FROM Table_1 JOIN Table_2
   ON Table_1.User_id = Table_2.User_id
   JOIN Table_3
     ON Table_2.Contact_id = Table_3.contact_id
WHERE Table1.name = <user name>

Upvotes: 0

Related Questions