I have a table with the following structure:

--- UserId (int)
--- UserName (varchar)

A user can have a relation with other users. Meaning, a UserId from Table_1 can have a relation with other UserIds. So, the question is, how should I structure a second table to implement this?

The second table needs to contain the UserId of a user and other UserIds that the user has relations with. So, should I structure the second table as follows:

--- UserId (int - Primary Key)
--- OtherId (varchar)

In the structure above, in OtherId I was planning to store all other UserIds in conjunction, like one column would contain all other UserIds that the principal UserId is related with, but each UserId in the OtherId column would be separated with a comma (,). The way I would retrieve them would be by taking the value from the OtherId column and then, in code-behind, splitting the string by (,) into an array. Then I would use each value from the array to retrieve the UserName from Table_1.

This was the idea I had. I tested it on a database of 300,000 records in Table_1 and one relationship string consisting of almost 3000 UserIds in the second table. This took a significant amount of time (10s) to process all the data, including the splitting of the string.

I am almost certain that my idea is nowhere near to ideal practices. Can someone just give me some ideas on how I should go about this?

  • Can you clarify if you use MySQL or SQL Server? No need to have both tags if you use a a specific DBMS. – ypercubeᵀᴹ May 3 '19 at 11:58
  • I am using Sql-Server, however, i am allowed to switch between databases and finally choose the one with the best performance. Which one do you suggest by the way? – Aousaf rashid May 3 '19 at 12:13
  • 1
    You might find of help my answer to this question, I believe this post can be considered a duplicate. – MDCCL May 3 '19 at 13:29

It seems what you're describing for Table_2 is a linking table for a self-reference for Table_1. In the case where there is only one possible reference for each UserID, you would store this information on Table_1 itself (UserID and ReferenceUserID with a foreign key back to UserID).

What you have described, however, is a many-to-many relationship, where a UserID can have many related UserIDs and vice-versa. In this scenario, Table_2 should not use a string holding all of the related userIds for your principal. There are several reasons for this, but the two most important are:

  1. Performance will be terrible when querying because you will have to do string manipulation to split the CSV string into rows, then join on those extracted IDs
  2. You will not be able to store information about individual relationships.

To illustrate #2 (using hypothetical use cases for the table), say this database holds information about family relationships. Users can be associated with 0 to many other users in their family tree. If you want to store information about each relationship (not DB relationship, but IRL) then each relationship should be a separate row.

For example, Dave has a UserID of 1. Pete is Dave's brother and has a UserID of 5. In Table_2, you would store Dave's UserID in the UserID column and Pete's in the OtherID column. You can then add fields to record the relationship (Brother) and other information (maiden name for marriages etc).

With the CSV column for OtherID, you could not efficiently store this information for the potentially many relationships a single UserID might have.

Therefore, consider this as the base design for the second table:

  UserID          int FOREIGN KEY REFERENCES Table_1 (UserID),
  ReferenceUserID int FOREIGN KEY REFERENCES Table_1 (UserID),
  PRIMARY KEY (UserID, ReferenceUserID)

You can add other columns to this setup to store additional details about each relationship, as discussed above.

| improve this answer | |
  • So, what should i do then? If i use Table_1 itself to hold ReferenceId as a foreign key pointing back to the UserId, how am i supposed to add multiple ReferenceId fro each UserId? – Aousaf rashid May 3 '19 at 9:57
  • @zackraiyan hold those relations in a second table. Something like Table_2 (UserID, ReferenceUserID) as Handy has said above – George.Palacios May 3 '19 at 10:47
  • @George.Palacios, in the 2nd table, should UserId and ReferenceId both be foreign keys? – Aousaf rashid May 3 '19 at 10:47
  • 2
    @zackraiyan yes, both referencing Table_1 (UserId) – George.Palacios May 3 '19 at 10:48
  • 2
    As the OP kept asking questions, I took the liberty of explicitly showing in your answer what the second table should look like. Feel free to edit further as you see fit. – Andriy M May 3 '19 at 11:42

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