6.5. Proposed Database Schema - Purplewells/WattWise GitHub Wiki

1. Service Providers

CREATE TABLE ServiceProvider (
    ServiceProviderID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    ContactEmail VARCHAR(255),
    SupportPhone VARCHAR(50),
    Address TEXT,
    Website VARCHAR(255),
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    ModifiedDate DATETIME
);

2. Users

CREATE TABLE User (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    Username VARCHAR(100) NOT NULL UNIQUE,
    Email VARCHAR(255) NOT NULL UNIQUE,
    PasswordHash VARCHAR(255) NOT NULL,
    Role ENUM('Admin', 'Engineer', 'Customer') NOT NULL,
    AvatarUrl VARCHAR(255),
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    ModifiedDate DATETIME
);

3. Smart Meters

CREATE TABLE Meter (
    MeterID INT AUTO_INCREMENT PRIMARY KEY,
    CustomerID INT,
    ServiceProviderID INT,
    SerialNumber VARCHAR(100) UNIQUE NOT NULL,
    Location VARCHAR(255),
    InstallationDate DATE,
    FirmwareVersion VARCHAR(50),
    BatteryLevel DECIMAL(5,2), -- %
    Status ENUM('Active', 'Inactive', 'Faulty') DEFAULT 'Active',
    LastCommunication DATETIME,
    CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    ModifiedDate DATETIME,
    FOREIGN KEY (CustomerID) REFERENCES User(UserID),
    FOREIGN KEY (ServiceProviderID) REFERENCES ServiceProvider(ServiceProviderID)
);

4. Credit Top-ups

CREATE TABLE CreditTopUp (
    TopUpID INT AUTO_INCREMENT PRIMARY KEY,
    MeterID INT,
    UserID INT,
    Amount DECIMAL(10,2) NOT NULL,
    OldCredit DECIMAL(10,2),
    NewCredit DECIMAL(10,2),
    Method ENUM('Card', 'Cash', 'MobileMoney', 'AdminAdjust') DEFAULT 'Card',
    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    ModifiedDate DATETIME,
    FOREIGN KEY (MeterID) REFERENCES Meter(MeterID),
    FOREIGN KEY (UserID) REFERENCES User(UserID)
);

5. Usage Readings

CREATE TABLE UsageReading (
    ReadingID INT AUTO_INCREMENT PRIMARY KEY,
    MeterID INT,
    ReadingTimestamp DATETIME NOT NULL,
    ConsumptionKWh DECIMAL(10,3),
    Voltage DECIMAL(6,2),
    Current DECIMAL(6,2),
    PowerFactor DECIMAL(4,2),
    Frequency DECIMAL(5,2),
    CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    ModifiedDate DATETIME,
    FOREIGN KEY (MeterID) REFERENCES Meter(MeterID)
);

6. Alerts

CREATE TABLE Alert (
    AlertID INT AUTO_INCREMENT PRIMARY KEY,
    MeterID INT,
    AlertType ENUM('LowCredit', 'LowBattery', 'CommunicationLoss', 'Overload', 'Manual'),
    Severity ENUM('Info', 'Warning', 'Critical'),
    Message TEXT,
    IsAcknowledged BOOLEAN DEFAULT FALSE,
    CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    ModifiedDate DATETIME,
    FOREIGN KEY (MeterID) REFERENCES Meter(MeterID)
);

7. User Preferences

CREATE TABLE UserPreference (
    PreferenceID INT AUTO_INCREMENT PRIMARY KEY,
    UserID INT,
    ReceiveEmailAlerts BOOLEAN DEFAULT TRUE,
    ReceiveSMSAlerts BOOLEAN DEFAULT FALSE,
    ReceiveWebPush BOOLEAN DEFAULT TRUE,
    LowCreditThreshold DECIMAL(10,2) DEFAULT 5.00,
    LowBatteryThreshold DECIMAL(5,2) DEFAULT 20.00,
    CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    ModifiedDate DATETIME,
    FOREIGN KEY (UserID) REFERENCES User(UserID)
);

8. Audit Log

CREATE TABLE AuditLog (
    AuditLogID INT AUTO_INCREMENT PRIMARY KEY,
    UserID INT,
    Action ENUM('CREATE', 'UPDATE', 'DELETE', 'LOGIN', 'TOPUP'),
    Entity VARCHAR(100),
    EntityID INT,
    Description TEXT,
    OldValue TEXT,
    NewValue TEXT,
    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    ModifiedDate DATETIME,
    FOREIGN KEY (UserID) REFERENCES User(UserID)
);

9. Scheduled Reports

CREATE TABLE ScheduledReport (
    ReportID INT AUTO_INCREMENT PRIMARY KEY,
    ReportType ENUM('UsageSummary', 'TopUpSummary', 'Alerts', 'AuditTrail'),
    Frequency ENUM('Daily', 'Weekly', 'Monthly'),
    LastGenerated DATETIME,
    NextScheduled DATETIME,
    RecipientEmails TEXT,
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    ModifiedDate DATETIME
);

10. Roles

CREATE TABLE Role (
    RoleID INT AUTO_INCREMENT PRIMARY KEY,
    RoleName VARCHAR(50) NOT NULL UNIQUE, -- e.g. 'Admin', 'Engineer', 'Customer'
    Description TEXT,
    CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    ModifiedDate DATETIME
);

## 11. User
CREATE TABLE User (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    Username VARCHAR(100) NOT NULL UNIQUE,
    Email VARCHAR(255) NOT NULL UNIQUE,
    PasswordHash VARCHAR(255) NOT NULL,
    RoleID INT NOT NULL,
    AvatarUrl VARCHAR(255),
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    ModifiedDate DATETIME,
    FOREIGN KEY (RoleID) REFERENCES Role(RoleID)
);