470,614 Members | 1,425 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,614 developers. It's quick & easy.

Database structure

Hello,

I would like an advise on the following problem :

I have a table of patients.
Each patient can make different biological assessments.
Each assessment is always decomposed into different laboratory tests.
A laboratory test is made of a test number and two values coming from
analysers.

The schema is :
Patients(#patient_nr,name,etc...)
Assessment(#assessment_nr, #patient_nr, assessment_type, other usefull
values). Assessment_types(assessment_type, labtest_nr) An assessment
is composed of different tests, let's say assessment type 1 is
composed of lab test 1,2,3,5 and assessment type 2 of lab test number
10 to 70.

I have an assessment with 60 different lab tests (always the same). I
have two ways for storing the values :

1 - a table with 120 columns for the two values.
results(#assessment_nr, p10,d10, p11,d11, .....,p70,d70).
where 10 to 70 represents the lab test number.

2 - a table with 60 rows for one assessment :
results(#assessment_nr, labtest_nr, p, d) where p and d are my two
results.

Here comes my question. Which of the two would you choose?

The firsrt solution has the advantage of returning one single row for
one complete assessment. If I have to make statistics, it is easy.
But, if I have to modify the composition of an assessment (which
occurs very rarely), I shall have to use an alter table instruction.
As I have 4 different assessment types, I have to create five
different tables, one per assessment type.

The second solution is normalized and more elegant. But I am
preoccupied by the size of the table. For one assessment, I'll store
60 rows with only two useful integers in it. And you must add the size
of the index. With 25.000 assessments a year, it makes 1.500.000 rows
with only 4 columns amoung them 2 only for the results and 2 for
identification. I would like to store 10 years online, so 15.000.000
rows. What about the size of index ?

Any advise ? I thank you in advance.
Alain Reymond

Jul 19 '05 #1
0 1228

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by bryja_klaudiusz[at]poczta[dot]fm | last post: by
1 post views Thread by Srihari | last post: by
346 posts views Thread by rkusenet | last post: by
25 posts views Thread by Colin McKinnon | last post: by
3 posts views Thread by josh.kuo | last post: by
8 posts views Thread by situ | last post: by
9 posts views Thread by Peter Duniho | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.