A Set of Normalized Tables for the Missoula Foodbank
Normalized Tables. If we think about our data, we have the information that a client must fill out on a form that relates directly to them. This includes things such as
But we also need to track
and the number of visits will grow with each new visit.
We also need to keep track of the dependents associate with each household.
We want dependents names and ages. Remember, dependents can include children, a spouse and elderly dependents. This list will not necessarily grow with each visit. But there is going to be a one-to-many relationship between each head of household and their dependents.
I am starting with the data dictionary for the clients table. Remember, MFB already had their standard form to be filled out on a sheet of paper. I do not have a copy of this. But this form largely reflects the contents of that paper form.
|Notice there are all kinds of questions that are
asked on the form that relate to things like
This is definitely a synopsis of the questions. But you can see how this database can be used to cull other information about the MFB clients.
Now we get into the table that will contain information about dependents. The data dictionary is really quite simple with only a key, foreign key, name and age information.
|Remember, there is a one-to-many relationship
between the clients and this dependents table. Sometimes there
are no dependents, so the minimal cardinality is zero. Other
times there are many dependents and the maximum cardinality is
Finally, we have the data dictionary for the table that records the past visit information. It has a key, the foreign key referring back to the client, the date of the visit and any interviewer remarks.
|The following diagram illustrates the relationships between the tables.|
|Remember, this relationship diagram is created under the Tools menu by choosing the Relationships item within it. Then the tables are added and the relationships connections are drawn in. Both of these relationships are one-to-many from clients to dependents and clients to past-visits.|