From: Alain Reymond <alain.reymond@ceia.com>
To: pgsql-sql@postgresql.org
Subject: Database structure
Date: Fri, 30 Apr 2004 17:06:03 +0200
Message-ID: <4092877B.2470.1E894F5@localhost> (raw)
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).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: alain.reymond@ceia.com
Subject: Re: Database structure
In-Reply-To: <4092877B.2470.1E894F5@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox