I have a table with the following structure:
Table_1 --- UserId (int) --- UserName (varchar)
A user can have a relation with other users. Meaning, a
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:
Table_2 --- 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
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?