Question concerning arrays

"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 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 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