I used to program for a medical tester. I used method 2: 2 - a table with 60 rows for one assessment : results(#assessment_nr, labtest_nr, p, d) where p and d are my two results.
The BIG advantage was changes to the tests, adding new ones, or deleting fields. Made it much more flexible. > 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). > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
