there MUST be a way to create a many-to-many relationship between rows in the same table without using (shudder) numbered columns in your junction table. First of all, that’s not even first normal form. And second of all, what if you want the join to be directionally agnostic? What, do you drop one foreign key into “Item1” and another into “Item2” and then write a trigger that creates another identical row except with the keys swapped, doubling your data storage just so that everything can be searched both ways without hacky software workarounds? What if you have additional metadata about the connection, do you duplicate that too? What if the metadata isn’t directionally agnostic?!? How will you fix that when you’ve already gone to hell for data duplication???????
I can’t believe relational databases have been around for 50 years and yet stackoverflow STILL can’t provide me with answers to these questions!!!!
am I just using the wrong data structure here? could non-relational data or some other arcane solution save me? tagging @argumate @staple-madness @floydsir @etirabys and anyone else I can think of who knows something about code or even sufficiently advanced excel spreadsheets – @commonsensenovice if you think of anything useful you’re welcome to chime in. even @dreamwaffles because I think you know some people who might know.
hell, you know what? tagging @astolat because if anyone knows anything about weird data joins then SURELY it is she.
Help me, tumblr buds. You’re my only hope for making progress in this weird semi-personal project I picked up because there’s no work to do at work.
if it was truly bidirectional you could order the foreign keys so that item1 is always less than item2 so you only need to store each pair once!
I mean, this is a generality problem. nobody is FORCING you to use numbered names for your edges, if (for example) you were writing a Follows tables, you could have “follower” and “followee”, for example
you should find the names that most accurate describe the relationship you’re trying to model
@nightpool this is true but it doesn’t help much if “follower” and “followee” are conceptually the same thing.
Like, for example, say you’ve got a bunch of people in a room and they’re all shaking hands with each other, and you want to keep track of the handshakes. Naming your columns “Handshaker” and “Handshakee” is nicer-looking than “Handshaker1″ and “Handshaker2″, but unless you care about who initiated the handshake it’s not conceptually any better and might even be worse because it implies a directional relationship that isn’t there.
Plus you’re going to get duplicates if two people shake hands twice with a different person initiating each time, which is fine if you are actually interested in who initiated but if you don’t care then that’s just junk you have to deal with somehow.
Argumate’s suggestion of sorting the foreign keys would prevent duplicates of this type, but it would make naming the columns that way even more senseless because then “Bob” is always the handshaker when he shakes hands with “Charles” but he’s always the handshakee when he shakes hands with “Anna”. (and of course you wouldn’t even be sorting them alphabetically, you’d be sorting them by foreign key so the assignment is pretty much completely arbitrary, even though
by giving the columns meaningful names
you’ve implied that it’s not arbitrary). Horrid.
But the worst part is when you go to query everyone Bob shook hands with. You have to do two queries, one for each column! and then combine the results! And yes, all right, this is entirely possible and it’s not hard, but it’s ugly, all right? I hate it. In my head it looks like a couple of spiders engaged in an amorous embrace.
I’m starting to suspect there isn’t any way around it, though. sigh.
do you need two queries? can’t you just search for all rows where either name1 or name2 == Bob?
there MUST be a way to create a many-to-many relationship between rows in the same table without using (shudder) numbered columns in your junction table.