Search Postgresql Archives

Arrays instead of join tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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?



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux