Thread: Question concerning arrays
Hi list, i am in the middle of breaking my head over designing a database and came to the following question/problem: i have personswhose values (integer) have to be entered in the db, but per person the amount of values ranges from 10 to around50. Now my question is if it makes sense, concerning performance, to store these integer values in arrays or if itis better to change the design so that the values are stored separately in fields in tables. I have to add, that it isnot unlikely (or at least it won't happen very often) that select queries will try to find one of those specific values,but rather something like select * from persons where person_id=... Thanks Chris -- Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230
"Christian Rengstl" <Christian.Rengstl@klinik.uni-regensburg.de> writes: > i am in the middle of breaking my head over designing a database and came to > the following question/problem: i have persons whose values (integer) have to > be entered in the db, but per person the amount of values ranges from 10 to > around 50. Now my question is if it makes sense, concerning performance, to > store these integer values in arrays or if it is better to change the design > so that the values are stored separately in fields in tables. I have to add, > that it is not unlikely (or at least it won't happen very often) that select > queries will try to find one of those specific values, but rather something > like select * from persons where person_id=... When I had a similar problem I decided on using arrays. But in my case I'm never going to search for individual values for every row -- after retrieving one row I may look for some specific value, though, but then I already have it. I'd also like to know what would be the best design for this when it is needed to search for individual values... In my case I had concentration and measured values in the form of key=value (conc=measured), both numeric and in variable quantities for each specific test (minimum of 3, maximum of ... I don't know :-)). -- Jorge Godoy <jgodoy@gmail.com>
On Tue, Jun 27, 2006 at 13:43:21 +0200, Christian Rengstl <Christian.Rengstl@klinik.uni-regensburg.de> wrote: > Hi list, > > i am in the middle of breaking my head over designing a database and came to the following question/problem: i have personswhose values (integer) have to be entered in the db, but per person the amount of values ranges from 10 to around50. Now my question is if it makes sense, concerning performance, to store these integer values in arrays or if itis better to change the design so that the values are stored separately in fields in tables. I have to add, that it isnot unlikely (or at least it won't happen very often) that select queries will try to find one of those specific values,but rather something like select * from persons where person_id=... If the data isn't actually a vector, than it is probably better to not use arrays. This will provide you with more flexibility.
On Tue, Jun 27, 2006 at 01:43:21PM +0200, Christian Rengstl wrote: > i am in the middle of breaking my head over designing a > database and came to the following question/problem: i have > persons whose values (integer) have to be entered in the db, > but per person the amount of values ranges from 10 to > around 50. Now my question is if it makes sense, concerning > performance, to store these integer values in arrays or if > it is better to change the design so that the values are > stored separately in fields in tables. I have to add, that > it is not unlikely (or at least it won't happen very often) > that select queries will try to find one of those specific > values, but rather something like select * from persons > where person_id=... If each value has a distinct medical meaning (think blood sugar readings) which makes sense on its own apart from all the other values it should very likely be stored as a separate value in the database. If, however, it is just one integer in a large batch of them (think raw values of EEG readings) which only really make sense in conjunction with each other and even then mainly to the application they should probably be stored as arrays or some other aggregate datatype. You might still want to query for single values out of the batch at times - detecting Ausreißer, fitting curves, detecting peaks of activity, etc. Karsten Assistenzarzt für Kinderheilkunde, Chirurgie und Allgemeinmedizin Leipzig -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346