Research Study

[Desastor]

Hi,

I am a db newbie and am trying to design a db for a large study that we are trying to implement. I have read up on the materials and although I do understand most of the single concepts, I am utterly at a loss with some of the interactions while putting everything together in order to create a functioning database.

Here is the overarching design:
Patients (consisting of many families) each with a unique identifier return to the clinic for a variety of measures completed not necessarily on the same of their repeating appointments. Thus most, if not all patients, eventually will have many records in the db due to their repeating appointments.

I don't remember how many different which ways I have tried to make this work, but somehow I have never been able to. I had the variables split into up to three tables and I have tired one-to-one, one-to-many, and many-to-many relationships, the latter with a junction table. Nothing seems to work and I am obviously something not understanding. I am hopeful that someone in this forum more experienced than I am would be able to give me a hand and pointers:

Here are the variables:

The next 7 are currently contained in tbl_Demographic:

PatientID (this is a only truly unique identifier)
FirstName
LastName
Locator Code
ZipCode
Birthdate
StudyReleaseDate

The next 17 variables all represent medical and psychological measures. Initially, the first 5 variables were contained in tbl_Psychiatric

LstMD
MedExp
RN
RTC
AIMS

whereas the remaining 12 were contained in tbl_Psychological

LstMH
MH
TxPlan
SMI/A
SMI/C
SA
Consent
Dx1
Dx1t
Dx2
Dx2t
Last DI85

Because I could not get that to work, I since have stored all 17 variables into one tbl_ClinicalData.
All clinical data is unique but I would prefer to handle the design by breaking the clinical data into the aforementioned two tables.

Because I only had one truly unique variable for the PrimaryKey (i.e., PatientID), I relied on the Autonumber function in Access to create Foreign and additional Primary Keys when I dealt with two or more tables in the one-to-many or many-to-many design attempts. The Relationships alone gave me nightmares since I do not understand for example if I should be indexing or not, which obviously has significant meaning on the overall design of the db.

The more I was trying the more I was confusing myself I suppose. I am however at my wits end and really would appreciate if someone could help me in my confusion.



Thanks

D

[rzw0wr]

PatientID (this is a only truly unique identifier)
FirstName
LastName
Locator Code
ZipCode
Birthdate
StudyReleaseDate

This looks like a good start for 1 table. You may want to remove StudyReleaseDate from this table.
The rest of the fields I can't say because I don't know what they are.
I do see a few fields that look like they should be in another table.
Dx1
Dx1t
Dx2
Dx2t
These look like they may hold the same data.

Read a little on table normalization first off. If we can get your tables correct the database will be easier to work with later. Less design problems.

If you will define the fields we can the better advise you about designing you tables.

Dale

[Desastor]

I continued to work on the db after my post and actually removed the StudyReleaseDate from the tbl_Demographic, which is renamed to tbl_Patient and now left with these variables:

tbl_Patient
PatientID (unique identifier)
FirstName
LastName
Birthdate


I now thought about adding a Location Table, which would contain study sites different from the Zipcode above:

tbl_Location
LocatorCode
SiteName


I also thought about separating appointment types into a separate "date" table (explanations of variables in parenthesis):

tbl_Dates
LstMD (Last Psychiatric Appointment Date)
MedExp (Psychiatric Medication Expiration Date)
RN (Last Psychiatric Nurse Appointment Date)
RTC (Next Appointment to Return to Clinic Date)
AIMS (Date of Most Recent Adult Involuntary Movement Scale)
LstMH (Last Psychology Appointment Date)
TxPlan (Date of Most Recent Treatment Plan)
Consent (Date of Consent Form)
StudyReleaseDate (End of Study Date)
Last DI85 (Date of Study End Remuneration)


To further simply the date, another new table

tbl_Classification
MH (Psychological Classification Score, combination of numeric and alphanumeric: 1-5, a-d, e.g, 3D )
SMI/A (Seriously Mentally Ill via Assessment, Yes/No)
SMI/C (Seriously Mentally Ill in Community, Yes/No)
SA (Suicide Attempt History, Yes/No)

And lastly, a diagnostic table, although I am wondering if this could be further simplified by using a primary/secondary table or numeric/narrative table

tbl_Diagnostic
Dx1 (Primary DSM-IV Diagnostic Code, e.g, 296.90)
Dx1t (DSM-IV Diagnostic Description, Mood Disorder NOS)
Dx2 (Secondary DSM-IV Diagnostic Code, e.g., 296.90)
Dx2t (Secondary DSM-IV Diagnostic Description, Mood Disorder NOS)

[rzw0wr]

First off, It is starting to look better.
FYI, the proper name is field not variable. We use variables in code.

I would look at the tbl_Dianostic, it might be better split into 2 table.
1 For DX1 and a table for DX2.

In all tables add an autonumbered PK.
tbl_Patient you can still use your PatientID field however still add an aoutnumbered PK.

I am not sure if the tbl_Dates will cause problems later, however it looks OK to me.

After you get your tables made and the relationships, send us a picture.

Looks to me like you have a good start and are getting the idea.
Dale

[Desastor]

Dale,thanks for the feedback. I started to think about db's as I would if I were to run a statistical analysis such as an ANOVA or MANOVA and that approached helped. So the final tables will look like the following, although I am still getting confused about when and where to use PK, FK's, including junction tables. Specifically, all of these tables starting to look pretty complex. I am not sure anymore if this is now a one_to_many, many_to_many, or a combination thereof. With that, do I ned junction tables or if not, I would suppose I would need foreign keys in some of the tables. I am just not sure where and why to put place them. I will try to study in order to find out, although so far that has been the biggest problem for me understanding:

tbl_Patient
PK_PatientID (autonumber)
PatientID (unique identifier)
FirstName
LastName
Birthdate


I simplified the Location Table:

tbl_LOC
PK_LOC (Autonumber)
LOC
HU


I split up the tbl_Dates into two for Psychiatry and Psychology and renamed the tables

tbl_MD
PK_MD (Autonumber)
LstMD (Last Psychiatric Appointment Date)
MedExp (Psychiatric Medication Expiration Date)
RN (Last Psychiatric Nurse Appointment Date)
RTC (Next Appointment to Return to Clinic Date)
AIMS (Date of Most Recent Adult Involuntary Movement Scale)

tbl_PhD
PK_MH (Autonumber)
LstMH (Last Psychology Appointment Date)
TxPlan (Date of Most Recent Treatment Plan)
Consent (Date of Consent Form)
StudyReleaseDate (End of Study Date)
Last DI85 (Date of Study End Remuneration)

tbl_Classification
PK_Class (Autonumber)
MH (Psychological Classification Score, combination of numeric and alphanumeric: 1-5, a-d, e.g, 3D )
SMI/A (Seriously Mentally Ill via Assessment, Yes/No)
SMI/C (Seriously Mentally Ill in Community, Yes/No)
SA (Suicide Attempt History, Yes/No)

As I had wondered already, and you had suggested, I divided the diagnostic table:

tbl_Dx1
PK_Dx1 (Autonumber)
Dx1 (Primary DSM-IV Diagnostic Code, e.g, 296.90)
Dx1t (DSM-IV Diagnostic Description, e.g, Mood Disorder NOS)

tbl_Dx2
PK_Dx2 (Autonumber)
Dx2 (Secondary DSM-IV Diagnostic Code, e.g., 296.90)
Dx2t (Secondary DSM-IV Diagnostic Description, e.g., Mood Disorder NOS)

Thanks,

R

[rzw0wr]

The rest is simple with a little thought.
Start with the Patient table, it will always be a 1 (PK) to many (FK) to another table in this case.
Everything I see in your database is about a Patient. The patient table as you have it now will never have a FK in it.
Try relating the tables by thinking "What table is an attribute to the table in question?"
You may have to do this several times.
When you make a relationship be sure to give it Referential Integrity as you make it. This is done on the Edit Relationships dialog box.

If you hit a snag give us a post.

You are well on you way and looking good.

Dale