Food Bank Tables

The system stores and manages data in the following tables:

  1. USR_FOODBANK – A food bank is an extension of an organization constituent record. An example of a food bank is the Low Country Food Bank (http://www.lowcountryfoodbank.org/). To track food inventory for the Low Country Food Bank, we need to add a constituent record within the CONSTITUENT table and a row in the new USR_FOODBANK table. A one-to-one relationship ties the USR_FOODBANK table to the CONSTITUENT table.

  2. USR_FOODBANKTYPECODE – A code table that lists the different types of food banks. It is used to categorize food banks.

  3. USR_FOODITEM – A simple list of Food SKUs such as cereal, rice, or canned soup.

  4. USR_FOODBANKTXHEADER – The food bank transaction (TX) header table stores top-level inventory transaction records for food banks. Every time someone donates or receives food from a food bank, a row is created in this table. Top-level transaction information such as the constituent who donated or received food, the total amount of food in the transaction, the transaction date, and receipt information are tracked in this table. The USR_FOODBANKTXHEADER table tracks the type of transaction (Receive, Distribute) and is used to calculate current inventory levels for a food bank. A food bank tracks one-to-many transaction header records. A constituent can participate in one-to-many food bank transactions.

  5. USR_FOODBANKTXDETAIL – The food bank transaction detail table stores the individual food items and amounts for a given transaction. If a user donates a gallon of milk, five boxes of cereal, and 10 cans of vegetables, then one row is created in the USR_FOODBANKTXHEADER table and three rows are created in the USR_FOODBANKTXDETAIL table. The LINEAMOUNT column is a calculated field with an expression equal to FoodItemAmount * Quantity.

Figure: Food Bank Entity Relationship Diagram