Schema for Bulk Updates to Canadian Food Manufacturer Listings

The Canadian Food Manufacturer listings at BuyFromTheFarm.ca are unusual in that the pages cannot be user-submitted.

The information is stored in a database compatible with our Canadian Food Labeling Proposal and must be submitted in a format which complies with the following schema. Data items marked with an asterix (*) are required:

MANUFACTURER INFORMATION     A single record for each Company Code.

    Company Code * char(10)  The Company Code used to assign UPC or EA13 barcodes to your products (6-10 digits).
    Name         * char(256) The full name of the Company.
    Contact      * char(256) The name of the Contact Person or Department responsible for Consumer Relations.
    Email          char(256) Email address to be used for General Consumer Inquiries.
    Web Site       char(256) The full url of the Comapany Web site (include 'http://').    
    Address      * char(256) The full Municipal Postal Address (Street or PO Box) for Consumer Relations.
    Address2       char(256) A second address line, if required.
    City         * char(128) Name of City.
    Province     * char(2)   Province or State code (2-letter abbreviation).
    Country      * char(2)   Country Code (2-letter abbreviation).
    Phone        * char(16)  Phone number, toll free if available, for Consumer Relations.
    Fax            char(16)  Fax number, if available, for Consumer Relations.
    Body           text      What should be in the body of the processor listing. 
                             Should be in xhtml 1.0 format, suitable for enclosure in <div class='body'> tags.

INGREDIENT INFORMATION       A single record for each Ingredient used. One Ingredient may be used in one or in many Products
                             If the same Ingredient is used for Products produced using different Company Codes, 
                             one record is required for each Company Code.

    ID           * char(10)  A unique identifier for each ingredient.
    Company Code * char(10)  The Company Code used to assign UPC or EA13 barcodes to your products (6-10 digits).
    Canadian     * dec(6.3)  The percentage of the ingredient originating from Canadian Farms. Defaults to 0.
    Barcode        char(16)  The UPC or EA13 barcode of the ingredient, if known.
    Supplier       char(256) The name(s) of the Supplier(s) from which Ingredient is obtained.
    Body           text      What should be in the body of the Ingredient listing to describe the Ingredient. 
                             Should be in xhtml 1.0 format, suitable for enclosure in <div class='body'> tags.

PRODUCT INFORMATION          A single record for each Product, identified by its UPC or EA13 barcode. 

    Barcode      * char(16)  The UPC or EA13 barcode of the Product.
    Name         * char(256) The full name of the Product, including Brand Name and Package Size.
    Serving      * char(128) Statement of Serving Size for Nutrient Value list.
    Body           text      What should be in the body of the Product listing to describe the Product. 
                             Should be in xhtml 1.0 format, suitable for enclosure in <div class='body'> tags.

PRODUCT INGREDIENTS          A single record for each Ingredient, identified by its Ingredient ID, 
                             used in each Product, identified by its UPC or EA13 barcode. 

    Barcode      * char(16)  The UPC or EA13 barcode of the Product.
    Ingredient   * char(10)  A unique id for the Ingredient.
    Contribution * dec(6.3)  The percentage the Ingredient contributes to the Product. Defaults to 0.

PRODUCT NUTRIENTS            A single record for each Nutrient, identified by its standard Nutrient Value title,
                             used in each Product, identified by its UPC or EA13 barcode. 

    Barcode      * char(16)  The UPC or EA13 barcode of the Product.
    Nutrient     * char(128) Standardized Name of Nutrient (eg. 'Iron / Fer').
    Amount         char(128) Amount of Nutrient per Serving. Defaults to NULL.
    Daily          char(128) Amount Nutrient contributes to Daily Requirements per serving. Defaults to NULL.
                 * Either Amount or Daily MUST have a value. One, but not both, may be NULL. 0 is a permitted value.

INGREDIENT COUNTRY OF ORIGIN A single record for each source Country (Province) for the Ingredient. 
                             The Country Code file we use is available in csv format below.

    Ingredient   * char(10)  A unique id for the Ingredient.
    a2p2         * char(4)   Concatenation of ISO 3166 2-char country code with 2-char province code for Canadian Provinces.
    Contribution * dec(6.3)  The percentage of the Ingredient originating in the Country (Province) identified in a2p2.