From: "Alain Reymond" <alain.reymond@ceia.com>
To: pgsql-sql@postgresql.org
Date: Fri, 30 Apr 2004 17:06:03 +0200
MIME-Version: 1.0
Subject: Database structure
Message-ID: <4092877B.2470.1E894F5@localhost>
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body

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

(I hope that it is clear enough with my bad English).