Design Data Model for Building Management System

Problem Statement

Design a building management system that tracks employee locations and activities in real time across a corporate campus. The system ingests events from badge readers at entry points, motion sensors in restrooms and common areas, and parking gate systems. It must provide live occupancy counts per zone, enable individual employee location lookups, and report daily parking utilization metrics.

The system handles 50,000 employees across multiple buildings, with peak traffic generating thousands of badge swipes per minute during morning arrival. Facilities dashboards must update within seconds of any zone change. Historical data powers analytics for space planning and energy management. The core technical challenge is maintaining accurate counters under high concurrent updates while serving low-latency reads and handling edge cases like missed badge-outs, sensor failures, and duplicate events.

Key Requirements

Functional

• Live zone occupancy -- display current headcount for each building, floor, restroom, and common area with sub-5-second freshness
• Individual location lookup -- show where a specific employee is right now and their movement history for the past 24 hours, subject to access controls
• Parking utilization -- track how many employees parked today, current available spots, and peak occupancy over time
• Threshold alerts -- notify facilities managers when any zone exceeds its capacity limit or shows unusual patterns

Non-Functional

• Scalability -- handle 10,000 events per minute during peak hours across 50,000 employees and hundreds of sensor-equipped zones
• Reliability -- tolerate sensor failures, network partitions, and individual component outages without losing occupancy accuracy
• Latency -- reflect badge swipes in live dashboards within 2 seconds; serve current occupancy queries in under 100 milliseconds
• Consistency -- guarantee accurate zone counts despite concurrent entries and exits, out-of-order events, and sensor retries

What Interviewers Focus On

Based on real interview experiences, these are the areas interviewers probe most deeply:

1. Data Model and Entity Relationships

Interviewers want to see how you structure the domain entities and their relationships. The model must support both real-time state queries and historical analytics without forcing expensive joins or scans.

Hints to consider:

• Define core entities: Building, Floor, Zone (restroom, common area, office), Employee, Sensor, and ParkingSpot
• Separate static configuration (building layouts, zone capacities, sensor assignments) from dynamic state (current occupancy, employee locations)
• Model events as immutable records with timestamp, employee ID, zone ID, direction (entry/exit), and a unique event ID for deduplication
• Store current state (employee-to-zone mapping, zone counters) separately from the event log to serve fast queries

2. Real-Time Count Maintenance Under Contention

Main lobby doors and popular restrooms become hot spots where hundreds of events per minute update the same counter. Naive locking will serialize updates and create bottlenecks.

Hints to consider:

• Use atomic increment and decrement operations in an in-memory store like Redis for zone counters
• Consider counter sharding where you split a zone counter across multiple keys and aggregate on read
• Partition the event stream by employee ID to guarantee ordering for a single person's movements without cross-employee locking
• Ensure idempotency by checking the unique event ID against a recent-event cache before applying updates

3. Event Stream Processing and Deduplication

Badge readers may retry network requests, producing duplicate events. Out-of-order delivery happens when events route through different network paths. Without proper handling, counters drift and ghost occupants accumulate.

Hints to consider:

• Assign unique event IDs at the sensor level and use them as deduplication keys in a rolling time window
• Design a time-window strategy for accepting late events while eventually closing windows to finalize counts
• Handle paired events (entry-exit) where one might arrive much later than the other
• Use Kafka partitioned by employee ID for ordered, replayable event ingestion

4. Handling Stale Presence and Ghost Occupants

Employees occasionally forget to badge out, and sensors malfunction. Without correction mechanisms, occupancy counts drift upward over time as ghost occupants accumulate.

Hints to consider:

• Implement maximum occupancy duration logic that auto-exits people after a configurable threshold (e.g., 18 hours)
• Use overnight batch reconciliation that resets building occupancy to zero after closing hours and reconciles against next-day entries
• Design anomaly detection that flags when a person appears to enter without exiting or exit without entering
• Consider paired turnstile sensors where entry and exit events are physically coupled

Suggested Approach

Step 1: Clarify Requirements

Confirm how many buildings and zones exist, typical and peak event rates, and how many concurrent users view dashboards. Clarify acceptable latency for live updates versus historical queries. Understand privacy constraints on individual tracking and whether second-by-second precision is needed or minute-level accuracy suffices. Confirm whether the system must handle retrofit buildings with incomplete sensor coverage.

Step 2: High-Level Architecture

Sketch three layers: ingestion, processing, and serving. Sensors publish events to Kafka partitioned by employee ID for ordering guarantees. Stream processors consume events, deduplicate using recent event IDs cached in Redis, and update materialized views in both a fast cache for current state and a durable store for history. The API layer reads from Redis for live occupancy and from a durable database for analytics queries. A background reconciliation job publishes corrected counts. WebSockets or server-sent events push occupancy changes to connected dashboards.

Step 3: Deep Dive on Event Processing

Walk through a badge swipe event lifecycle. The reader generates an event with timestamp, employee ID, zone ID, direction, and unique event ID. It lands in Kafka partitioned by employee ID. A stream consumer checks Redis for the event ID in a rolling 10-minute deduplication window; if found, it discards the duplicate. For new events, it atomically updates the employee's current location in Redis and increments the destination zone counter while decrementing the source zone. The processor appends the event to Cassandra for the employee's movement timeline. Partitioning by employee ID ensures concurrent events for different people do not conflict.

Step 4: Address Secondary Concerns

Cover how to serve various query patterns: live occupancy queries hit Redis for O(1) lookups, employee location lookups read a single Redis key, movement timelines query Cassandra with employee ID and time range, and parking analytics aggregate daily counters stored in a time-series table. Address caching for historical reports and precomputing popular aggregations overnight. Discuss monitoring by tracking event lag in Kafka and comparing expected versus actual zone totals. Explain disaster recovery through Kafka retention enabling full replay to rebuild all materialized views.

Related Learning Resources

• Databases -- modeling event-driven state with relational and NoSQL stores
• Caching -- using Redis atomic counters and sharded keys for real-time aggregation
• Message Queues -- partitioned event streams for ordered, replayable ingestion