Skip to content

🗄️ Storage Layer Documentation (Stage 1)

📌 Database Overview

The data persistence layer for this project is built using a MySQL Relational Database. The schema, named storage_data_1, is designed to handle corporate, employee, and financial data with high integrity and relational consistency.

🏗️ Schema Architecture

The database follows a normalized structure to minimize redundancy. It consists of four primary tables with established One-to-Many relationships: - Companies ➔ Employees - Companies ➔ Events - Companies ➔ Financials

🗂️ Data Dictionary

1. Companies (companies_data_1)

Stores core organizational data.

Column Type Constraints Description
Company_id INT PK, AUTO_INCREMENT Unique identifier for each company.
Company_name VARCHAR UNIQUE, NOT NULL Registered name of the entity.
Sector VARCHAR - Industry classification.
No_of_employees INT CHECK (>= 0) Current staff count.
Annual_Revenue FLOAT CHECK (>= 0) Total yearly earnings.

2. Employees (employees_data_1)

Tracks personnel linked to specific companies.

Column Type Constraints Description
Employee_id INT PK, AUTO_INCREMENT Unique identifier for staff.
Company_id INT FK (Companies) Link to the employer.
Age INT CHECK (18-65) Valid working age range.
Status VARCHAR CHECK (Active/Resigned/Laid-off) Current employment state.

3. Events (event_data_1)

Records corporate milestones and impacts.

Column Type Constraints Description
Type_of_Event VARCHAR CHECK (Layoff, Expansion, etc.) Standardized event categories.
Impact_Revenue FLOAT CHECK (>= 0) Financial effect of the event.

4. Financials (financial_data_1)

Quarterly performance tracking.

Column Type Constraints Description
Quarter INT CHECK (1-4) Fiscal quarter identifier.
Profit_margin FLOAT CHECK (-1 to 1) Performance ratio.

🛡️ Data Integrity & Performance

  • Referential Integrity: Uses ON DELETE CASCADE on all foreign keys to ensure that deleting a company automatically cleans up related employees, events, and financial records.
  • Validation: Implemented CHECK constraints to prevent invalid data (e.g., negative salaries or age outside working limits).
  • Indexing: A composite index idx_company_period is added to the Financials table to optimize time-series queries for specific companies.

🛠️ Setup Instructions

  1. Ensure MySQL Server is running locally.
  2. Run the provided Python script to initialize the database: ```bash python storage_setup.py