Arrays are -- by definition -- not atomic, and so they fundamentally break the model that relational databases are founded upon. If you want to be a good database designer, don't use arrays.
Thanks. I was reading about Codd after your last email, but couldn't guess at which point was objectionable. I'll have to keep that in mind and always question when I come across an array or if I am ever tempted to use one.
While I can make more minor modifications to schema and suggest best practices, I am not in the position to set or enforce policy within my organization. The table and field in question are absolutely a mess and this field in particular is actually an array of IDs of other records on the same table (that actually represent another object that is similar but not the same as the one containing the array of IDs).
So, I am just looking to make the best of a bad situation currently and understand how best to use the various tools available to me, with little impact on the existing codebase but still getting performance benefits from proper indexing. Thanks for sharing the additional context.
