From: "Alain Reymond" <alain.reymond@ceia.com>
To: "Andrei Bintintan" <klodoma@ar-sd.net>
Date: Tue, 04 May 2004 17:45:44 +0200
MIME-Version: 1.0
Subject: Re: Database structure
Cc: pgsql-sql@postgresql.org
Message-ID: <4097D6C8.31518.1705566@localhost>
In-reply-to: <00a401c431e5$51dc7250$0b00a8c0@forge>
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body

I thank you for your answer. 

The more I think about it, the more I find the second option better. Just one precision. 
All tests are always done, so I always hae all columns filled with a result.

My only trouble was about size and performance. I store only a few byte with a lot of 
overhead (#assessment_nr, labtest_nr) for only one integer and one real per row. And I 
can have up to 1.500.000 rows per year with at least 10 years on line... It means big 
indexes.

Regards.

Alain

> I would go for the second one. I think the size of the table is not a
> problem. You will have just to write the right indexes for easy joins.
> 
> OBS: " For one assessment, I'll store 60 rows with only two useful
> integers in it" ... why? Better make a "lab_test" table where you have
> the tab tests and you write in the results(#assessment_nr, labtest_nr,
> p, d) only those datas that you have. For example if you have the
> assesment no. 3000 and you have only the results for lab_test 10->40
> then why to write in the DB also the lab_test from 40->70(if you don't
> have it)??? (if I didn't understand this clear, sorry for the
> observation).
> 
> 
> The second option is better if you change one time the lab_test
> list(have to think also this option --- if making the database for at
> least 10 years). Because in the first solution you will have to add
> always a new column... and that is not the "best" option. In the
> second way you just add a new ID in the lab_test list and finish. No
> problems.
> 
> If you go for the first option and you have to change something in the
> result table... it won't be easy.
> 
> The alter table is not so tragical as it seems... use
> constrains...don't ever erase from DB.
> 
> So... my final answer: the second option.
Alain Reymond
CEIA
Bd Saint-Michel 119
1040 Bruxelles
Tel: +32 2 736 04 58
Fax: +32 2 736 58 02
alain.reymond@ceia.com
PGP key sur http://pgpkeys.mit.edu:11371