2 ms·
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
by 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 agoOh I see. Hmm... I wonder what OP meant?
- withinboredom 4y agoYep, 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 agoAh, 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'