title: Many-to-many on same table
body: <p>Funny that I've never come across this yet!</p>
  
  <p>It never occurred to me that one could have a "many to many" relationship on one table- until I started working on a system where users can "friend" each other (social networking).</p>
  
  <p>A standard lookup table, at least in the way that I'm used to using it, isn't appropriate here. Lets keep it simple:</p>
  
  <p>User table has "id" and "name" column.</p>
  
  <p>User_relationship table has "uid1" and "uid2", representing users that are "friends" or "buds" or "pals" or "whatever else".</p>
  
  <p>It becomes apparent pretty quick what the problem here is- uid1 and uid2 are the same data type from the same column of the same table, meaning that unique keys become flawed.</p>
  
  <p>E.g.:
  uid1 = 1
  uid2 = 2</p>
  
  <p>Is the same as:</p>
  
  <p>uid1 = 2
  uid2 = 1</p>
  
  <p>And therefore could return 2 records, or 0 records if the query is performed wrong.</p>
  
  <p>In the spirit of designing a table well, I don't want to have to scan the entire table twice to check for existing values. </p>
  
  <p>Is there some sort of trick for handling this? This is a design question that has never occurred to me, and it irks me because I know that there's some simple trick to make it work.</p>
  
  <p>Before you ask, I haven't tried anything yet, because I already see that my favorite way of relating things (lookup tables) is insufficient for my needs here, and I need some help- I can't find anything on SO or Google :(</p>
  
  <p>Thanks in advance.</p>