It's not a hypothetical, it's a database that I've seen before, I just thought it would be a good example of something similar that I wanted to do.
I was hoping you'd tell me that my current idea was wrong (As I knew it wasn't the best way, just didn't know the alternative.
I like your idea of having a friends table with 2 columns.
So if 1 person had 1000 friends, that persons id would appear 1000 times in the database, each time having the id of the the person he's friends with, right?
But how could you reciprocate that without duplicating data?
For example, if person A has 10 friends, he will have 10 records, 1 for each friend.
But what would be the solution for each of his 10 friends?
Would they have a record of their own, with him listed as their friend?
So rather than just having 10 rows, I'll have 100.
This quickly scales up if 100 people are all friends with each other.
Would you suggestion still apply or are there alternatives?
I guess you could do a piece of code to first say something like:
If person A is friends with person B, then person B cannot be made friends with person A.
What do you think?