PostgreSQL effective way to store a list of IDs

Question

In my PostgreSQL I have two tables board and cards tables with OneToMany relationship between them(one board can have a multiple cards).

User can hold a few cards on the board. In order to implement this functionality typically I would created another table called for example cards_on_hold with OneToMany relationship and placed cards on hold IDs into this table. In order to fetch this data for board I'd use JOIN between board and cards_on_hold.

Is there any more effective way in PostgreSQL to store cards on hold IDs ? Maybe for example some feature to store this list inline in board table ? I'll need to use this IDs list later in IN SQL clause in order to filter card set.

Answer 1

Postgres does support arrays of integers (assuming your ids are integers):

http://www.postgresql.org/docs/9.1/static/arrays.html

However manipulating that data is a bit hard compared to a separate table. For example with a separate table you can put a uniqueness guarantee so that you won't have duplicates of ids (assuming you'd want that). To achieve the same thing with an array you would have to create a stored procedure to detect duplicates (on insert for example). That would be hard (if possible at all) to be as efficient as simple unique constraint. Not to mention that you lose consistency guarantee because you can't put foreign key constraint on such array.

So in general conisistency would be an issue with inline list. At the same time I doubt you would get any noticable performance gain. After all arrays should not be used as an "aggregated foreign key" IMHO.

All in all: I suggest you stick to a separate table.