Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
5  
A list of foreign keys should not be stored in an array. You should create a 1:n relationship table. –  a_horse_with_no_name Jan 29 '12 at 5:52
    
Why do you want to store the IDs in an array rather than another table? That would "maximise speed and follow convention". Yes postgres has arrays but they are not intended to replace normal relationships - and they will be very slow and cause you much pain if you try and use them to do so. –  Jack Douglas Jan 29 '12 at 8:30
    
If you have any insight I'm willing to listen. I intend to store an array of user IDs associated with owning an account. Does that need a new table? thanks –  crockpotveggies Jan 29 '12 at 14:39

2 Answers 2

up vote 6 down vote accepted

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
share|improve this answer
    
Overall this is a much more elegant solution, and I appreciate that you were able to share it. –  crockpotveggies Jan 30 '12 at 7:50

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.