Jodi,
In my experience:
It is better to normalize these arrays into separate tables - even if it isn't "nice" to do so :-p
Array types can't be indexed, can't be foreign keys, and aren't compatible with other systems.
Normalization is always the most efficient way of handling multiple values under one category for a table.
Sometimes you may file the need to hit the C and write a procedure or two that can handle creation, deletion and retrieval from these quickly.
A lot of the time though, this isn't necessary.
Regards
Gareth Kirwan
Thermeon Europe Ltd
Can anyone offer some insight or link to documentation on the use of integer and/or string array data types? We are thinking of using them in our database but there is some concern about breaking normalization standards and opening ourselves up to more issues later.
We are storing data about various analyses that are being run - i.e user and system parameter values. It would be nice not to have to create a separate table for each of these fields.
Any thoughts would be greatly appreciated.
Thank you,
Jodi Kanter
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu