System Design Interview Questions

Data isolation becomes critical: one school must never see another's data. Load peaks multiply, for example every school taking attendance between 8 and 9 AM. Availability, backups and restore per school, deployments without downtime, security reviews, and the cost per school all become explicit requirements with numbers.

Keep the normalized transactional database for daily work such as attendance and fees. Send reporting to a separate copy built for reading: a read replica for simple reports, or a data warehouse with denormalized tables refreshed on a schedule. Heavy reports then never slow down a teacher saving attendance.

SaaS (software as a service) means one company runs one system and many customers use it over the internet, usually for a monthly fee. Each customer is a tenant: it shares the system with the others but must only ever see its own data. In the SMS, each school is a tenant.

Functional requirements say what the system does, such as "a school admin can add a teacher". Non-functional requirements say how well it does it, such as "95% of requests answer in under 300 ms" or "99.9% availability". Non-functional ones need numbers, or nobody can test them.

A short document, kept next to the code, that records one important decision: the context, the options considered, the choice, and its consequences. It explains later why the system looks the way it does, so the same debate does not have to be repeated.

Shared database with a tenant column: one schema, cheapest, simplest upgrades, but a missed filter leaks data and one busy tenant can slow others. Schema per tenant: better separation, but every table is copied per tenant. Database per tenant: strongest isolation and easy per-tenant restore, but many databases to upgrade, back up, monitor and pay for, and cross-tenant reports are hard.

Measure bytes per row on real or realistic sample data (data plus every index, for example from sys.allocation_units or sys.dm_db_index_physical_stats), then multiply by rows per year from written assumptions: tenants, users, events per user. Keep the assumptions in one place so the estimate can be rerun. For the SMS: about 100 bytes per attendance row, 176 million rows a year, about 19 GB.

Users judge the system at its busiest moment. In the SMS, morning attendance is about 11 requests a second but results day is about 444, 40 times more; a system sized for the average would fail exactly when parents need it. Size compute, connection pools and caches for the peak, and use the quiet hours for batch work.

Each copy drifts: fixes must be repeated per customer, some copies miss security fixes, and upgrades become projects. Move to one multi-tenant system: one code base, one schema with a tenant id, per-tenant settings instead of per-tenant code, and a migration plan that moves customers one by one with their data verified.

Data leaks from a missing filter, and noisy neighbours. Control leaks with two layers: tenant filtering in every repository and database-level row-level security, plus tests that try to read another tenant's data. Control noisy neighbours with indexes that start with the tenant id, per-tenant rate limits, and background or serialized heavy jobs. Keep the option to move a very large tenant to its own database later.

Start with a shared database and a tenant id for cost and simple operations, with isolation enforced in the application and the database. Design for movement: tenant id in every key and index, a tenant catalogue that maps each tenant to a database, and tooling to move one tenant. As it grows, shard tenants across several databases, and give the largest or most regulated tenants their own database, all behind the same code.