Re: Array with Subselect / ANY - cast?

On Tue, 21 Aug 2007 14:19:03 -0500
Michael Glaesemann <grzm(at)seespotcode(dot)net> wrote:

> Out of curiosity, what led to the schema design of storing these
> pb_id values in an array rather than in a many-to-many table?
> You're working against the database server here. The usual way to
> define this relationship would be

<snip>

Yeah - I'm a big proponent of doing things the relational way but for
this application it seemed like the right thing (after much
debating) to do at the time and it's been working well so far. The
app is kind of an object builder that lets any joe schmoe create an
object like a student, so you can create a field called fname, lname,
etc. and also fields that contain lists of values (phone numbers,
majors, etc) - it's a simple example but the number of list-type
fields is anticipated to be VERY high so I decided to deal with
arrays instead of creating all sorts of normalized tables.
Was it the right thing to do? I don't know yet but it's working so
far. It gets interesting when you set a field that links to another
object - say you have a class object and a student object and you
want to say student has many classes - the array in this case store
the class id pks but it's not a foreign key as arrays don't do that
so I had to put some triggers (as you mentioned) to keep things in
line (if a class is deleted for example). These are really poor
examples because anyone who does an app for students/classes creates
normalized tables (at least for oltp) but the app we're building gives
the end user the flexibility to create whatever they can think of.

The meta data that this app uses to store information about the
objects the user is building is all stored in normalized tables, but
the data for the objects themselves I wanted to keep as wide as
possible.

So yeah, arrays are a PITA but I think for this it'll work, time will
tell...

Josh