I am attempting to create a DB for my app and one thing I'd like to find the best way of doing is creating a one-to-many relationship between my Users
and Items
tables.
I know I can make a third table, ReviewedItems
, and have the columns be a User
id and an Item
id, but I'd like to know if it's possible to make a column in Users
, let's say reviewedItems
, which is an integer array containing foreign keys to Items
that the User
has reviewed.
If PostgreSQL can do this, please let me know! If not, I'll just go down my third table route.
Best Answer
It may soon be possible to do this: https://commitfest.postgresql.org/17/1252/ - Mark Rofail has been doing some excellent work on this patch!
The patch will (once complete) allow
However, author currently needs help to rebase the patch (beyond my own ability) so anyone reading this who knows Postgres internals please help if you can.