On Tue, 21 Aug 2007 14:19:03 -0500 Michael Glaesemann <grzm@xxxxxxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
