An enterprise-grade MS SQL Server database system designed to manage the full business lifecycle of a furniture manufacturing company. This project integrates e-commerce sales, complex Bill of Materials (BOM) manufacturing, and automated supply chain logistics.
The system utilizes 24 tables structured into four logical zones to ensure maximum data integrity and performance:
- Dictionaries: Standardized lookups for Categories, Materials, System Statuses, Cities, and Occupations.
- Core Entities: Management of Products, Parts, Customers, Suppliers, and Employees.
- Extensions & Warehouse: Specialized 1:1 tables for Individual vs. Corporate clients and real-time stock management for both components and finished goods.
- Transactions & History: The engine handling Orders, Production tasks, and a full audit trail for every status transition.
- BOM Management: Defines complex product structures via recursive-style recipes in
ProductRecipes. - Resource Planning: Functions to estimate production time and verify employee availability (
f_IsEmployeeAvailable) before assigning tasks. - Automated Stock Alerts: Triggers monitor inventory levels and automatically log "Low Stock" alerts to
SystemLogswhen quantities fall belowResupplyThreshold. - Supply Workflow: Procedures to order parts from suppliers and receive deliveries with automated inventory updates using
MERGElogic.
- JSON-Powered Orders: Uses
sp_PlaceClientOrderto parse nested product lists in JSON format, allowing for high-performance bulk order processing. - Status History Tracking: Triggers automatically archive the history of every order, capturing
BeginningDateandEndDatefor every phase of the lifecycle. - Data Protection:
INSTEAD OFtriggers prevent the modification or deletion of completed orders to maintain immutable historical financial records.
The system includes over 20 specialized views for management reporting:
- Financials: Unit cost calculation (Materials + Labor) and periodic cost reports (Weekly/Monthly/Quarterly).
- Sales Analytics: Detailed customer summaries, revenue tracking with discounts, and time-based sales trends.
- Operational Control: Planned production queues, upcoming deliveries, and inventory health dashboards.
The database is populated with realistic, high-volume data generated via a Python script using the Faker library:
- Volume: ~100 sales orders, 100 supply orders, and 100 production tasks.
- Consistency: The generator ensures logical date sequences (Order Date < Completion Date) and valid NIP/Phone formats.
The project includes a robust transactional unit testing framework. Each module (Sales, Production, Supply) is verified through "Happy Path" and "Error Handling" scenarios:
- Sales Tests: Verify stock reduction, order valuation, and edit-lock triggers.
- Production Tests: Validate material cost calculators and employee assignment logic.
- Safety: All tests run within a transaction that is rolled back at the end, keeping the production environment clean.
Important
For detailed step-by-step instructions on environment setup, script execution order, and data generation, please refer to the INSTALL.md file.
- Schema: Execute
DDL_Initialize_DB.sqlto build the tables. - Logic: Run scripts for Views, Functions, Procedures, and Triggers.
- Seed: Populate the database using
DML_Seed_Data.sql. - Verify: Run the built-in Test Suites to confirm the system is fully operational.
- Wiktor Trybus along with two colleagues
Developed as a final project for the "Databases" (Podstawy Baz Danych) course at AGH University of Science and Technology (Faculty of Computer Science, 2025/2026).