Обсуждение: Arrays instead of join tables

I've never worked with a database with arrays, so I'm curious what the
advantages and disadvantages of using it are.  For example:

-- METHOD 1: The "usual" way --

Items table:
   item_id int,
   item_data1 ...,
   item_data2 ...
   Primary Key = item_id

ItemSet table: <-- Join table
   item_id int,
   set_id int
   Primary Key = (item_id,set_id)
   Foreign Key set_id --> Sets(set_id)
   Foreign Key item_id --> Items(item_id)

Sets table:
   set_id int,
   set_data1 ...,
   set_data2 ...
   Primary Key = set_id

ItemSet is the table joining Items to Sets in a one-to-many
relationship.  The above is how I would typically set that up in a dbms.
  But with postgres, I could do this:

-- METHOD 2: Using arrays --

Items table:
   item_id int,
   set_ids int[],  <-- Hey, neato!
   item_data1 ...,
   item_data2 ...,
   Primary Key = item_id

This way I don't even need an ItemSet join table.

+ Efficiency: To return the set_ids for an Item, I could return an array
back to my C# code instead of a bunch of rows with integers.  That's
probably faster, right?

- Can't store any additional join info in the ItemSet table, but that's
okay for my application.

? Can I write a constraint to ensure that set_ids has at least one element?

Is this better or worse?  Can I enforce referential integrity on the
elements of the set_ids array?  Is it more or less efficient?  What else
have I missed?

William Garrison wrote:
> I've never worked with a database with arrays, so I'm curious...
>
> + Efficiency: To return the set_ids for an Item, I could return an array
> back to my C# code instead of a bunch of rows with integers.  That's
> probably faster, right?

You should look in to the contrib modules intagg and intarray.

In particular contrib/intagg/README.int_aggregate discusses an example
quite similar to what you're proposing.

Short summary: Seems like a win for very static data, a loss for very dynamic data.

William Garrison wrote:
> I've never worked with a database with arrays, so I'm curious what the
> advantages and disadvantages of using it are.  For example:

I am prejudiced against arrays because they violate the relational model. I do
not see an advantage over a related table.

Arrays seem to me to re-invent the network database model, which lost out to
the relational model decades ago.

-- Lew