472,363 Members | 2,007 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,363 software developers and data experts.

Database structure


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

The schema is :
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

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

Jul 19 '05 #1
0 1271

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: bryja_klaudiusz[at]poczta[dot]fm | last post by:
Hi, How to copy database diagram (all data) to other server using only MSDE server and VS.Net Proffesional? I have no Enterprice manager. I have to move database (create on my machine) to my...
by: Srihari | last post by:
I'm trying to develop a tree structure using javascript. The node values of the tree are generating from a mysql table depending on login. The tree structure contains 3 sub levels. I developed...
by: rkusenet | last post by:
http://biz.yahoo.com/rc/040526/tech_database_marketshare_1.html Interesting to see that database sales for windows is more than Unix.
by: Colin McKinnon | last post by:
Hi all, There's lots of DB abstraction layers out there, but a quick look around them hasn't turned up anything which seems to met my requirements. Before I go off and write one I thought I'd...
by: josh.kuo | last post by:
Sorry about the subject, I can't think of a better one. I recently wrote some PHP classes that I think might be of interest to this group. Since I have been reaping the benefits of reading news...
by: Eric Sadoyama | last post by:
I have a database documentation question, but I am not even sure how to phrase it properly so I don't know where to start looking for answers. We are developing a database that is based on...
by: barkana | last post by:
Platform: ANSI C, RTOS Hi, I am trying to fread a database from a text file, and assign the database directly to the public structure below. In the text file first 20 chars is for SKU next 35 chars...
by: situ | last post by:
Hello all, i have Database1 and database2, is it possible to make database connection to database2 by running stored procedure on database1. Thanks and Regards Situ
by: Peter Duniho | last post by:
Is there a straightfoward API in .NET that allows for inspection of a database? That is, to look at the structure of the database, without knowing anything in advance about it? For example,...
by: rahulephp | last post by:
Hi there, can you please help me? i have data in array and i want to insert it in a single table. structure of table and array to be store is shown below: Please let me know how to store...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.