Medical DB 101

[dbnewby]

I'm currently developing a medical DB and have questions as to how each individual patient encounter are stored. There are several components to an encounter: complaint, history of illness, past medical history, past surgical history, medications, allergies, social history, family history, review of symptoms/systems, physical exam, assessment/diagnosis, and plan. Could anyone offer a rookie developer some idea of how to approach this? Is each element of the encounter simply a column in the table with the date corresponding to a row in the table or should this be approached differently? Thanks.

[Hack]

Welcome to DevX :WAVE:

I would have a patient table listing all pertinent information, including codes indicating medical history. The codes would come from other tables.

I would have an illness table that does nothing more than lists illness and and illness Id (the patient table would also have an illness Id field)

I would have a surgery table that lists all surgerys and a surgery Id for each. (the patient table would also have surgery Id field)

I would have an allergies table that lists all allergys and an allergy Id field (the patient table would also have an allegery Id field)

Etc, etc, etc

That is more or less a general approach to a normalized database design.

[dbnewby]

Thanks Hack. I understand the concept of normalization but how would you store larger amounts of information (physical exam)? Would this simply exist in a cell with a varchar data type?

[Hack]

Define "physical exam"

[dbnewby]

GENERAL: A pleasant, obese, female who is in no distress
VITAL SIGNS: Blood pressure 122/65. Pulse 76. Temperature 98.1 degrees.
HEENT: The sclerae are nonicteric. Head is normocephalic/atraumatic. Oropharynx and otic canals are clear.
NECK: Supple. Full range of motion. No adenopathy.
CHEST: Clear.
CVS: With distant tones, regular rate and rhythm.
ABDOMEN:On plane, obese, soft, good bowel sounds. There is no guarding or rebound.
EXTREMITIES: No edema
NEURLOGICAL: Alert and oriented to person, place, and time. Motor function normal


All elements of the encounter would populate the database a number of ways (free text entry, voice recognition, templates) but I presume that's a function of the front-end.

Furthermore, when the patient presented again, all elements of the previous note should appear in an editible form (but with a different date) so that the current note could quickly be changed (and no need to re-enter all the old data).

[Hack]

Ok, it looks like a simple physical exam table which links back to the patient table by patient Id.

As you have already mentioned, most, if not all, would be free standing text.

(Retrieving the record would, in fact, be a function of the front end as well as actually entering the information and saving the information. Discussing the front end would be a subject for a different thread. For the time being, we are just concentrating on designing the database itself.)

[dbnewby]

So the physical exam would be a different table? (Would be nice to see a schema).

[Hack]

Yes...it would be a separate table. Rule number 1 of normalization is to never repeat the same information in multiple tables. (Various primary key ids notwithstanding of course).

Based on what we have discussed thus far, you should be able to come up with a db diagram with the various Id fields being the primary keys in each table.

[dbnewby]

Thanks Hack, but at least to me this is still nebulous without seeing an example. I'm sure there are several ways to do this.

[Hack]

There are several ways to do it.

There will always be several ways to do it.

It becomes a matter of picking one and seeing how it works.

I just reread your initial post. You didn't mention which database you will be using for this.

[dbnewby]

SQL Server 2005

[Hack]

Well, then getting a schema diagram will be pretty easy.

Create the tables (you can always delete, or redo them) and use one of the diagram tools to layout afterwards.

What are you going to use for the front end, VB.NET?

[dbnewby]

Sounds like this note table or encounter table will contain no data but rather serve as a reference table to other tables???

Still have not decided about the front end.

I've made probably 30 tables thus far.

[Hack]

Sounds like this note table or encounter table will contain no data but rather serve as a reference table to other tables???
There is no point in having a table that contains no data. What is the "note" or "encounter" table that you are referring to?

[dbnewby]

This is the table that would house all the elements in my first post. What I mean is it seems like each part of this encounter table links to other tables (diagnosis links to separate diagnosis table, physical exam links to separate physical exam table, etc).

[Hack]

For a medical database, I would think that all other tables would be linking directly to a patient table as that is the primary thing against which we are accumulating data.