From: | William Garrison <postgres(at)mobydisk(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Arrays instead of join tables |
Date: | 2007-03-31 13:44:20 |
Message-ID: | 460E65B4.8050705@mobydisk.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Jonathan Barbero | 2007-03-31 15:26:16 | Instalation problem |
Previous Message | andyk | 2007-03-31 12:34:26 | Re: Array extraction |