Foodclub:Manual:DatabaseAdministrator

From Foodclub
Jump to: navigation, search

For Database Administrators

Warnings

  • It can be "dangerous" to update a database when the foodclub system has items in it that are from the database -- especially if you change product codes, Invoices will not be able to calculate correctly.  Worse, if you create overlapping product codes (two items with the same code) that will cause erroneous items to be ordered in Group order / Final order.

Database fields

Regular items

The following should be set:

Sale items

  1. In addition to the fields for a regular item, set the following:
  2. The settings on the administration page for the source need to be updated to display sale items. The exact location is: Administration -> Source settings -> (under Product database options) "Show sale_price column..."
  3. Once sale prices are enabled for the source, when the user searches the database the regular price will appear in black and the sale price will appear in red. Also, a percent discount will be automatically calculated and displayed. The sale price will be the default on the invoice.

Split items

  1. In addition to the fields for a regular item, set the following:
  2. You do not want items which you can not split to show up on the "Splits" page. Quite often a source database has items which you want to allow splits for and items which you don't want to allow splits for. To accomplish this:
    • set the valid_split_increment field in the database to 0
    • Admin -> Product database -> Disable users from manually entering items
    • Admin -> Product database -> Enable user input validation on split requests page
  3. It is often possible to purchase produce in bulk. When selecting the size field, think of something that customers are familiar with. Examples:
    • For 1/2 bushel of apples, you figure it weighs about 25 pounds. We would put "25 pounds" in the size field because customers like to order say 5 pounds of apples.
    • For a 1 pound bag of herbs, we would put "16 ounces" in the size field, because customers like to enter their herb quantity in ounces.
    • For the above examples, none of the "priced per pound" settings should be used.

Priced per pound items

  1. In addition to the fields for a regular item, set the following:
    • price per weight
    • is priced by weight
    • When you fill out "price", it is only an estimate of what a unit costs. This is only used so that the user can have a feel for what their total will be.
  2. Examples
    • This is usually only used for meats, as meats are expensive and the farmer and customer both want exact poundage.
    • Cantaloupes are usually priced $4.00/melon so these fields should not be set. That said, it is possible for a farmer to charge for cantaloupes by weight and these fields could be used.
    • If a farmer charges $2.00/lb for cucumbers, the fields could be used. However, if someone orders three pounds of cucumbers, some farmers choose just to give them a little more and not bother with inputting exact weights
  3. Current software limitations are if a item is priced per pound, it can not be split. The only case where we would like to do this so far is when we split a half cow which is based on poundage. However, computing the amounts by hand is not too burdensome considering how often this occurs.

Automatically prorating shipping based on shipping weight in the database

Images

Options for updating the database

  • For massive databases or databases which change often, we like to automate the update process. Basically we get an Excel or PDF from the source and write a script which automatically updates the foodclub database.
  • If the database is small, we usually prefer to update it directly using phpMyAdmin
  • For larger, more complicated databases, we usually prefer to maintain a separate Excel sheet which we import.
  • If it is a "less used" database which you do not want to set up, you can simply allow users to do manual input. There is always a risk of error here, as users can do typos. But it must be weighted against the time involved in a database set up.

Using Excel sheets

  1. Be familiar with the following Excel commands:
    • Insert, delete and replicate row
    • Copy and clear contents
    • Hide and show columns
  2. Get an Excel sheet matching the content of the database to be updated. If you are unsure that you have the latest Excel sheet you can export the current database into Excel.
  3. Update the Excel sheet
    • Update the appropriate fields
    • If a new record needs to be inserted, insert a line, copy in a similar line, and make updates
    • If you are doing items priced per pound using a "smart" Excel sheet which contains code to copy and compute fields, updating will be slightly easier. Only prices in the description field should change. Excel copies these into other fields as appropriate. "~" must immediately precede the poundage. "$" must immediately precede the cost. The cost estimate is computed by multiplying the "~" number by the "$" number.
  4. Save the Excel sheet as a CSV file
  5. Use the import command to update the actual database.

Using phpMyAdmin

Getting to the desired database

  1. Go to http://foodclub.org/phpmyadmin/
    • User Name: your_phpmyadmin_user (all lower case)
    • Password: (all lower case)
  2. In the left blue margin, click "foodclub", this will bring you to our databases
  3. In the left blue margin, click the database you are interested in. If you want to experient with a database, use one that is no longer in use such at private_gbgcorinna
  4. To edit an existing row in a database, just click on the 'pencil' for the given row.
  5. To add a new item to the database, click on the 'Insert' tab.

Using the top tabs

Browse (and edit)
  • To look at and edit what is really out there
  • Steps:
    1. Select Browse tab
    2. If you only want to edit/delete one record...
      1. Click edit or delete on the appropriate row.
    3. If you are editing/deleting lots of records...
      1. Check the boxes by each record you wish to edit/delete or click select all at the bottom of the page
      2. Next to select all at the bottom, either click change or delete
Insert
  • To add a new record (product). Remember to give your record a new and unique key
SQL
  • To delete the contents of your database (it's a good idea if you are importing a new database from a csv file).
    • Once you click SQL, then click the delete, then click go
  • To get a nice printed table of your product database containing only the fields which you want
    • Steps:
      1. In the left side frame, click the table you want to lis, e.g. regional_US_organic_pastures
      2. Click SQL tab
      3. In the box titled "Run SQL query/queries on database foodclub:", after the word SELECT, select the "*". If you leave the "*", it will print all fields of the database and there are many fields which you will not not interested in
      4. Replace the "*" with the fields you are interested in. Select the fields from the field box. After you have selected a field press "<<" to insert it. You can continue selecting many fields.
      5. When you are done selecting fields, press "GO"
      6. Under "Query results operations", select "print view". The result is a nice, readable table.
  • To change many records at a time, if you can think of a rule to apply. However, you must be familiar with SQL commands.
    • Examples:
      1. update regional_tuscarora set category=manufacturer
      2. UPDATE regional_tropical_traditions SET sale_price=NULL WHERE sale_price=0
      3. delete FROM `regional_tropical_traditions` WHERE 1 -- deletes the entire contents, be sure to hit "GO" to complete the process
Export
  • When to use
    • Use it to create an initial Excel sheet once you have created a new source. Your initial Excel sheet will contain row headings and otherwise be blank
    • If for any reason you think your current Excel sheet is out of date, download the current one. Better safe than sorry. Use it to create an initial Excel sheet once you have created a new source. Your initial Excel sheet will contain row headings and otherwise be blank
  • How to export
  • Select Export tab
    • Export Method:
      • Quick vs. Custom, Custom allows for column headings which make your document much more user friendly.
    • Output:
      • No need to select anything
    • Format:
      • Select something which is on your computer and easy to edit such as Microsoft Word 2000
    • Select Go at the bottom, you will be prompted where to save your file on your computer
Import
  • To update the Foodclub database from an Excel/CSV file
  • Import steps
    1. Your Excel sheet must be saved as a CSV file. When editing your Excel sheet, use the "Save as" command and look for the option to save as a CSV file.
    2. Select SQL tab at top of page
      • It is always a good idea to empty (delete) the current database before importing. Importing only overwrites what is there. If there are old items with different codes (keys), they would hang around and this is not what you would want.
      • See SQL instructions above.
    3. Select Import tab at top of page
      • Under File to Import:
        • Select Browse your computer and find the .csv file which you want to upload
      • Under Format:
        • Select CSV using LOAD DATA
      • Under Format-Specific Options:
        • Check Replace table data with file
        • Set Columns terminated by to "," (no quotes, just a simple comma)
        • For the rest of the fields, the default is fine
      • Select Go at the bottom
    4. Select Browse tab at top of page
      • After an import, it is always good to Browse the file. Double check that it is correct.
      • When importing from an Excel sheet which contains a header, you will see the header record gets copied into the database. Select "x" by it's row to delete it.
  • For more details (and options like just importing specific fields on a .csv file), see http://foodclub.org/wiki/Foodclub:Documentation:FAQ#Brief_step-by-step_instructions_for_importing_a_pricelist_from_a_distributor_using_phpMyAdmin


Empty
Retrieved from "http://wiki.foodclub.org/w/index.php?title=Foodclub:Manual:DatabaseAdministrator&oldid=13794"