Medical Database Design for Research

[jzwp11]

Basically I would need a separate tables for each type of consolidated problem.

Separate tables for each problem would not be the proper way to structure it. Trying to query any data would require separate queries for each and thus separate forms, reports etc. That would be an extremely messy situation that you want to avoid at all costs.

If a problem or ICD9 is related to another problem or ICD9 code, then you need a structure as follows (I'll just use the ICD9 codes as an example).

tblCodes
-pkCodeID primary key, autonumber
-ICD9CodeNumber
-txtDescription


tblRelatedCodes
-pkRelateCodeID primary key, autonumber
-fkPCodeID foreign key to tblCodes (represents the primary code)
-fkSCodeID foreign key to tblCodes (represents the code related to the primary code i.e. the secondary code)



Taking the 250.0 code example,


tblCodes
pkCodeID|ICD9CodeNumber
1|250.0
2|250.02
3|250.60


tblRelatedCodes
pkRelateCodeID|fkPCodeID|fkSCodeID
1|1|2
2|1|3

[orange]

Designing a data base is much like designing anything -- you can design if without ever building it.
You might wish to start considering the questions you want answered. These questions will involve Things, combination of things, subsets of things related to other things and grouped in various ways.
The "Things" mean Entities and Tables; combinations means related things...

You can go through the data and analyze it without having "the ultimate data base design".

Have you looked at what is contained in "the other half of the data"?

I don't think you have too many constraints on your design at this point. But you have to have a good knowledge of the questions you want answered.

[JJ22]

Separate tables for each problem would not be the proper way to structure it. Trying to query any data would require separate queries for each and thus separate forms, reports etc. That would be an extremely messy situation that you want to avoid at all costs.

If a problem or ICD9 is related to another problem or ICD9 code, then you need a structure as follows (I'll just use the ICD9 codes as an example).

tblCodes
-pkCodeID primary key, autonumber
-ICD9CodeNumber
-txtDescription


tblRelatedCodes
-pkRelateCodeID primary key, autonumber
-fkPCodeID foreign key to tblCodes (represents the primary code)
-fkSCodeID foreign key to tblCodes (represents the code related to the primary code i.e. the secondary code)

Taking the 250.0 code example,


tblCodes
pkCodeID|ICD9CodeNumber
1|250.0
2|250.02
3|250.60


tblRelatedCodes
pkRelateCodeID|fkPCodeID|fkSCodeID
1|1|2
2|1|3

I think I understand what you are trying to say. I spent the last week reading and understanding both excel and access. If I want each patient to have a consolidated problem (if patient has 10+ specific and unique type/status of diabetes II ICD9 codes, the are just lumped just as a diabetes II problem) why wouldn't the structure be:

tblPatient
-pkPatiendID

tblPatientProblem
-pkpatientproblemID
-fkProblemID (Diabetes = 1)

tblProblems
-pkproblemID (ie Diabetes type II would have codes 250.0, 250.02, 250.60)
-Probdescript (diabetes II, hypothyroid, etc)

tblProblemCodes
-pkProblemCode
-fkproblemID (to link the codes to one problem)
-fkCodeID

tblCodes
-pkCodeID
-Codedescript


I don't understand where the relatedcodes table is coming into play here or where that implementation goes.

Direct copy and paste of my problem list, de identified. You can see how patient 1 has three codes, all for what I see in the end as Diabetes, type II

ID	DOB	Problem DX Name	ICD9No	Problem Status	Prob Noted Dt	Prob Resolved Dt
1	4/17/1951	TYPE 2 DIABETES, HBA1C GOAL < 8%	250.00	Active	7/31/2011
1	4/17/1951	DIABETIC RETINOPATHY, type II	250.5	Active	9/7/2011
1	4/17/1951	Diabetic Neuropathy, type II	250.6	Active	9/7/2011
2	12/28/1958	UNCONTROLLED TYPE 1 DIABETES WITH RETINOPATHY	250.03	Active	2/28/2012
2	12/28/1958	DM NEURO MANIF TYPE I	250.61	Active	2/28/2012
2	12/28/1958	CONTROLLED TYPE 1 DIABETES WITH RETINOPATHY, CONTROLLED	250.01	Active	4/28/2012

[jzwp11]

I don't understand where the relatedcodes table is coming into play here or where that implementation goes.

If a code is related to another code just as you described earlier then you would need the tblRelatedCodes. Now what you propose is to have a table of problems and related to those problems you would have the associated codes. I was interpreting codes and problems as 1 entity (the code identified the problem); you have separated them into 2. How you have it structured should work fine.

[JJ22]

If a code is related to another code just as you described earlier then you would need the tblRelatedCodes. Now what you propose is to have a table of problems and related to those problems you would have the associated codes. I was interpreting codes and problems as 1 entity (the code identified the problem); you have separated them into 2. How you have it structured should work fine.

Thank you, and that makes sense based on what I have been learning so far.

I am sorry, I was vague. I have thousands of codes that I want to consolidate into a 15-20 problems. ICD9 codes can be under the same general category but they branch inorder to record more detail. Thus I want to lump them back together for my research as a consolidated problem. I don't have the number of subjects to have individual codes in addition to the variance on how the codes were actually assigned.

[jzwp11]

Thanks for the explanation. It sounds like you are moving along well on the table structure.