4 ms·

I bet they meant no foreign key constraints.

by chrchr 4y ago

deleted 4y ago
[deleted]
lisper 4y ago
OK, but that still doesn't make any sense because: > we had tables for each thing I take that to mean "a separate table for each class of thing." So the single "relationship" table had to somehow refer not just to rows in tables but to the tables themselves. AFAIK that is not possible to do directly in SQL. You would have to query the relationship table, extract a table name from the result, and then make a separate query to get the relevant row from that table. And you would have to make that separate query for every relationship. That would be horribly inefficient.
- criddell 4y ago
  I don't see why you would have to include the table name. I presume the calling code knows what they are looking for. For example, say we have a table of people and a table of books and a relationship table books-owned-by. If I want to see what's in your library I can select you in the relationship table and join with the books table to get a record set describing your library. Or is this not what the OP was describing?
  - lisper 4y ago
    The OP said, "we had tables for each thing (users, companies, etc) and a “relationship” table that described each relationship between things". I took that to mean that there was only one relationship table.
    - criddell 4y ago
      Oh I see. Hmm... I wonder what OP meant?
      - withinboredom 4y ago
        Yep, so a table for each thing with their own schemas. A user table, a company table, etc. Then a single relationship that defines all the relationships between all the objects in a giant many-to-many relationship. So to query a full relationship, you'd do a join from A -> relationship -> B, probably an inner join. But most of the time, it was usually just a singular join (to get a list of things to show the user, for example) since you'd already be querying in the context of some part of the relationship.
        lisper 4y ago
        Ah, that makes sense. So something like: select whatever from thingy, relation, widget where thingy.id = relation.id1 and widget.id = relation.id2 and relation.type1 = 'thingy' and relation.type2 = 'widget'

I bet they meant no foreign key constraints.

by chrchr 4y ago

I bet they meant no foreign key constraints.

deleted 4y ago

[deleted]

lisper 4y ago

OK, but that still doesn't make any sense because: > we had tables for each thing I take that to mean "a separate table for each class of thing." So the single "relationship" table had to somehow refer not just to rows in tables but to the tables themselves. AFAIK that is not possible to do directly in SQL. You would have to query the relationship table, extract a table name from the result, and then make a separate query to get the relevant row from that table. And you would have to make that separate query for every relationship. That would be horribly inefficient.

criddell 4y ago
I don't see why you would have to include the table name. I presume the calling code knows what they are looking for. For example, say we have a table of people and a table of books and a relationship table books-owned-by. If I want to see what's in your library I can select you in the relationship table and join with the books table to get a record set describing your library. Or is this not what the OP was describing?
- lisper 4y ago
  The OP said, "we had tables for each thing (users, companies, etc) and a “relationship” table that described each relationship between things". I took that to mean that there was only one relationship table.
  - criddell 4y ago
    Oh I see. Hmm... I wonder what OP meant?
    - withinboredom 4y ago
      Yep, so a table for each thing with their own schemas. A user table, a company table, etc. Then a single relationship that defines all the relationships between all the objects in a giant many-to-many relationship. So to query a full relationship, you'd do a join from A -> relationship -> B, probably an inner join. But most of the time, it was usually just a singular join (to get a list of things to show the user, for example) since you'd already be querying in the context of some part of the relationship.
      - lisper 4y ago
        Ah, that makes sense. So something like: select whatever from thingy, relation, widget where thingy.id = relation.id1 and widget.id = relation.id2 and relation.type1 = 'thingy' and relation.type2 = 'widget'