Nested data

[guichemot]

I am pretty new to databases and design, so this may be obvious to some.

I am designing a medical database that requires nested data. The simple, straightforward data for each patient would be date of birth, height, weight, etc. The more complicated data would be stuff like:



-what medications are they taking?
-what dosage of each (500ml a day, 1000ml a day, etc.)?
-what are their medical and psychological diagnoses?

I could just have a text field called "diagnoses" and just list them, but then it would be difficult for me to sort them into all patients with "diagnosis X," or all patients with both "diagnosis X" and taking "medication X" as well. The best analogy I can think of for what I need is a "tagging" system for my patients, where I can tag certain patients with certain illnesses, medications, and other kinds of data and then group them by those tags.

How can I design a database that allows for this kind of data?

[ssanfu]

The first thing you should do is read about "Normalization". Read it several times. And when you think you understand it, read it again. Not trying to be flippant, but you need to have a good structure or you will be re-designing - maybe several times. I still read chapters on normalization.

Then search the forum for book recommendations. I have around 7 books I use as references.

Here is one place to start.... Crystal's site:

http://www.accessmvp.com/Strive4Peace/

(also check out http://mvps.org/access/tencommandments.htm )

-what medications are they taking?
-what dosage of each (500ml a day, 1000ml a day, etc.)?
-what are their medical and psychological diagnoses?

To start with, besides the table for Patient data, you would need a table for Medications, Dosages, and Diagnoses (included medical and psychological).

They would be related (IMO)

one ------------->  many
patient             Diagnoses  (one patient to many Diagnoses  )
patient             medications (one patient to many medications)
medications         Dosages  (one medication to many Dosages)

I don't know the rest of your requirements, so this is just a start.

[guichemot]

This is helpful.

I'm going to read all the Access Basics by Crystal stuff to get a better handle on this. But I imagine that many people have designed databases like this before; I'm surprised I haven't been able to find some simple solutions in my searching. Let's tackle a specific issue for this database: medication. There is a one to many relationship to patients and which medications they are taking. I could just take this approach:

Medication Yes/No

Medication1 Y
Medication2 N
Medication3 Y

This would make it easy for me to just avoid having to deal with multiple tables and just have a bunch of "medication" fields and a lot of "yes/no" data for what each patient takes. The problem with this would be that for each patient I would have to enter "no" a million times for all the medications that they don't take. I imagine there's a way to design a form to make that kind of data entry less of a pain. Do you see any other problems with this approach, though?

[ssanfu]

---<snip>---

Medication Yes/No

Medication1 Y
Medication2 N
Medication3 Y

This would make it easy for me to just avoid having to deal with multiple tables and just have a bunch of "medication" fields and a lot of "yes/no" data for what each patient takes.
---<snip>---

I think this idea is a very bad if you are talking about adding a field for each medication to the patient table. How many medication fields will you have to add to the patient table?? After you get all of the queries forms & reports done, what happens when a patient has to take a medication that is not in the table??

Remember that a table has a lifetime limit of 255 fields. This means if you create 20 fields, delete 10 fields, then add 30 more fields, you have used up 50 fields (20+30).

For each medication, don't you also have to track the dates they were prescribed? Do you need to have a history of which medications & dosages a patient has taken?


That is the purpose of "...deal with multiple tables...". When you have to add a new medication, it is just another row in the table, not a new field. No redesign of the queries, forms or reports. And the dosage table makes it easy to track medication/dosage.

I would recommend really working on your table structure. Otherwise you will have major headaches and be redesigning several times.

[guichemot]

Yea, I'm working through the crystal documents to try and get a grip on this stuff. I just don't feel like I know enough to tackle the structure of this kind of database. Hopefully I will feel more confident by the time I've gotten through it all. I don't know how people come to learn all this stuff...

[jzwp11]

I have to agree with ssanfu regarding the table structure and how important it is to set it up correctly at the beginning. In terms of learning this stuff, it does take time. Some of it comes down to analyzing your data and being able to recognize the relationships in the data you have. I have learned a lot just reading various posts on this forum as well as others geared around Access. Here is just one recent thread in which I was involved. It might be worth a look to see the thought progression and how to uncover the relationships within the data and how that analysis is transcribed into tables and relationships. Caution: the thread is quite lengthy