Smart Waste ManagementPHP/MySQL waste service platform
A subscription-gated waste service platform — user and admin roles, a 9-table relational schema, and the full workflow from pickup request to leaderboard, built for CSE327 at NSU.
What it had to solve.
The brief for CSE327 was to design and build a small but complete service web application end-to-end — schema, server, and UI. The interesting design question was where to put the boundary between user features and admin operations: how to keep the same data model honest across both surfaces, gate subscription-gated features behind an active plan, and keep the schema simple enough to reason about without losing the relational integrity that the workflows actually need.
How the pieces fit together.
A procedural PHP monolith on Apache, fronted by Bootstrap-rendered HTML and backed by a 9-table MySQL schema. Two parallel surfaces — a player-facing user app and an admin console — share the same models. Feature gating runs at the request boundary based on subscription state.
The surfaces users and admins actually touched.
User workflows
Subscription, waste reporting, sorting guidance, special pickups, notifications, feedback, and a points leaderboard — gated behind an active subscription.
- Three subscription tiers (Basic / Standard / Premium) with expiry checks
- Self-reported waste dumps generate points at 10× weight (kg)
- Special pickup requests for bulk or hazardous items
- Sorting guide for the four waste categories
Admin operations
A separate admin login fronts management views over the same tables — schedule collections, dispatch notifications, review feedback, and audit the leaderboard.
- Schedule waste collection by waste type, date, and location
- Send location-targeted notifications to all matching users
- Pickup queue management with status updates
- Feedback review with delete actions
Relational schema
Nine tables covering accounts, subscriptions, waste types, collections, dumps, notifications, pickups, feedback, and leaderboard — with foreign keys + cascade rules that match the workflows.
- Users + Admin as separate authentication surfaces
- Subscription ledger with start/end dates and amount
- Notifications targeted by user + waste type + collection date
- Append-only waste_dumps feeding leaderboard projections
Choices that shaped the build.
Subscription gate at the boundary, not inside features
Each user feature checks subscription state at entry rather than every feature reimplementing access logic. Expired subscriptions get a renewal page; missing subscriptions get a purchase page; active subscriptions reach the feature. One place to read, one place to change.
Same models, two surfaces
The user app and the admin console read and write against the same nine tables. No duplicate model — admin sees the same pickup request the user submitted; the leaderboard is projected from the same waste_dumps the user wrote into.
Points as a projection, not a stored balance
The leaderboard query sums weight × 10 on read rather than maintaining a separately incremented points balance. Slower on huge datasets, but completely auditable and immune to drift between the source rows and the displayed total.
Notifications as targeted database records
Admin dispatches a notification by location + waste type; the system writes one row per matching user. There's no real-time push — the user sees the notification next time they visit the page. Simpler than implementing a transport, accurate for the academic scope.