Foodclub:Documentation:Database fields

From Foodclub

For users who choose to maintain their own private product database using phpMyAdmin, this document explains each field in your private_<account-name> table.

Thanks to Sally Zimmermann of Nourishing Connections Food Club for this document.

Contents 1 code 2 category 3 sub_category 4 sub_category2 5 manufacturer 6 description 7 size 8 case_units 9 each_size 10 unit_weight 11 case_weight 12 price 13 sale_price 14 unit_price 15 retail_price 16 price_per_weight 17 is_priced_by_weight 18 valid_price 19 taxed 20 upc 21 origin 22 num_available 22.1 For out of stock items 22.2 To count down inventory 23 valid_order_increment 24 valid_split_increment 25 last_updated 26 last_updated_by 27 last_ordered 28 num_orders

code

• Character 16
• Product code
• Foodclub software treats this as a record key. When combining products for the merged order, like product codes are combined.
• Warning, if you change a code once orders have been placed, your order most likely will not merge together as it should. If you are going to change product codes, the best time to do this is after an archive and before new orders come in.
• Some companies (e.g. Frontier, Green Pastures) already have product codes assigned to their products. If this is the case, we use their product code.
• Some companies (Toigo, Tuscarora, usually the really local ones) do not use product codes. In this case, we assign easy unique codes. Keep in mind that product codes are alphabetized in the merged order and final merged order and we want like items grouped together for readability. Some examples:
  • apples1
  • apples2
  • apples3
  • potatoes1
  • potatoes2

category

• Character 128
• Displayed in the “product search” function. Try to divide the products offered for sale into major categories and use those words. Some examples:
  • apples
  • potatoes
• Since this field is not displayed on the final merged order and manufacturer is, we tend to leave it blank and put the category in the manufacturer field.

sub_category

• Character 128
• Currently, not used for anything. Sometimes we set these anticipating software upgrades. Some examples:
  • Gala
  • Nitany
  • Red (potatoes)
  • White (potatoes)
• Eventually a product “browse” feature will be available where you can browse by category, then sub_category, etc.

sub_category2

• Character 255
• Currently, not used for anything. We don’t set it.

manufacturer

• Character 255
• displayed on the order form, merged order form and bookkeeping status. For now at least one foodclub, sets it to the "category" since category is not displayed as we would like and manufacturer is
• Consumers like know where something is coming from.
• When we are dealing with a local orchard, all products from the orchard will simply have the orchard name. For example: Toigo Orchards

description

• text of unlimited length
• displayed on the order form, merged order form and bookkeeping status
• Be as descriptive as possible. On seasonal items, it is helpful to say what the season is.

size

• character 32
• The number is used only in split processing. Everyone orders part of the size and when the parts add up to the “size”, the item gets ordered. The size field must start off with a number and whatever comes after the number is ignored by the software, and is only useful for human readability. Examples:
  • size: "12 jars/case" – Sally orders 4, Elizabeth orders 2 and Cyndi orders 6. It is understood that the quantity the user enters is in the same units as "size"
  • size: "10 lbs" - Sally orders 6 lbs, Elizzabeth orders 2 lbs and Cyndi orders 2 lbs.
• This number is displayed on the regular order form, but not used for anything. If you will not be doing splits with the item, you could leave the field blank or put whatever you want in it. Often we choose to fill size with something informative for the user - e.g. 8 oz.

case_units

• integer
• not currently used – leave blank or set to NULL

each_size

• character 24
• not currently used – leave blank or set to NULL

unit_weight

• decimal
• not currently used – leave blank or set to NULL

case_weight

• decimal
• not currently used – leave blank or set to NULL

price

• decimal
• self-explanatory. Though it should be noted this is not necessarily always the price that the item will cost at delivery, since sometimes the sources (notably distributors) change their prices.

sale_price

• decimal
• sale price is only displayed and used for private databases if explicitly enabled in the Administration page. If enabled, it displays in red, and calculates the discount from normal price (above). Also, sale_price is used instead of price when adding an item from a search to a user's order or splits.

unit_price

• decimal
• not currently used

retail_price

• decimal
• not currently used

price_per_weight

• decimal
• If "is_priced_by_weight" = 1, this number is used for the default price per weight on the Bookkeeping page. It can be changed by the person doing bookkeeping.
• If "is_priced_by_weight" = 0, this field is ignored

is_priced_by_weight

• boolean
• Most items are not priced by weight (e.g. container of laundry soap, Toilet paper, jar of pasta sauce, etc.) In these cases the flag should be set to “0”
• Farm goods are often priced by weight (when you order 1 lb of ground beef, you usually get slightly over or under and get charged for the amount you receive). In these cases the flag should be set to “1”

valid_price

• boolean
• Should always be set to “1”
• If it is not set, prices do not carry over to the bookkeeping page and it is a pain to manually enter them

taxed

• boolean
• Should be set to “0” or “null”

upc

• character 24
• not currently used – set to NULL
• there only for back-end purposes for official distributor databases

origin

• character 64
• Should be set if the country of origin is not the U.S. For example:
  • In the Frontier catalog some items are from other countries and folks like to know where their products are from. In Frontier, country of origin gets displayed with the product search

num_available

• integer
• If set to NULL (all capital letters), the software will not use it, even if the Use num_available setting is enabled in Administration.
• Do not leave this field blank or set it to "null" (lower case letters), something doesn't work??

For out of stock items

• Set to “0” when an item is out-of-stock and the end user will not be able to order it. Some examples:
  • In our orchard database items go in and out of stock on a seasonal basis. Rather than deleting an out-of-season item from the database we choose to set num_available to “0”. Usually in the description field we give it’s seasonal dates. At least this way the user can figure out what is coming when.

To count down inventory

• Feature will be coming soon.
• The software will automatically count down when items get ordered.

valid_order_increment

• decimal
• For input error checking on “order form” Users will only be able to enter multiples of this number when ordering. Examples:
  • It is usually set to 1. This means the user can only enter 1, 2, 3, 4,… for the “Quantity” field on the order form.
  • valid order increment = "1" - the user can only enter integers for the "Quantity' field on order form
• This field is ignored unless the Enable user input validation when adding items from search on users' order form is set in Administration.

valid_split_increment

• decimal
• For input error checking on “split request”. Users will only be able to enter multiples of this number when ordering splits. Examples:
  • valid split increment = ".25" - the user can only enter .25, .5, .75 for the “Quantity” field on “split request”
  • valid split increment = "1" - the user can only enter integers for the "Quantity' field on "split request"
• Think of the people who will be sorting the item, and make sure their job will not be too difficult.
• This field is ignored unless the Enable user input validation on split requests is set under Administration. Food Club admins can click the Administration link at the bottom of any of their source pages to check on this setting.

last_updated

• timestamp
• Do not set
• this is shown under the Valid as of column in search results. If you don't enter anything in phpMyAdmin, or in an import file, it will get set to the date and time you enter/import the item.

last_updated_by

• character 12
• not shown anywhere currently

last_ordered

• timestamp
• not shown anywhere currently

num_orders

• integer
• This is currently shown in the Orders column in search results for private databases, and gets updated when archiving the order, but only when phpMyAdmin is not enabled. When using phpMyAdmin, this field is not shown anywhere so it can be ignored.