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?
What about storing the users(contacts) and the relationship details in separate tables and linking them using PK and FK.
what would be the column then?
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
That depends on your requirement.
ex
USERS
----------
ID
FIRST_NAME
LAST_NAME
........
........
etcRELATIONSHIP
---------------------
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....
You can join the tables based on user ID in both the tables and retrieve the desired data.
no but the last table it has two colums for making a connection. there a relationship column then theres the user who the relationship
what you are concern about , is it the duplication of relations ?
yes
You can always validate that using a simple procedure to create a new relation after checking for existence of duplicate.
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
As i have already suggested earlier , you can restrict he duplicate entry by using a procedure.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.