Hi everyone!!

I am really pleased to have found this forum, as I wasn't sure where to get help!!

I am learning about, and building, fairly simple databases for medical research but having some trouble. First of all, I am using openoffice 2.4 (with the default database engine) on a ubuntu (hardy) platform. I have successfully created a single table database (for contacts) and created tables and forms, but I am having trouble setting up the relationships between my tables.

I have looked at the OO help files and examples (and other forum entries) but I can't get through the jargon (complete novice when it comes to databases).

I have three tables:

1. Clinical Info (includes: ID, name, sex, length of symptoms etc)
2. Initial clinical exam (includes: weakness of specific muscles eg r elbow flexion, r elbow extension etc)
3. Neurophysiological data (includes nerve conduction study results, eg r median n wrist CMAP, l median n wrist CMAP)

I want each patient to be given an ID, then have that as the common field between tables, in other words when I enter data into my clinical features table I want the ID to be entered into the other two tables ready for me to enter the data that corresponds with that patient.

I have put an ID field in all three tables (with auto increment in the clinical features) and defined the relationships between the ID fields in all the other tables, with update and delete cascades enabled, but when I enter a record in one table, the there is no entry in the other tables.

What am I doing wrong?

I'd be really grateful for any assistance, I'm keen to get my research progressing asap.

Thanks in advance!!

Why do you want to create records in other tables? You know that patients should register before having any treatment, so it is possible to have a patient with no clinical information stored against them.

Create each record as you need it

Thanks for your reply.

I'm not sure I entirely understand what you've suggested. In order to have relationships between the tables, don't you need to have a common field? I'm obviously misunderstanding some basic 'rules' of database design. I need to be sure that the 'clinical' information on a patient is attached to the 'test results' on the same patient, I thought that having each patient defined by ID in each table would ensure this - am I wrong? Is there a better approach?

It dawned on me the other day. Why do I need more than one table? I have now combined my three tables into one and my problems have (for the moment) been resolved - but . . . I still can't work out why my design (or oobase) wasn't working. The cascade function doesn't seem to work for me at all. Surely if a field is shared between two tables then if you update one, the other should be updated??

Woah, woah, woah; back up a minute.

By combining it all in to one table you are stating that one patient can only have one of anything!

I misunderstood your original problem ; I assumed you were thinking that you needed to create the related record as soon as the parent was created, which is not the case.

Cascade applies to key fields only, for example, if you were to change patient #1's id to 150, all related records need to update this value also otherwise we are left with orphans.

I think you need to describe what your entities are a little better and how they relate; for example one patient can have many tests.

Moving this question to database concepts and design.

Woah, woah, woah, woah woah.

Not only are you violating the principles of Normalization by combining all your data into one table, but your development process is ass backwards as well:
I know you want to create lots of pretty forms right away, because thats a lot of fun dragging and dropping boxes and picking out fonts and such.
But resist the urge.
FIRST, define all the types of data that you intend to track in your database.
THEN, create all of your tables and relationships.
FINALLY, create the forms and reports that will access your database.

If you create your forms first, then your application will exist to server the forms rather than serve the need of the business. That will make it difficult to adapt your database to new business requirements in the future. Also, you will find yourself have to do a LOT of rework on your forms as you realize bits and pieces that are missing from your database design.

Thanks for the replies . . . I'm glad I've stirred up some interest, and hopefully (with your help) I'll learn where I'm going wrong.

OK, some more info about what I'm trying to do and why . . .

I am building a single purpose database - ie this is for one particular project with fairly specific limits to what data I have. I am not trying to run before I can walk - I am hoping that I will learn enough to build more complicated databases for other projects, but his one is going to be simple and I don't anticipate needing to rework, or expand this database in its current form.

Understanding my data . . .

I have collected 30-50 patients with a particular condition - let's call it disease 'X'. All of the patients were given a treatment (let's call it treatment Y) and some responded to the treatment, some didn't. What I am trying to do is identify any characteristics that the patients who did respond had, that the patients who did not respond did not. The 'characteristics' include things like:

Basic demographics:
1. Age
2. Sex
3. Duration of symptoms
4. Types of symptoms
5. Their primary doctor
6. Their secondary doctor (useful for chasing up missing results)

Detailed clinical information:
eg 1. Finger flexion weakness (Boolean YES/NO)
2. Finger extension weakness (Boolean YES/NO)

Nerve conduction study results (mostly numerical, but some boolean YES/NO)
eg 1. Right median nerve CMAP at the wrist - 6.5
2. Right median nerve CMAP at the elbow - 6.2
etc

I had originally planned to have three tables - one for basic info, another for detailed clinical information and a third for nerve conduction studies. I wanted each patient to have an entry in each table where I could put the various data about them. So ideally, I would want to enter the basic info into the first table and have an entry in the other tables ready for me to add more detailed information. I set up the three tables with an 'ID' field in each table as the primary key. I set up relationships between all three connecting the ID field, thinking that this would accomplish my goals - but I could not get things to work properly. For example, I would enter a patient's info into the 'basic info' table and they would be given an ID (eg 12), but there would not be an entry under '12' in the nerve conduction studies table for me to add further detail to.

I understand (I think) your reaction to having only one table, but in this study I only have the data at one time point - ie at the beginning, I do not have a second examination with different strength or nerve conduction studies results. So in this case, for example, each patient does only have 'one right median nerve cmap at the wrist'. If I were to have a 'before' and 'after' nerve conduction study result this might cause more problems. Most of what I am trying to do could be done in a spreadsheet, but what I need is the ability to runs queries with various combinations of characteristics (plus learning how to design databases is fun!!)

I hope this provides some background to my goals and thinking - please feel free to offer any suggestions etc

Thanks

It sounds to me like you should be storing this in Excel, (which has many more built-in statistical functions for data analysis) and running a multi-linear regression to determine correlations between characteristics and treatment response. But I can tell you 30-50 patients is WAY too few to get any statistically significant result. You need about 30 data points per characteristic.

try google