views:

157

answers:

1

I seem to have two options on how to implement arrays, and I want to know which I should go with:

  • Use the ARRAY data type and (from what I understand) effectively serialize data objects into the database (which in my case are just wrapped primitive types; don't know of another way to make this work).

  • Use a separate table and map with foreign keys for each array item.

If you have experience with this (especially with H2), which would you recommend?

+2  A: 

Array fields are often a no-no in databases, it often goes against the normalization principles. Depending on the purpose of the data, you need to store them in either a separate table with fk relations to the parent table, or in an independent table (better when a single item may occur more than once, i.e. a n-m relationship instead of n-0) and use a fk-fk join table for the relations between them and the parent table.

In SQL/JDBC, you can however often SELECT them as an array, depending on whether the DB supports the SQL construct/function or not. In PostgreSQL for example you can use the ARRAY() function for this which you can obtain in Java/JDBC using ResultSet#getArray().

BalusC
Yeah, I guess I don't want to start recoding database functions, so I'll just follow your normalization advice and map it to a table. I don't even know if handling arrays with my own code actually improves anything, anyway.
Daddy Warbox
H2 actually does support Arrays. I would also avoid using it if possible, but just to let you know.
Thomas Mueller
@Thomas: yes, almost all self-respected DB's support the SQL ARRAY type. However, in real world this goes in almost any use case against the normalization principles.
BalusC
@BalusC: you are right of course.
Thomas Mueller