4 ms·

Postgres is powerful, I want a row in a table to have a relationship to one or more rows in _any other table_ in the database, and Postgres can't help with that

by porker 8y ago

Postgres is powerful, I want a row in a table to have a relationship to one or more rows in _any other table_ in the database, and Postgres can't help with that. That to me is a compelling reason to use something other than Postgres.

ralusek 8y ago
There are a few solutions to what you're proposing which might help. One solution is to have a generic reference property paired with another indicating which table to reference reference_id: '1234', reference_table: 'posts' reference_id: '4321', reference_table: 'comments' You can still benefit from indexes for joins in the same way that you would with actual foreign key constraints, but the downside with this is that you can't actually apply a foreign key constraint. Another solution you can look into, which I don't have much experience with myself, is table inheritance: https://stackoverflow.com/questions/3074535/when-to-use-inherited-tables-in-postgresql https://stackoverflow.com/questions/3074535/when-to-use-inhe... Another form of modeling the data in such a manner is called Exclusive Arc, which is that you just simply put all of the possible keys you might reference on the table, and add a foreign key on all of them. Then, when you need to make a reference, you just leave all but the one in use on that row as null values. If a "like" can go on a "post" or a "comment" or an "image", you would just have all 3 foreign key columns/constraints on the "like" table. And lastly, and probably best of all, is to simply use 1-2 tables for your entire data model for vertices/edges, and just treat Postgres as a graph database.
- porker 8y ago
  > One solution is to have a generic reference property paired with another indicating which table to reference > You can still benefit from indexes for joins in the same way that you would with actual foreign key constraints, but the downside with this is that you can't actually apply a foreign key constraint. Thanks, I had not thought JOINs possible on this - how would a query look? I'm guessing it works only for one type of reference_table. > Another solution you can look into, which I don't have much experience with myself, is table inheritance: https://stackoverflow.com/questions/3074535/when-to-use-inhe.. https://stackoverflow.com/questions/3074535/when-to-use-inhe.... Been there, tried it, banged my head on most of these (particularly the FK limitation): https://www.postgresql.org/docs/11/ddl-inherit.html https://www.postgresql.org/docs/11/ddl-inherit.html :) If it was fixed by Postgres it'd be awesome. I've lost the wiki page which was around on the topic, but it was unchanged for ~10 years, so really should've been marked 'wontfix'. > Another form of modeling the data in such a manner is called Exclusive Arc, > And lastly, and probably best of all, is to simply use 1-2 tables for your entire data model for vertices/edges, and just treat Postgres as a graph database. That's what I'm doing, with the 'node' table using JSON to store the node-specific data. I feel sooooooo dirty but it works. Yet to figure out schema evolution of the JSON in a controlled manner :)
  - ralusek 8y ago
    > Thanks, I had not thought JOINs possible on this - how would a query look? So this is for the structure with the two columns reference_id: '1234', reference_table: 'posts' reference_id: '4321', reference_table: 'comments' So let's say that this is the `likes` table that can like either `comments` or `posts`, here are some queries: // Get comments and their respective likes SELECT * from comments JOIN likes ON comments.id = likes.reference_id; // Get posts and their respective likes SELECT * from posts JOIN likes ON posts.id = likes.reference_id; // Get likes and their referenced comments SELECT * from likes JOIN comments ON comments.id = likes.reference_id; And so on.

by porker 8y ago

ralusek 8y ago

There are a few solutions to what you're proposing which might help. One solution is to have a generic reference property paired with another indicating which table to reference reference_id: '1234', reference_table: 'posts' reference_id: '4321', reference_table: 'comments' You can still benefit from indexes for joins in the same way that you would with actual foreign key constraints, but the downside with this is that you can't actually apply a foreign key constraint. Another solution you can look into, which I don't have much experience with myself, is table inheritance: https://stackoverflow.com/questions/3074535/when-to-use-inherited-tables-in-postgresql https://stackoverflow.com/questions/3074535/when-to-use-inhe... Another form of modeling the data in such a manner is called Exclusive Arc, which is that you just simply put all of the possible keys you might reference on the table, and add a foreign key on all of them. Then, when you need to make a reference, you just leave all but the one in use on that row as null values. If a "like" can go on a "post" or a "comment" or an "image", you would just have all 3 foreign key columns/constraints on the "like" table. And lastly, and probably best of all, is to simply use 1-2 tables for your entire data model for vertices/edges, and just treat Postgres as a graph database.

porker 8y ago
> One solution is to have a generic reference property paired with another indicating which table to reference > You can still benefit from indexes for joins in the same way that you would with actual foreign key constraints, but the downside with this is that you can't actually apply a foreign key constraint. Thanks, I had not thought JOINs possible on this - how would a query look? I'm guessing it works only for one type of reference_table. > Another solution you can look into, which I don't have much experience with myself, is table inheritance: https://stackoverflow.com/questions/3074535/when-to-use-inhe.. https://stackoverflow.com/questions/3074535/when-to-use-inhe.... Been there, tried it, banged my head on most of these (particularly the FK limitation): https://www.postgresql.org/docs/11/ddl-inherit.html https://www.postgresql.org/docs/11/ddl-inherit.html :) If it was fixed by Postgres it'd be awesome. I've lost the wiki page which was around on the topic, but it was unchanged for ~10 years, so really should've been marked 'wontfix'. > Another form of modeling the data in such a manner is called Exclusive Arc, > And lastly, and probably best of all, is to simply use 1-2 tables for your entire data model for vertices/edges, and just treat Postgres as a graph database. That's what I'm doing, with the 'node' table using JSON to store the node-specific data. I feel sooooooo dirty but it works. Yet to figure out schema evolution of the JSON in a controlled manner :)
- ralusek 8y ago
  > Thanks, I had not thought JOINs possible on this - how would a query look? So this is for the structure with the two columns reference_id: '1234', reference_table: 'posts' reference_id: '4321', reference_table: 'comments' So let's say that this is the `likes` table that can like either `comments` or `posts`, here are some queries: // Get comments and their respective likes SELECT * from comments JOIN likes ON comments.id = likes.reference_id; // Get posts and their respective likes SELECT * from posts JOIN likes ON posts.id = likes.reference_id; // Get likes and their referenced comments SELECT * from likes JOIN comments ON comments.id = likes.reference_id; And so on.