Rebuilt a fragmented landscaping company data environment into a clean, normalized SQL Server system. Standardized entities, enforced relational integrity, and built a full query library to uncover client trends, service patterns, and project profitability.
Project overview
The original data environment mixed spreadsheets, ad hoc tables, and partially designed schemas. Client records appeared in several places, bid amounts did not tie cleanly to labor and material costs, and basic questions about profitability required manual reconciliation.
Key deliverables
Phase 1
Requirements & audit
Reviewed legacy spreadsheets and tables, identified duplicated data, and documented core business questions.
Phase 2
Conceptual modeling
Designed an ERD capturing clients, projects, bids, labor, materials, and suppliers with correct cardinalities.
Phase 3
Schema build
Implemented the logical model in SQL Server with tables, keys, indexes, and constraints.
Phase 4
Data load & validation
Migrated sample data, reconciled totals across modules, and verified referential integrity.
Phase 5
Analytics & reporting
Developed a library of insight queries and outlined how they feed BI dashboards.
The first step was to convert scattered operational data into a single consistent model. This included cleaning naming conventions, aligning data types, and deciding which entities belonged in which tables.
Consistency and normalization
Consolidated legacy client, project, and service data into a canonical model. Applied clear naming standards and enforced data types so every field has one meaning and one home.
Keys and constraints
Replaced duplicated records and repeated columns with relational joins. Implemented primary keys, foreign keys, and unique constraints to maintain integrity and improve performance.
SQL analytics
Designed a set of tables and relationships specifically to support revenue, cost, and workload analysis so the database doubles as a reporting asset.
Using ERDPlus, the full lifecycle of a landscaping project was modeled, from first contact and bid through labor, materials, and production planning. The ERD became the contract between business rules and technical implementation.
Normalized ERD for the landscape services database, showing clients, bids, labor, materials, tools, suppliers, and production planning.
The conceptual model was implemented in SQL Server Management Studio as a scripted deployment. This ensures the database can be rebuilt from source control at any time.
The script initializes the database, builds core tables, and wires up keys and constraints. From there, sample or production data can be loaded for analytics and reporting.
-- Create database
IF DB_ID('NBDDB_CTF') IS NOT NULL
DROP DATABASE NBDDB_CTF;
GO
CREATE DATABASE NBDDB_CTF;
GO
USE NBDDB_CTF;
GO
-- Client table
CREATE TABLE Client (
ClientID INT IDENTITY(1,1) PRIMARY KEY,
ClientName VARCHAR(100) NOT NULL,
Phone VARCHAR(20),
Email VARCHAR(100),
City VARCHAR(50),
StateCode CHAR(2)
);
-- Project table
CREATE TABLE Project (
ProjectID INT IDENTITY(1,1) PRIMARY KEY,
ClientID INT NOT NULL,
ProjectName VARCHAR(120) NOT NULL,
StartDate DATE,
EndDate DATE,
Status VARCHAR(30),
CONSTRAINT FK_Project_Client
FOREIGN KEY (ClientID) REFERENCES Client(ClientID)
);
Schema scripts enforce referential integrity and provide a clean baseline for loading data.
On top of the normalized schema, more than thirty queries were developed that support estimators, managers, and operations. These can be used in SSMS or wired into BI dashboards.
-- Popular services by volume
SELECT s.ServiceName,
COUNT(*) AS ProjectCount
FROM Project p
JOIN ProjectService ps ON p.ProjectID = ps.ProjectID
JOIN Service s ON ps.ServiceID = s.ServiceID
GROUP BY s.ServiceName
ORDER BY ProjectCount DESC;
-- Client profitability summary
SELECT c.ClientName,
p.ProjectName,
SUM(jm.Quantity * m.UnitCost) AS MaterialCost,
SUM(l.HoursWorked * l.HourlyRate) AS LaborCost,
p.BidAmount - (
SUM(jm.Quantity * m.UnitCost) +
SUM(l.HoursWorked * l.HourlyRate)
) AS EstimatedProfit
FROM Project p
JOIN Client c ON p.ClientID = c.ClientID
JOIN JobMaterials jm ON p.ProjectID = jm.ProjectID
JOIN Material m ON jm.MaterialID = m.MaterialID
JOIN LaborAssignment l ON p.ProjectID = l.ProjectID
GROUP BY c.ClientName, p.ProjectName, p.BidAmount
ORDER BY EstimatedProfit DESC;
These patterns can power dashboards that guide pricing, staffing, and which services to promote.
The final phase focused on validating the new schema, proving that constraints behave correctly, and outlining how the system can grow into a full production analytics platform.
Database revitalization transformed fragmented operational data into a strategic asset supporting data-driven decision making across the organization.
A normalized schema dramatically reduces redundancy and confusion in operational reporting.
Modeling costs at the material and labor level unlocks accurate project profitability analysis.
Entity relationships that mirror real workflows make it easier to extend the database later.
Scripted deployments turn the database into an asset that can be versioned and reused.
Objective: Enable daily operational insights for project managers and estimators.
Objective: Understand client value and optimize relationship management.
Objective: Support long-term strategic decisions with profitability insights.
This project transformed a fragmented landscaping database into a clean, normalized SQL Server implementation. Grounded in a comprehensive ERD and backed by constraints and analytics, the system now produces trustworthy insight into customers, services, and profitability. The revitalized schema gives the business a scalable foundation for future dashboards, automation, and decision support tools.