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

  • name
  • contact information
  • work information
  • state support info

But we also need to track

  • past visits

and the number of visits will grow with each new visit.

We also need to keep track of the dependents associate with each household. 

  • dependents

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
  • how long they've been in the area
  • whether their rent is subsidized
  • what other forms of monetary assistance they are receiving
  • food stamps
  • medical insurance
  • whether and how much income derives from work
  • schooling
  • distance and travel to MFB

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 potentially unlimited.

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.