4. Database / ER Diagram_Architecture

GiveHope Platform: Database & ER Diagram Documentation

4. Database / ER Diagram

Documents the full data model across 16 tables with every column, data type, and constraint (PK, FK, UK) defined. It covers core entities like Donors and Campaigns, pivot tables for Causes, Objectives and Add-ons, and operational tables for Transactions, Schedules, Abandonments and Admin Settings. This diagram answers the question: how is the data structured and how do tables relate to each other?

Diagram: Database ER Diagram.png

This fourth diagram is an Entity Relationship Diagram (ERD). If the previous diagrams were the "blueprints" and "circuitry," this is the filing cabinet of the application. It defines exactly how data is structured, stored, and linked within the database.

1. Core Identity & User Management

At the top right (pink/red section), we have the Users and Profiles.

• User Table: Stores core credentials (email, hashed password, google_id).

• Donors Table: Extends the user with specific donation-related metadata.

• Roles/Permissions: Likely manages the distinction between a standard Donor and a Platform Administrator.

2. The Campaign Engine

The large central table (teal) is the Campaign entity. This is the "heavyweight" table of the system.

• Campaign Data: Stores titles, descriptions, goal amounts, and end dates.

• Relationships: It links to Categories (for filtering) and Causes (the specific mission the money serves).

• Campaign Objectives: Smaller tables linked to campaigns that define specific milestones (e.g., "Build 5 wells").

3. The Financial Transaction Heart

The bottom right (dark blue section) contains the Transactions and Donations tables.

• Donations: Records individual "gifts." Notice the foreign keys linking a donation to a User, a Campaign, and a Payment Method.

• Schedules: This table tracks recurring gifts. It stores the frequency (monthly, weekly) and the next billing date.

• Transaction Status: A separate lookup table or field likely tracks whether a payment is Pending, Success, Failed, or Refunded.

4. Specific Islamic Finance Entities

Consistent with the previous diagrams, there are specialized tables for:

• Zakat/Sadaqah Type: A table or column that categorizes the funds to ensure they are routed according to religious requirements.

• Split Payments: Logic captured here ensures that if a donor adds a "Platform Tip" or splits a gift between two causes, the database can track those as separate line items under one transaction ID.

Key Database Relationships

The lines between tables represent how data "talks" to other data:

• One-to-Many ($1:N$): One Donor can have many Donations. One Campaign can have many Donations.

• Many-to-Many ($M:N$): A Campaign might belong to multiple Categories. This is usually handled by a "junction table" (the smaller boxes between large tables).

• Nullable Relationships: A donation might have a user_id (Registered Donor) or it might be null (Guest Donor), supporting the "Guest Checkout" feature identified in the first diagram.

Final Summary of the Documentation Stack

You now have a complete architectural narrative:

1. Context: The platform's place in the world.

2. High-Level: The cloud services and infrastructure.

3. Component: The internal code logic and controllers.

4. ERD: The permanent data structure.

🏗️ ERD Phase Evaluation: The "Data Integrity" Audit

This Entity Relationship Diagram (ERD) evaluation is the "truth" of your system. In software, code can be rewritten easily, but a poorly designed database is a nightmare to change once you have thousands of real donations stored in it.

The use of Junction Tables for Many-to-Many ($M:N$) relationships proves your developers are building for scale, not just a quick fix. To ensure the database is bulletproof, consider these points:

1. The "Guest Donor" Nullability

• The Strength: You correctly identified that user_id must be Nullable.

• The "Perfect Build" Check: Even if a user is a guest, the Donations table must still capture an email_address and full_name as string fields.

• Developer Note: "Ensure that for Guest Donors, we don't just leave the identity blank. Use a 'Shadow Profile' or store the PII (Personally Identifiable Information) directly on the Donations row so we can issue tax receipts later."

2. Financial Precision (The "Decimal" Rule)

• The Risk: Many developers mistakenly use Float or Double for money. This causes "rounding errors" (e.g., $\$10.00$ becomes $\$9.99999$).

• Developer Note: "All currency columns (Goal, Amount, Tips, Zakat) must use the DECIMAL(19,4) or NUMERIC data type. Never use floating-point math for Zakat."

3. The "Soft Delete" Pattern

• The Requirement: If an Admin accidentally "deletes" a Campaign that has already raised $\$5,000$, you cannot actually delete that row because the Transaction history would break.

• Developer Note: "Implement deleted_at timestamps (Soft Deletes) on all core tables. We should never physically DELETE a record that has a Foreign Key relationship to a financial transaction."

📊 Visualizing the Data Flow

🛠️ Critical Data Constraints Table

Ask your developers to confirm these "Database Rules" are in place to prevent data corruption: