Back to the Data Model
A. The requirement is to design a Database for Family Advocacy,
A second update to the Requirements was contained in a second email on March 8th. :-
I only have two additional points :-
First, when it comes to my Interventions, sometimes the results aren't immediate but may take a few days or weeks.
So I don't know if that impacts the intervention table at all.
Second, I will need a table that documents the Children's progress in school, specifically something that tracks
their attendance record, their report card, standardized test scores and any disciplinary matters.
Other than that, this database has everything I need that I can think of.
An update to the Requirements was contained in an email on March 8th. :-
I think the Interventions table is wonderful.
My only comment is that I wonder if it could be possible to link Interventions to the Events table.
I say this because many of my interventions occur within a meeting (and often times I do several
Interventions in a meeting).
Let me know if this relationship is possible and beneficial.
The only other thing that I see missing from this data model is a table that documents the individual
Goals of my families.
The Parents come up with them and then we work towards them in both our meetings and individually.
For instance, one of my mothers wants to get her GED.
We set the Goal.
We've had Meetings where I've educated her about resources and she has gone out on her own to do testing.
So the Goal each has Steps (some of which become obsolete and unnecessary, others which we complete),
and each of the Steps has a projected Deadline and Actual Completion Date.
I think a table that contains the Goals, their Steps and the Dates of Completion is the only
thing else I see needed in this data model.
The Requirements were originally defined on March 1st. in the following terms :-
I am the director of a nonprofit pilot program called the Family Advocacy program in Kalamazoo, Michigan.
I basically act as a resource broker helping low-income families identify and acquire the resources they
need to provide for their children and to achieve their own goals.
I'm part social worker, part advocate, part teacher, part counselor, part coach---doing whatever I can to
aid these families in climbing out of poverty. Most of my parents have the goals of pursuing higher education
to broaden their career options;
others have requested that I help them get their children examined and diagnosed; the goals are incredibly
varied and diverse.
One last thing I forgot to mention is that I am also responsible for coming up with a system of monitoring and
evaluating all the changes and advances that my families make.
In essence, I'm charged with the task of creating a database. I've never done something like this before and
I'm lost at how to take my daily work with the 15 families I work and break it down into tables and relationships.
It seems that you would have a fair idea of how to help me solve this dilemma, considering your stabs at data modeling
the Middle East Conflict to your life and work.
I would really appreciate any advice or direction you can give me on this matter.
I've been poring over your databases (after having picked up a few books on Access and several nights trolling Access forums),
and it seems to me that my database is a mix of My Life, Patient Care, Generic Foundation and School Management System.
I need to not only keep track of my clients and what we do together, but I also need to keep track of the changes my
clients undergo from the moment they begin my program until they end it, a time frame that will roughly span 9 months
(and for the amount of changes I need to record, the Social Workers Case Mgt is just too simplified).
For instance, I would need to catalog the family's state before beginning my program-if they were unemployed, if they
were receiving welfare (and how much), if they were living in an apartment, etc. Then, I would need to catalog if any of
these "demographics" change-if the parent became employed, the family moved into a house with a lease-to-own contract;
if they got off welfare.
In order to do this update, I wouldn't want to "delete" their previous status because then that would be a loss of valuable data for me.
I also need to keep track of how many times I meet with each family; what I do in these meetings and the results of the meeting.
At first I thought this was a relatively simple task.
I could make an Events table and then have the individual IDs for the parents and then a "notes" attribute where I could write things.
However, upon contemplation, I realized that a lot of what is done in our meeting can be broken down into categories:
I reference a resource for them; they access the resource;
I teach them something (from a skill like making a resume, to how to do an IEP with a school);
they update me on what has changed or improved in their life; etc.
Even though these things are qualitative data, I was thinking that maybe I could use the database to "count" and "categorize"
their occurrences so I could gain aggregated numbers on our activities.
Being able to mark trends or typify the interactions I have with these families is important from an evaluative perspective and the
continuation of the program.
So even for when I reference a resource to the family, it would also be important to break this down as well so that I can track what
kind of resources I typically refer, how many families use it and if they cannot use it if this was their fault or the fault of the agency.
For instance, in one of my families, I taught the parent about how to do the IEP process in schools.
We then wrote a letter to the school and now the school is communicating with us on what to do next.
In this process, I've referred this parent to another agency who specifically helps with strategizing for the IEP.
You could say that what I've taught the mother is both "information" and a "skill"---given the normalization rules of databasing that
each attribute have only one value, I cannot label the meeting we had as both "information-distribution" and "skill-building"
(these are examples of some of the codes that I would use).
On top of this, I also have established goals that each of my families is trying to reach.
This would be another important part of the database: not just stating the Goal, but tracking each Step they complete towards it
as well as when it is eventually completed.
The "Goals" table would thus have to be linked to the "Events" table and even to the demographics table (e.g., when a family moves
into a bigger house that they hope to buy, this completes their goal; it is an Event in their life; and it represents a change in their Address).
Finally, I also need to keep track of the Children in the Family -
- What Resources I've referred for them
- What Activities and Goals I have achieved with them.
Pretty much the same kinds of tables as the parents, but also including table(s) that document their progress in school.
My biggest problem is that I don't know how to update the states of the family (e.g. employment, living situation, financial situation,
welfare situation, health situation) as they change without losing the previous data.
I also don't know how to do the Events and Goals table to categorize these very individualistic actions and changes into broader groups.
So, I know that I would have a "Family/Household" table, a "Parent" Table, a "Child" table, each one listing Demographics.
For the parents, an "employment" table, an "assistance" table could be linked to the larger "Parents" table.
Then I could have an "Events" and "Goals" table where the Parent ID is attached.
To the "Events" table I could have linked a "Resources" table and a "Categories" table that lists all the different potential
categories I could have (but then how could I apply multiple categories to the same event)?
This is about as far as I've gotten.before feeling very overwhelmed.
I'm sorry that my email is a bit disorganized and incoherent.
Thank you for taking the time to read it and consider my case.
B. Things of Interest :-
C. How are they related ?
C.1 A Family can have zero, one or many Goals.
C.2 A Goal can have one or many Streps.
5th. March 2009