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.
