I am in the process of designing a database for medical research. I'm having trouble understanding the concept of relationships. I think I'm missing a key point, which makes the rest of my design process extremely difficult.

I suppose am making the usual beginner mistakes, thinking about the tables in terms of the data collection forms (paper forms) that I've already created. Essentially however, my study design is very simple. I will enrol the patient (collecting demographic information which should not change significantly throughout the course of the study), ask them a few questions (these will be repeated on a follow-up assessment as well), do a basic examination (again, this will be repeated on a follow-up assessment), perform a standardised test (again, repeated on a follow-up assessment), and having them complete a patient symptom questionnaire (again repeated on follow-up assessment). We plan to grade the severity of the test results on a scale of 1 to 5 for both the initial assessment and for the follow-up assessment. We will then subtract the initial assessment score from the follow-up assessment score to determine which patients had deteriorated over the time period. We will then analyse the data to assess whether any of the information we collected on the initial assessment could have predicted those patients who have deteriorated.

I've attempted to create an entity relationship diagram (which I have attached) which if nothing else, I hope will illustrate what my intentions are. I have attempted to read a number of books, websites, tutorials, but somehow I seem to be missing or have misinterpreted the basic concept of primary and foreign keys. Each time I try and create a multi-table, database, get stuck trying to work out what the relationships are and should be. Most of the examples I read about in a different context to the one that I'm trying to create, which makes it difficult.

While I don't expect anybody to design my database for me (you can, if you'd like, I'd be really grateful!!!, I was hoping someone could point me in the direction of some easy to understand reading material (preferably in relation to medical research database design) or a good-quality online course that could teach me this stuff. I'm pretty sure that the research that I am planning to do will not need high-powered databases, but I am the kind of person that wants to understand what I'm doing, and to get it right first time.

After 15 years of medical training I am not averse to formal teaching, I just want to know where find it.

I'd be grateful for any help.

Your attached erd is missing

This is the course I've been using. A bit pricey perhaps ($185) and it doesn't go into 4NF and 5NF which is odd considering the apparent prevailing opinion on these boards?

Sorry - realised after I posted. Here's my attachment - I will check out that link, thanks.

Not the prevailing opinion

pootle flump ur codings are working excelent.

It's true kristofer: The Yak Corral III

I suggest Database Design for Mere Mortals by Hernandez ($40 on Amazon).

One thing I like about his book is that it is completely RDBMS independent.

Another thing I like is that he is really focused on the entire design process, from interviews to schema to business rules.

He even makes a flowchart of his design process into an appendix.

Anyway, it was a very good introduction to databases for me.