Database Schema

HydroLink Plus Database Schema

Table of Contents

1. Introduction
2. Database Overview
3. Schema Design
   • Key Tables
4. Table Definitions
   • Users Table
   • Meters Table
   • Water Usage Table
   • Alerts Table
   • Billing Table
5. Entity Relationship Diagram (ERD)
6. Sample Queries
7. Conclusion

---

Introduction

The HydroLink Plus database schema is designed to store and manage data efficiently for both consumers and water authorities. The schema supports user profiles, meter management, water usage tracking, alert generation, and billing automation.

---

Database Overview

• Type: Relational Database
• Engine: MySQL
• Key Features:
  • Optimized for storing real-time water usage data.
  • Scalable to support multiple meters and users.
  • Ensures data integrity with primary and foreign key constraints.

---

Schema Design

The database is structured to handle the following:

1. User Management: Tracks consumer and authority profiles.
2. Meter Management: Logs meter details, statuses, and firmware versions.
3. Usage Tracking: Records real-time and historical water consumption.
4. Alerts and Notifications: Stores alerts for anomalies like leaks or tampering.
5. Billing: Automates billing based on usage data.

---

Key Tables

Table Name	Purpose
users	Stores consumer and authority data.
meters	Tracks meter configurations and statuses.
water_usage	Logs water consumption details.
alerts	Records alerts and notifications.
billing	Manages billing and payment records.

---

Table Definitions

Users Table

Column Name	Data Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique user ID.
name	VARCHAR(100)	NOT NULL	User's full name.
email	VARCHAR(100)	UNIQUE, NOT NULL	User's email address.
password	VARCHAR(255)	NOT NULL	Encrypted password.
role	ENUM('consumer', 'authority')	DEFAULT 'consumer'	User role type.
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Account creation timestamp.

---

Meters Table

Column Name	Data Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique meter ID.
serial_number	VARCHAR(50)	UNIQUE, NOT NULL	Serial number of the meter.
user_id	INT	FOREIGN KEY REFERENCES users(id)	Linked user.
location	VARCHAR(255)	NOT NULL	Physical location of the meter.
status	ENUM('active', 'inactive', 'maintenance')	DEFAULT 'active'	Operational status.
firmware_version	VARCHAR(20)	NOT NULL	Current firmware version.
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Meter registration date.

---

Water Usage Table

Column Name	Data Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique record ID.
meter_id	INT	FOREIGN KEY REFERENCES meters(id)	Linked meter ID.
timestamp	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Time of data recording.
flow_rate	FLOAT	NOT NULL	Current flow rate in liters/min.
total_usage	FLOAT	NOT NULL	Cumulative water usage in liters.

---

Alerts Table

Column Name	Data Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique alert ID.
meter_id	INT	FOREIGN KEY REFERENCES meters(id)	Linked meter ID.
alert_type	VARCHAR(50)	NOT NULL	Type of alert (e.g., "leak", "tampering").
details	TEXT	NULLABLE	Additional alert information.
timestamp	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Time of alert generation.

---

Billing Table

Column Name	Data Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique billing ID.
user_id	INT	FOREIGN KEY REFERENCES users(id)	Linked user ID.
amount_due	FLOAT	NOT NULL	Total bill amount.
due_date	DATE	NOT NULL	Payment due date.
status	ENUM('paid', 'unpaid')	DEFAULT 'unpaid'	Payment status.
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Billing record creation date.

---

Entity Relationship Diagram (ERD)

erDiagram
    USERS {
        INT id
        VARCHAR name
        VARCHAR email
        VARCHAR password
        ENUM role
        TIMESTAMP created_at
    }
    METERS {
        INT id
        VARCHAR serial_number
        INT user_id
        VARCHAR location
        ENUM status
        VARCHAR firmware_version
        TIMESTAMP created_at
    }
    WATER_USAGE {
        INT id
        INT meter_id
        TIMESTAMP timestamp
        FLOAT flow_rate
        FLOAT total_usage
    }
    ALERTS {
        INT id
        INT meter_id
        VARCHAR alert_type
        TEXT details
        TIMESTAMP timestamp
    }
    BILLING {
        INT id
        INT user_id
        FLOAT amount_due
        DATE due_date
        ENUM status
        TIMESTAMP created_at
    }
    USERS ||--o{ METERS : "owns"
    METERS ||--o{ WATER_USAGE : "records"
    METERS ||--o{ ALERTS : "generates"
    USERS ||--o{ BILLING : "receives"

Loading

---

Sample Queries

1. Get Water Usage for a Specific Meter

SELECT timestamp, flow_rate, total_usage
FROM water_usage
WHERE meter_id = 1
ORDER BY timestamp DESC;

2. Fetch Unpaid Bills for a User

SELECT amount_due, due_date
FROM billing
WHERE user_id = 2 AND status = 'unpaid';

3. List Active Meters with Alerts

SELECT m.serial_number, a.alert_type, a.details
FROM meters m
JOIN alerts a ON m.id = a.meter_id
WHERE m.status = 'active';