An array of PostgreSQL elements, each of which is a foreign key

Question

I am trying to create a database for my application, and one thing I would like to find is the best way to do this is to create a one-to-many relationship between my Users and Items tables.

I know that I can make the third table ReviewedItems and have columns as the User identifier and id Item , but I would like to know if it is possible to make the column in Users , say ReviewedItems , which is an integer array containing the foreign keys to the Items that the User examined .

If PostgreSQL can do this, let me know! If not, I just go down the route of the third table.

Answer 1

No, It is Immpossible.

PostgreSQL is a relational DBMS that works most efficiently on properly normalized data models. Arrays - by definition, these are ordered sets, not relational data structures, and therefore the SQL standard does not support the definition of foreign keys for array elements, and also not PostgreSQL.

However, you can create an absolutely perfect database with array elements that bind to primary keys in other tables. However, these array elements cannot be declared foreign keys, therefore, the DBMS will not support referential integrity.