Should arrays of IDs stored in Postgres use varchar, int, or another type?

Question

What is proper convention for creating a field that will hold an array of foreign keys? In this particular case, I'm creating a field called "owners" that will hold an array of user IDs. I want to make sure I choose the right type to maximize speed and follow convention.

Thanks.

Answer 1

The "one true way" is to create a table and use foreign keys:

CREATE TABLE owners(
    user_id int references users
  , account_id int references accounts
  , primary key(user_id, account_id)
);

That way you can verify membership using a single and simple query:

SELECT user_id, account_id FROM owners WHERE user_id = X AND account_id = Y

And you can get a list of owners for an account using:

SELECT user_id FROM owners WHERE account_id = X

Alternatively, you can find all accounts a user owns using the reverse query:

SELECT account_id FROM owners WHERE user_id = X

Answer 2

So apparently PostgreSQL has array functionality built in! This took some extra searching, but found an article on PGSQL documentation describing how arrays can be defined for certain field types.

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

Basically, the syntax looks like:

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

It goes as deep to allow strict definition of array size and depth. Very helpful.