CREATE TABLE `food_group` ( id int PRIMARY KEY NOT NULL, name text NOT NULL ); CREATE TABLE `food` ( id int PRIMARY KEY NOT NULL, food_group_id int REFERENCES food_group(id) NOT NULL, long_desc text NOT NULL DEFAULT '', short_desc text NOT NULL DEFAULT '', common_names text NOT NULL DEFAULT '', manufac_name text NOT NULL DEFAULT '', survey text NOT NULL DEFAULT '', ref_desc text NOT NULL DEFAULT '', refuse int NOT NULL, sci_name text NOT NULL DEFAULT '', nitrogen_factor float NOT NULL, protein_factor float NOT NULL, fat_factor float NOT NULL, calorie_factor float NOT NULL , price DOUBLE); CREATE INDEX food_short_desc_search_index ON food(short_desc); CREATE INDEX food_long_desc_search_index ON food(long_desc); CREATE TABLE `nutrient` ( id int PRIMARY KEY NOT NULL, units text NOT NULL, tagname text NOT NULL DEFAULT '', name text NOT NULL, num_decimal_places text NOT NULL, sr_order int NOT NULL ); CREATE INDEX nutrient_name_search_index ON nutrient(name); CREATE TABLE `nutrition` ( food_id int REFERENCES food(id) NOT NULL, nutrient_id int REFERENCES nutrient(id) NOT NULL, amount float NOT NULL, num_data_points int NOT NULL, std_error float, source_code text NOT NULL, derivation_code text, reference_food_id REFERENCES food(id), added_nutrient text, num_studients int, min float, max float, degrees_freedom int, lower_error_bound float, upper_error_bound float, comments text, modification_date text, confidence_code text, PRIMARY KEY(food_id, nutrient_id) ); CREATE TABLE `common_nutrient` ( id int PRIMARY KEY REFERENCES nutrient(id) );