Home

Data Schema

The tables that make up the complete system are:

• Location - Where we are
• Commodity - What to grow
• Item - What it takes
• Price - Cost of Items
• Phase - What part of production
• Materials - What items needed to grow

All the tables below show an xxxx_id as a unique identifier. These are not included in the farm budget data tables, but are shown below for completeness. In most cases, tables need to just include all the unique columns, however for items we use the shorthand commodity (unit) as in Farm Gas (gal). If there is only one unit for an item, we can write like Farm Gas.

Location

Currently data is reported on three regions; the county, the AG district, and the State. When a user requests a price, they get back as description of where the price came from. We move from most to least specific; that is we go from county-> district-> state -> national based on the input. See the [Geographic Codes](Geographic Codes) for more information.

Commodity

This is the list of any item that can be budgeted by the data. Its the list of all the existing commodities that can be grown.

commodity_id	commodity	description

The commodity is unique in the table.

The current proposal is to use NASS data as our commodities.

Items

Items are anything that can be identified in the Production table. Item tables all look like this:

item_id	item	unit	category	description

The combination (item,unit) is globally unique. Category must exist in the category files

Category

Items are arranged by category. This is just to help organize them for the user. They can be moved at any time.

category_id	category	parent_id	description

Prices

Prices are the general term for production materials that are used. They are broken into a number of tables, but that is just for convenience, all price tables are union-ed together when used in the budgets.

Prices are reported in terms of their item, year, geographic location, unit and price per unit.

price_id	item	location	year	auth_id	price

The combination of (item_id,location,year) are unique within an authority. Authorities are assigned based on the location of the datafile within the (farm-budgets-data) file structure.
The item is specified either as *

item_id's must exist in the items table, and auth_id are authorities for the prices

Price Tables

The following price tables exist:

• Labor - Includes Labor Prices
• Fuel - Are various fuel prices
• Pesticides - Pesticide costs
• Land Rent - Land Rent costs
• Other - All Other costs

Production Materials

budget_id	commodity	location	phase	material	amount

This table is the amount of all the items that is need to grow a particular commodity

Using the farm budget data for SWAP Modeling

One of the main purposes for the (farm-budgets-data) is to provide support for the creation of regional budgets that can be input into economic models like SWAP. SWAP in particular needs budgets divided into a small number of categories; Land Rent, Labor, Supplies, and Water. To that end, we will include a function that reports budgets per acre, which include the above breakdown.

In order to calculate this for the AHB-PNW Crop management Zones, we run the budget application for each county in each CMZ, we then weight the budgets by the number of acres that exist for each commodity and for each pixel within the AHB grid.

So, we create a table as so: