Some social networks have a concept of friendship which is mutual by definition. You can send somebody a “friend request”, and if they accept it, then you two become “friends”.
(There is a separate concept of followers, that could be unidirectional: you could be my follower, but I may or may not be your follower; this is trivially modelled.)
I’d like to discuss a mutual friendship today, because it poses a couple of interesting questions. First, let’s begin with the requirements. We have the following typical operations and queries:
We can represent mutual friendship as a pair of directed friendships: Alice is a friend of Bob, Bob is a friend of Alice. We can design the following physical table:
Well, well, well, but isn’t it a (shudder) data duplication? Our code guarantees that for every row there is a corresponding symmetric row; we may think that this second row is redundant. Let’s investigate the single-row design then.
? Choosing a random order is not a good idea, because there is a chance that both possibilities would eventually get inserted, and what would that mean? And how will it affect our typical queries?