Track opportunities without turning predictions into facts
A placement cell coordinates students, companies, drives, applications and selection stages. The database must answer: Who is eligible now? Who applied? How many reached each stage? Who was selected? It should not label a student “employable” or infer merit from one company’s outcome.
Master facts
Student academic status and company criteria.
Event facts
Application creation and stage transitions.
Reports
Eligibility lists, drive funnels and auditable histories.
Separate policy from event history
| Rule | Implementation | Reason |
|---|---|---|
| CGPA is 0–10; backlogs cannot be negative | CHECK | Reject impossible states |
| One company may conduct many drives | drive.company_id FK | Do not duplicate company policy |
| One student applies once per drive | UNIQUE(drive_id, student_id) | Prevent double counts |
| Stage belongs to an allowed lifecycle vocabulary | CHECK(stage IN …) | Prevent spelling variants |
| Every stage change remains explainable | Audit trigger/event table | Preserve accountability |
The demonstration stores company minimum CGPA and backlog allowance. Real criteria may be drive-specific, include graduating batch and eligible branches, and change over time. In that case, snapshot criteria on the drive so later audits reproduce the rule that actually applied.
Application is the bridge between student and drive
stage_event forms a one-to-many history beneath application. Current stage remains on application for fast operational views; events preserve transitions. This deliberate duplication needs one write path—a trigger in the teaching script—so current and historical state do not diverge.
Why eligibility is derived
Storing an “eligible” Boolean becomes stale when CGPA, backlog count or criteria change. The eligibility query compares current student facts with current company rules. For legal/audit reproduction, store the criteria snapshot and eligibility decision used when invitations were issued.
Stage changes are events, not silent overwrites
- Confirm the application exists and its current stage permits the transition.
- Begin a transaction.
- Update exactly the expected row, optionally using
WHERE stage='Technical'as optimistic protection. - The trigger appends the new state to
stage_event. - Verify one row changed, then commit; otherwise roll back.
The sample changes Technical to HR only when the current value is still Technical. A production workflow should use a transition table or service rule to reject impossible jumps such as Rejected → Selected without authorized correction.
Conditional aggregation converts events into counts
The eligibility report cross joins students and companies, then filters pairs meeting both criteria. The drive funnel groups applications at drive grain. Expressions such as SUM(stage='Selected') count rows satisfying a condition in SQLite.
| Measure | Question | Caution |
|---|---|---|
| Applicants | How many unique applications? | Enforce uniqueness first |
| Reached technical | How many progressed to/through technical? | Current stage assumes ordered lifecycle |
| Selected | How many final selections? | Define withdrawn offers separately |
| Conversion | selected/applicants × 100 | Avoid division by zero |
If candidates can skip stages or re-enter a stage, calculate funnel metrics from stage events with distinct application IDs rather than inferring history from current stage.
Executable SQLite schema, trigger and reports
Loading source…Run with sqlite3 :memory: < placement-management.sql. The deterministic seed data demonstrates multiple companies, eligibility combinations, a funnel and an audited stage update.
Follow one safe status change
- Read expected state.
- Open transaction.
- Use guarded update.
- Validate new value.
- Write audit event.
- Commit both facts.
- Verify history.
Press Next to begin.
Test constraints, workflow and reports
Eligibility boundaries
Duplicate application
Invalid stage
Audit completeness
Guarded concurrency
Funnel reconciliation
Placement data deserves least-privilege access
The drive-stage index supports funnel filtering/grouping; the eligibility index may help range filtering, but low-cardinality backlog values alone are weak indexes. Verify real workloads with query plans. Separate student self-service, recruiter access and coordinator privileges. Encrypt sensitive data, avoid exposing personal contact details in broad exports, log corrections and set retention periods.
Reports should show definitions and timestamps. A “selected” count without drive scope, offer status or as-of date can mislead. Keep eligibility rules explainable and provide a correction route for inaccurate CGPA/backlog data.
Reason about derived state
What is the safest current eligibility source?
What prevents funnel double counting at entry?
Extensions
- Add branches, graduating batches and drive-specific eligibility criteria.
- Model offers, joining status and multiple-offer policy separately.
- Implement allowed stage transitions and authorized correction reasons.
- Create branch-wise conversion views without exposing student-level data.
- Use window functions to rank packages within a graduating batch.
Explain operational correctness
Why keep a stage-event table?
Current state answers “where now”; events answer how and when it changed, supporting audits and richer funnel analysis.
Trigger advantage and risk?
It applies across applications, but hidden behavior can surprise developers. Document, test and keep triggers small.
Why a transaction?
The business action must not leave current stage changed without the corresponding audit event.
What is optimistic concurrency?
An update includes the previously observed value/version. Zero affected rows signals another change instead of overwriting it.
How do you prevent biased reporting?
Use declared definitions and populations, avoid protected features in screening, audit data quality and publish limitations.
Operational data must remain explainable
The strongest design derives eligibility, makes application grain unique, protects stage updates transactionally and preserves history. Reports become trustworthy because their definitions and data lineage are visible—not because the dashboard looks precise.