Arrays instead of join tables
От | William Garrison |
---|---|
Тема | Arrays instead of join tables |
Дата | |
Msg-id | 460E65B4.8050705@mobydisk.com обсуждение исходный текст |
Ответы |
Re: Arrays instead of join tables
Re: Arrays instead of join tables |
Список | 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?
В списке pgsql-general по дате отправления: