From: Alain Reymond <alain.reymond@ceia.com>
To: Andrei Bintintan <klodoma@ar-sd.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Database structure
Date: Tue, 04 May 2004 17:45:44 +0200
Message-ID: <4097D6C8.31518.1705566@localhost> (raw)
In-Reply-To: <00a401c431e5$51dc7250$0b00a8c0@forge>
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
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, klodoma@ar-sd.net
Subject: Re: Database structure
In-Reply-To: <4097D6C8.31518.1705566@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