Databases
StackOverflow2013
Postsdbo
POIs there an elegant way to store a dual relationship (i.e. user 1 and user 2 are friends)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs there an elegant way to store a dual relationship (i.e. user 1 and user 2 are friends)
    primarykey
    data
    text
    <p>I've run into the same problem in two different pieces of work this month:</p> <pre><code>Version 1: User 1 &amp; User 2 are friends Version 2: Axis 1 &amp; Axis 2 when graphed should have the quadrants colored... </code></pre> <p>The problem is, I don't see an elegant way, using a RDBMS, to store and query this information.</p> <p>There are two obvious approaches:</p> <p>Approach 1:</p> <pre><code>store the information twice (i.e. two db rows rows per relationship): u1, u2, true u2, u1, true u..n, u..i, true u..i, u..n, true have rules to always look for the inverse on updates: on read, no management needed on create, create inverse on delete, delete inverse on update, update inverse Advantage: management logic is always the same. Disadvantage: possibility of race conditions, extra storage (which is admittedly cheap, but feels wrong) </code></pre> <p>Approach 2:</p> <pre><code>store the information once (i.e. one db row per relationship) u1, u2, true u..n, u..i, true have rules to check for corollaries: on read, if u1, u2 fails, check for u2, u1 on create u1, u2: check for u2, u1, if it doesn't exist, create u1, u2 on delete, no management needed on update, optionally redo same check as create Advantage: Only store once Disadvantage: Management requires different set of cleanup depending on the operation </code></pre> <p>I'm wondering if there's a 3rd approach that goes along the lines of "key using f(x,y) where f(x,y) is unique for every x,y combination and where f(x,y) === f(y,x)" </p> <p>My gut tells me that there should be some combination of bitwise operations that can fulfill these requirements. Something like a two-column:</p> <p>key1 = x &amp;&amp; y key2 = x + y </p> <p>I'm hoping that people who spent more time in the math department, and less time in the sociology department have seen a proof of the possibility or impossibility of this and can provide a quick "[You moron,] its easily proven (im)possible, see this link" (name calling optional)</p> <p>Any other elegant approach would also be very welcome.</p> <p>Thanks</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTed
    1. USTed
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIs there an elegant way to store a dual relationship (i.e. user 1 and user 2 are friends)
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POIs there an elegant way to store a dual relationship (i.e. user 1 and user 2 are friends)
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POIs there an elegant way to store a dual relationship (i.e. user 1 and user 2 are friends)
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you not use a Graph based database? Neo4j for instance. This would allow you to fully capture your graph relationships. You can also use Mongo and store it all in a Json Object.
      singulars
      1. POIs there an elegant way to store a dual relationship (i.e. user 1 and user 2 are friends)
      1. USSteve
    2. COThis is something not even 'document-oriented' aka 'NoSQL' databases support elegantly.
      singulars
      1. POIs there an elegant way to store a dual relationship (i.e. user 1 and user 2 are friends)
      1. USPeter V
    3. CO@steve yes, Neo4j is a real option (though i'm not a huge fan of their license). Neo4j abstracts this, but they've got to solve the fundamental problem somehow. I'd like to understand how.
      singulars
      1. POIs there an elegant way to store a dual relationship (i.e. user 1 and user 2 are friends)
      1. USTed
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload