| create database seerbharat; |
| use seerbharat; |
|
|
| SET NAMES utf8mb4; |
| SET FOREIGN_KEY_CHECKS = 0; |
|
|
| CREATE TABLE plant_parts ( |
| part_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, |
| part_name VARCHAR(100) NOT NULL UNIQUE |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
| CREATE TABLE usage_categories ( |
| usage_category_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, |
| category_name VARCHAR(150) NOT NULL UNIQUE |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
| CREATE TABLE conditions ( |
| condition_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, |
| condition_name VARCHAR(200) NOT NULL UNIQUE |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
| CREATE TABLE ingredients ( |
| ingredient_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, |
| ingredient_name VARCHAR(150) NOT NULL UNIQUE |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
| CREATE TABLE animal_types ( |
| animal_type_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, |
| animal_type_name VARCHAR(100) NOT NULL UNIQUE |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
| CREATE TABLE locations ( |
| location_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, |
| village_name VARCHAR(150), |
| district VARCHAR(150), |
| state VARCHAR(150), |
| UNIQUE KEY uq_location (village_name, district, state) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
| CREATE TABLE plants ( |
| plant_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, |
| local_name VARCHAR(200) NOT NULL, |
| scientific_name VARCHAR(200), |
| plant_type VARCHAR(100), |
| botanical_identification_by VARCHAR(200), |
| created_at DATETIME DEFAULT CURRENT_TIMESTAMP, |
| KEY idx_plants_local_name (local_name), |
| KEY idx_plants_scientific_name (scientific_name) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
| CREATE TABLE plant_alternate_names ( |
| alt_name_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, |
| plant_id INT UNSIGNED NOT NULL, |
| alternate_name VARCHAR(200) NOT NULL, |
| language_or_dialect VARCHAR(100), |
| FOREIGN KEY (plant_id) REFERENCES plants(plant_id) ON DELETE CASCADE, |
| UNIQUE KEY uq_plant_alt_name (plant_id, alternate_name) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
| CREATE TABLE informants ( |
| informant_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, |
| tribe_name VARCHAR(150), |
| age_raw VARCHAR(20), |
| age_min SMALLINT UNSIGNED, |
| age_max SMALLINT UNSIGNED, |
| gender VARCHAR(30), |
| role_in_community VARCHAR(150), |
| knowledge_source VARCHAR(200), |
| location_id INT UNSIGNED, |
| FOREIGN KEY (location_id) REFERENCES locations(location_id) ON DELETE SET NULL |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
|
|
|
|
| CREATE TABLE survey_entries ( |
| entry_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, |
| original_entry_id VARCHAR(100) UNIQUE, |
| submitted_at DATETIME, |
| timestamp_2 DATETIME, |
| consent_given BOOLEAN NOT NULL DEFAULT FALSE, |
|
|
| plant_id INT UNSIGNED NOT NULL, |
| informant_id INT UNSIGNED, |
| location_id INT UNSIGNED, |
| gps_latitude DECIMAL(10,7), |
| gps_longitude DECIMAL(10,7), |
|
|
| usage_category_id INT UNSIGNED, |
| purpose_description TEXT, |
|
|
| is_used_for_animals BOOLEAN DEFAULT FALSE, |
|
|
| condition_id INT UNSIGNED, |
| condition_as_reported TEXT, |
| detailed_disease_description TEXT, |
|
|
| preparation_method TEXT, |
| mode_of_use TEXT, |
| ingredients_mixed_raw TEXT, |
| used_alone_or_combined VARCHAR(255), |
|
|
| dosage_quantity DECIMAL(10,2), |
| dosage_unit VARCHAR(50), |
| frequency_of_administration VARCHAR(150), |
|
|
| effectiveness_rating DECIMAL(3,1), |
|
|
| has_cultural_ritual_use BOOLEAN DEFAULT FALSE, |
| is_culturally_sensitive VARCHAR(100), |
|
|
| created_at DATETIME DEFAULT CURRENT_TIMESTAMP, |
| updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
|
|
| FOREIGN KEY (plant_id) REFERENCES plants(plant_id) ON DELETE RESTRICT, |
| FOREIGN KEY (informant_id) REFERENCES informants(informant_id) ON DELETE SET NULL, |
| FOREIGN KEY (location_id) REFERENCES locations(location_id) ON DELETE SET NULL, |
| FOREIGN KEY (usage_category_id) REFERENCES usage_categories(usage_category_id) ON DELETE SET NULL, |
| FOREIGN KEY (condition_id) REFERENCES conditions(condition_id) ON DELETE SET NULL, |
|
|
| KEY idx_entries_plant (plant_id), |
| KEY idx_entries_informant (informant_id), |
| KEY idx_entries_condition (condition_id), |
| KEY idx_entries_location (location_id) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
|
|
|
|
| CREATE TABLE entry_parts_used ( |
| entry_id INT UNSIGNED NOT NULL, |
| part_id INT UNSIGNED NOT NULL, |
| PRIMARY KEY (entry_id, part_id), |
| FOREIGN KEY (entry_id) REFERENCES survey_entries(entry_id) ON DELETE CASCADE, |
| FOREIGN KEY (part_id) REFERENCES plant_parts(part_id) ON DELETE CASCADE |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
| CREATE TABLE entry_ingredients ( |
| entry_id INT UNSIGNED NOT NULL, |
| ingredient_id INT UNSIGNED NOT NULL, |
| notes VARCHAR(255), |
| PRIMARY KEY (entry_id, ingredient_id), |
| FOREIGN KEY (entry_id) REFERENCES survey_entries(entry_id) ON DELETE CASCADE, |
| FOREIGN KEY (ingredient_id) REFERENCES ingredients(ingredient_id) ON DELETE CASCADE |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
| CREATE TABLE entry_animal_types ( |
| entry_id INT UNSIGNED NOT NULL, |
| animal_type_id INT UNSIGNED NOT NULL, |
| PRIMARY KEY (entry_id, animal_type_id), |
| FOREIGN KEY (entry_id) REFERENCES survey_entries(entry_id) ON DELETE CASCADE, |
| FOREIGN KEY (animal_type_id) REFERENCES animal_types(animal_type_id) ON DELETE CASCADE |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
| CREATE TABLE entry_plant_combinations ( |
| combination_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, |
| entry_id INT UNSIGNED NOT NULL, |
| related_plant_name VARCHAR(200) NOT NULL, |
| related_plant_id INT UNSIGNED, |
| combination_type ENUM('Commonly Combined With','Never Combine With') NOT NULL, |
| FOREIGN KEY (entry_id) REFERENCES survey_entries(entry_id) ON DELETE CASCADE, |
| FOREIGN KEY (related_plant_id) REFERENCES plants(plant_id) ON DELETE SET NULL, |
| KEY idx_combo_entry (entry_id) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
|
|
|
|
|
|
| CREATE TABLE ritual_details ( |
| entry_id INT UNSIGNED PRIMARY KEY, |
| is_sacred_ritual VARCHAR(150), |
| ritual_use_description TEXT, |
| who_can_collect VARCHAR(200), |
| occasion VARCHAR(200), |
| symbolic_meaning TEXT, |
| ritual_process TEXT, |
| pre_collection_ritual TEXT, |
| sacred_lunar_day_restrictions VARCHAR(200), |
| FOREIGN KEY (entry_id) REFERENCES survey_entries(entry_id) ON DELETE CASCADE |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
| CREATE TABLE safety_info ( |
| entry_id INT UNSIGNED PRIMARY KEY, |
| side_effects TEXT, |
| side_effects_observed TEXT, |
| restrictions TEXT, |
| foods_activities_to_avoid TEXT, |
| FOREIGN KEY (entry_id) REFERENCES survey_entries(entry_id) ON DELETE CASCADE |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
| CREATE TABLE availability_status ( |
| entry_id INT UNSIGNED PRIMARY KEY, |
| season_available VARCHAR(150), |
| availability VARCHAR(100), |
| availability_vs_20_years_ago VARCHAR(100), |
| cause_of_reduction TEXT, |
| known_abundance_area VARCHAR(255), |
| can_be_home_grown VARCHAR(150), |
| replacement_plant_name VARCHAR(200), |
| replacement_plant_id INT UNSIGNED, |
| FOREIGN KEY (entry_id) REFERENCES survey_entries(entry_id) ON DELETE CASCADE, |
| FOREIGN KEY (replacement_plant_id) REFERENCES plants(plant_id) ON DELETE SET NULL |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
| CREATE TABLE entry_media ( |
| media_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, |
| entry_id INT UNSIGNED NOT NULL, |
| media_type ENUM('Photo','Audio') NOT NULL, |
| file_url VARCHAR(500) NOT NULL, |
| FOREIGN KEY (entry_id) REFERENCES survey_entries(entry_id) ON DELETE CASCADE, |
| KEY idx_media_entry (entry_id) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
| SET FOREIGN_KEY_CHECKS = 1; |
|
|
| ALTER TABLE ritual_details |
| MODIFY COLUMN is_sacred_ritual VARCHAR(255), |
| MODIFY COLUMN who_can_collect VARCHAR(255), |
| MODIFY COLUMN occasion VARCHAR(255); |
|
|
| ALTER TABLE survey_entries |
| MODIFY COLUMN dosage_unit VARCHAR(255), |
| MODIFY COLUMN used_alone_or_combined VARCHAR(255), |
| MODIFY COLUMN is_culturally_sensitive VARCHAR(255); |
