I need some input for an Access db I need to develop. The db will be used to track participants in research study (manage their contact data and data collected at each treatment).


A participant consists of both a student and his/her parent/caregiver. If the caregiver is under 18, they need their caregiver to consent for them (until they turn 18, then I need to ensure they get consented themselves).

There is also a person who will always know the whereabouts of the participant. Caregivers and their contact info are expected to change multiple times for a large percentage of our sample.

The schools are randomized, not the participant. There are 3 staff members assigned to a participant: 1 who will be delivering a treatment and 2 who will be collecting data. These will end up changing, though we hope to keep them the same as often as possible.

The diagram i attached is what I have for enrollment.. this doesn't yet include data collection, which is complicated also. There are three types of treatments that will all occur 3-4 times per year for each participant in the treatment group. Some are for the student, some for the parent.


I know you guys get a lot of these types of inquiries from new(ish) members, but any guidance would be helpful. If I'm off-course, let me know

I'm trying to normalize as much as possible with my understanding of it. Most people around here with my same job title end up using a flat table or maybe 3 or 4 connected tables, so I haven't found much assistance.

I would eliminate the three tables which are exactly alike, MotherCG, PersonConsenting and PersonKnows. Keep one of those tables and add in a type field to identify which type of person they are. Probably put the patient in here as well. Then a subordinate table for additional info on the patient. I would, also, put phone numbers/addresses into a separate table as those can change when people move/change phone providers, etc...
If you do the above, then you need a consent table that would just have the ID of the person doing the consent and info about the consent (date, etc...)
Can the staff have multiple roles? i.e: Can the Examiner for one patient be the recorder or interventionist for another patient? If so, then you should do this a bit different, maybe eliminate the roleid, otherwise each staff member would have to be entered multiple times in the staff table for each role.
The Sex table could be removed, if it just denotes M/F. If on the other hand you are keeping track of when they have sex etc... then the relationship is going the wrong way. Consider just a constraint on the column. Same for the Relation table. You may want to consider a code table that holds valid values. Something along the lines of:
Code Master
Code Number Code description
10 Gender
20 Relation

Code Table
Code Number Code Value anything else??
10 M
10 F
20 Mother
20 Father
20 Guardian
Why a random group of schools?
Dave

Thanks Dave!

The treatment is a behavioral (educational) intervention, not pharmaceutical. Schools are an effective way to provide community education rather than more expensive one-on-one education.

I like the idea of combining all individuals into one table, but I'm concerned that would limit my queries. Say for example, I want a form for entering all four people's info related to one participant (which we have as a paper form). Would this still be possible?

_Enrollment Form_
participant
first name:
last name:
DOB:
caregiver
first name:
last name:
DOB:
consenting adult
first name:
... etc.



In regards to the lookup tables (relation, sex AKA gender), is using a combined Lookup or Codes table bad practice? My understanding was that it is, but I could be reading outdated info. (The Access Web - Welcome has been one of my main sources.)

Is this only true if you were to store actual data there and not solely valid field values? If I did this, would I include the table in the relationships or just create a lookup query in the field properties?

How about using Access's "Lookup Wizard"? The website mentioned above strongly discourages it. Though it does seem convenient and would simplify the "relationships" view for when someone else eventually takes over the DB and needs to figure out what is going on.


It is possible that staff would have multiple roles, but it will be rare as any role other than the generic 'field staff' requires advanced training.

I understand about the schools, what I do not understand is having a random group of schools listed in a table.
The all users in a single table, then wanting all of them on a report/query, would mean 4 iterations of the table in the query, one for each of the types of people you are looking for, same as if you stored them in four separate tables.
The values table I have seen in at least 10 very large applications and make heavy use of it myself. I have seen where some installations introduce a value table for each type of value, but to me that is a pain having 1000+ tables when I could just have one table with a few thousand rows, also, makes it easier to pin that one table in memory. This is only a values table, it has actual data, but that data is the valid values for some field(s). If doing this, I would not create a foreign key relationship within the model, just the sql. I have been at a couple of places where in the fact table, they stored which value code the value column applied to. I thought that was a waste of time and space. Ex:
See what I mean. do you really need to see 20 on every row of your person table to know that to find valid values for sex you look at code number 20 in the codes table. I digress, though, for sex, I would put a constraint on the column, only M/F allowed.

Storing the value & not the code definitely makes sense. Access's default (via the wizard) is to store the code and create a link to the table w/o ref integrity. I'll skip the wizard and do it with sql.

You've convinced me on the single code table. Just had heard different things from different people.

The RandomGroup table is meant to be a lookup/code table for which group the school is in: treatment or control. So there's not table of random schools, just the schools in our study and the group they are assigned to.

I've added a set of tables for events & the forms associated with those events. In the DB I will be scheduling the event and filling in whether the required forms were completed for each participant at each event. Does this setup look correct?



Dave - Thanks for all of your help so far. Having a veteran say it looks good or give advice is an unbelievably big help.