0

what is the most efficient way to make a "contacts" table in mysql database.
like a user can change the relationship between him and another user.
like i cant figure out how the database should look to be most eficiant. ive been through two ways. the first was a column for every user and then a row for every user but that would be too much spaces. i only need to hold one field that shows the relationship between the users.
then i thot of having a row in the table for every person
one column would be like person1 then th next column would be person2 then the 3rd needed column would be relationship which would be set to 1 - 5 depending on the relationship of the user.
but that would be 2 rows for one relationship right?
is there another way>? thats easier db design?

2
Contributors
11
Replies
12
Views
7 Years
Discussion Span
Last Post by debasisdas
0

What about storing the users(contacts) and the relationship details in separate tables and linking them using PK and FK.

0

That depends on your requirement.

ex
USERS
----------
ID
FIRST_NAME
LAST_NAME
........
........
etc

RELATIONSHIP
---------------------
R_ID
U1_ID
U2_ID
R_TYPE
R_DESC
........
........

etc

0

That depends on your requirement.

ex
USERS
----------
ID
FIRST_NAME
LAST_NAME
........
........
etc

RELATIONSHIP
---------------------
R_ID
U1_ID
U2_ID
R_TYPE
R_DESC
........
........

etc

right. ok. but what about the mysql queries to pull out all the users that a user has a relationship with?
then there would be two columns one fore the user to be in u1_id and then the user would be in u2_id? is there a way to keep to to one column because im worried about checking and having two columns....

0

no but the last table it has two colums for making a connection. there a relationship column then theres the user who the relationship

0

You can always validate that using a simple procedure to create a new relation after checking for existence of duplicate.

0

no i mean liike why have two rows for two users when the relationship between them is the same. like one row for two users using maybe 2 columns that are user1 user2
but im lost from there

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.