Database Model - green-ecolution/backend GitHub Wiki

The database comprises seven main tables and three relationship tables that map the relationships between the main tables. The tables and their relationships are as follows:

• regions: represents geographic regions
• trees: represents individual trees
• tree_clusters: represents clusters of trees
• vehicles: represents vehicles
• users: represents user information
• watering_plans: defines watering plans
• sensors: represents the sensors themselves
• sensor_data: contains all data sent by the sensors over time

Relationships

• 1:1 | tree:sensor: sensor_id as foreign key in tree
• 1:N | tree:tree_cluster: tree_cluster_id as foreign key in tree
• 1:N | tree_cluster:region: region_id as foreign key in tree_cluster
• 1:N | sensor:sensor_data: sensor_id as foreign key in sensor_data
• N:N: watering_plans has N:N relationships with vehicles, users, and tree_clusters. These relationships are mapped through separate relationship tables

Pivot tables

• watering_plans_vehicles: links watering plans with vehicles
• watering_plans_users: links watering plans with users
• watering_plans_tree_clusters: links watering plans with tree clusters

The following diagram reflects the relationships between the tables and does not indicate the specific data fields present within each table. Certain details have been omitted as the fields within each table frequently change due to ongoing refactoring and development.