Initial checkin of nutridb.org and basic subversion directory structure

1 files changed, 47 insertions, 0 deletions

diff --git a/README b/README
new file mode 100644
index 0000000..b5f014d
--- /dev/null
+++ b/README
@@ -0,0 +1,47 @@
+INSTALLING A NEW VERSION OF THE SR DATABASE
+----------------------------------------------------------------------
+As the data comes from the USDA NAL not every food will have common
+weights associated with it in the weights table.  In such cases it is
+assumed that the nutrient data is for 100g.  However, as of SR19 the
+vast majority of foods have common weights and measures - out of 7,293
+foods only 532 don't have weights.  We want to keep the database
+as close to the original as possible so that future upgrades to new
+releases of the data are as easy as possible.  However, rather than
+having to add a lot of extra code to work around these special cases
+that have no common weights and measures, it seems easiest to me to make
+sure that every single food has at least one entry in the weights table.
+This will help to standardize the code and reduce exception handling.
+In order to do this only one simple query needs to be run on the database
+after a new install of fresh data from the USDA NAL:
+
+INSERT INTO weights (ndb_no, seq, amount, msre_desc, gm_wgt, num_data_pts)
+SELECT DISTINCT nutrientData.ndb_no, '1', '1', 'unit', '100', '999'
+FROM nutrientData LEFT JOIN weights
+	ON nutrientData.ndb_no = weights.ndb_no
+WHERE weights.ndb_no IS NULL;
+
+This should effectively add an entry in the weights table for each
+ndb_no in nutrientData that didn't previously have an entry.  The values
+are representative of 100g of that food.  The value of '999' in the
+field num_data_pts is bogus and is only there as a way to easily
+identify these added records later should we wish to delete or alter
+them at once.
+
+----------------------------------------------------------------------
+Also, since we allow users to search other user's saved foods and meals
+then it is expedient to add user saved foods and meals as categories.
+Thus, we need to enter two entries to the food categories table.  I have
+given them low categories numbers, below the current lowest number, so as
+to not conflict with future categories that the USDA may add.
+
+INSERT INTO foodCats(fdgrp_cd, fdgrp_desc) VALUES('0001', 'User saved foods');
+INSERT INTO foodCats(fdgrp_cd, fdgrp_desc) VALUES('0002', 'User saved meals');
+----------------------------------------------------------------------
+
+We also need to add column to foodDescs, userFoods, and userMeals that
+will incremented each time that a user selects a particular item after a
+search.  The higher the number, the higher popularity we attach to it.
+
+ALTER TABLE foodDescs ADD COLUMN popularity INT DEFAULT 0;
+ALTER TABLE userFoods ADD COLUMN popularity INT DEFAULT 0;
+ALTER TABLE userxMeals ADD COLUMN popularity IN DEFAULT 0;