sql - Storing one to many relation in database - Array or one to many relation? -

when store 1 many association in database, better approach. 1 - many mapping in table or storing many part array. i'm specific postgres database (constraint)

for example: if define relationship follows

a   b 1 - 2 1 - 3 1 - 6 2 - 3  2 - 4 3 - 5 3 - 6 

here, 1 part , many part b (primary key being a, b)

the same thing can stored array (similar adjacency list).

1 - {2,3,6} 2 - {3,4} 3 - {5,6} 

which of more efficient. may have operations on such transitive closure etc. and, graph may huge.

a practical example of above may connections of particular profile (linkedin connections), or social graph scenario

in example relationship many many, not 1 many. multiple a records can associated 1 b , multiple b records can associated 1 a. such, correct normalized form join table.

hypothetically, imagine db relationship represents 1 profile "liking" profile in social media context. in case may want store additional information; timestamp of when "like" initiated, degree profiled shruged/liked/loved other profile, etc. becomes apparent in array implementation there store additional data. need join table each "like" can have own metadata.

here structure recommend:

pk     b 100  1 - 2 200  1 - 3 300  1 - 6 400  2 - 3  500  2 - 4 600  3 - 5 700  3 - 6 

where pk auto generated pk, sequence, , a, b constrained unique index. structure future proof dropping unique index on a, b, headache i've had deal occasionally.