From One School to 1,000: SaaS Requirements and Sizing – Capstone | Lesson 97
Introduction
For 96 lessons, the SMS has served one school: NexCoding Public School. Now other schools want it too. They do not want to buy servers or hire developers; they want to sign up and start on Monday.
That is a SaaS product: software as a service. One company runs one system, and many customers use it over the internet for a monthly fee. Each school is a tenant: it shares the system but must never see anyone else's data.
By the end of this lesson, you will be able to:
- Explain what changes when one system serves many schools.
- Write requirements for a SaaS product, with numbers.
- Measure how much data the SMS makes and project it to 1,000 schools.
- Compare three ways to keep schools' data apart, and choose one.
- Record a decision in an architecture decision record (ADR).
Why this matters
Lesson 3 listed the SMS's requirements for one school, and one interview question there already asked what changes at 1,000 schools. Now that question is real.
Most software jobs today are on SaaS products. Interviewers love the question "how would you make this multi-tenant?", because the answer shows whether you think about data, security, cost, and scale together.
Decisions made now, before any code changes, are the cheapest to change. A wrong choice found after 500 schools have signed up costs months.
The concept
Think about school lunches. A school kitchen cooks only for its own children. A central kitchen cooks for 1,000 schools: one building, one menu, one team.
It is far cheaper per meal, but it needs new rules. Every box carries its school's label, and the vans must never deliver a box to the wrong gate.
A huge order from one school must not delay everyone else's lunch either.
The SMS is about to become that central kitchen. The label on every box will be a SchoolId.
Example: what changes
| Area | One school (Lessons 1–96) | 1,000 schools |
|---|---|---|
| Data | everything belongs to the one school | every row belongs to exactly one school |
| Users | staff and parents of one school | plus a school admin per school, and our own platform admin |
| Settings | appsettings.json | per school: academic year, reminder time, name on messages |
| Upgrades | one school to tell | one release reaches every school at once |
| Load | one school's morning | every school's 9 am at the same time |
| A bug | one school affected | possibly every school, or one school's data shown to another |
The last row is the one that keeps SaaS teams awake. Showing one school's student list to another school is not a bug report; it is a data breach.
Example: three ways to keep schools apart
| Option | How | Strong points | Weak points |
|---|---|---|---|
A. One database, SchoolId column | every school table has SchoolId; every query filters by it | one schema, cheapest, one upgrade | a missed filter leaks data; one busy school can slow others |
| B. One schema per school | school0001.Students, school0002.Students, ... | better separation | 1,000 copies of every table to upgrade |
| C. One database per school | SchoolDb_0001, SchoolDb_0002, ... | strongest separation, easy restore per school | 1,000 databases to upgrade, back up, and pay for |
There is no free option. Each one trades cost and simplicity against isolation. To choose, we need numbers.
Apply it to the SMS
Step 1: write the requirements
The full list is in the code repo, in docs/saas-requirements.md. The functional ones say what the product
does:
- Isolation: a user sees and changes only their own school's data, in every request, report, cache, log, and job.
- Which school: every request is for exactly one school, known from the signed-in user.
- Onboarding: the platform admin adds a school in minutes.
- Per-school settings: academic year, reminder time, school name.
- Same code for all: one release serves every school.
- Off-boarding: a leaving school gets its data exported, then removed.
The non-functional ones say how well it must do it, and each has a number:
| Need | Target |
|---|---|
| Schools | 1,000 in three years |
| Response time | 95% of requests under 300 ms |
| Availability | 99.9% (at most about 9 hours down a year) |
| Data loss | at most 5 minutes; back within 1 hour |
| Noisy neighbour | one school's billing run must not slow other schools |
| Cost | infrastructure under 5% of what a school pays |
A requirement without a number cannot be tested. "Fast" starts arguments; "95% under 300 ms" starts a test.
Step 2: measure one school
How big will 1,000 schools be? We do not guess. SchoolDb already holds a full-size school with 10,000 students and 600,036 attendance rows, so we can measure what each row really costs, indexes included.
-- Lesson 97: how much data the SMS makes, measured on SchoolDb, then projected to 1,000 schools.
-- The four numbers at the top are assumptions; change them and run again.
-- Bytes per row come from the real tables (data and every index), so the projection uses measured sizes.
USE SchoolDb;
GO
DECLARE @Schools INT = 1000;
DECLARE @StudentsPerSchool INT = 800; -- a typical school; the sample school has 10,000
DECLARE @SchoolDaysPerYear INT = 220;
DECLARE @MarksPerStudentPerYear INT = 30; -- 5 exams x 6 subjects
-- 1. Data: bytes per row today, and rows and size per year for all schools.
WITH Measured AS (
SELECT t.name AS TableName,
SUM(CASE WHEN i.index_id IN (0, 1) THEN p.rows ELSE 0 END) AS RowsNow,
SUM(a.used_pages) * 8192.0 AS BytesNow
FROM sys.tables AS t
JOIN sys.indexes AS i ON i.object_id = t.object_id
JOIN sys.partitions AS p ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE t.name IN (N'AttendanceRecords', N'Marks', N'Students', N'Enrollments')
GROUP BY t.name
),
Projected AS (
SELECT TableName, RowsNow,
BytesNow / RowsNow AS BytesPerRow,
CAST(@Schools AS BIGINT) * @StudentsPerSchool *
CASE TableName
WHEN N'AttendanceRecords' THEN @SchoolDaysPerYear -- one row per student per school day
WHEN N'Marks' THEN @MarksPerStudentPerYear
ELSE 1 -- one student row, one enrollment a year
END AS RowsPerYear
FROM Measured
)
SELECT TableName,
RowsNow,
CAST(BytesPerRow AS DECIMAL(6, 1)) AS BytesPerRow,
RowsPerYear,
CAST(RowsPerYear * BytesPerRow / POWER(1024.0, 3) AS DECIMAL(8, 1)) AS GBPerYear
FROM Projected
ORDER BY GBPerYear DESC;
-- 2. Busy moments: requests per second at the two peaks of a school day.
-- Attendance: every section's register saved between 08:45 and 09:15 (20 sections in an 800-student school).
-- Results day: half of all parents open a report card in the first hour, with a sign-in and two more requests each.
SELECT N'Attendance registers, 08:45-09:15' AS Moment,
CAST(@Schools * (@StudentsPerSchool / 40) / (30 * 60.0) AS DECIMAL(8, 1)) AS RequestsPerSecond
UNION ALL
SELECT N'Results day, first hour',
CAST(@Schools * @StudentsPerSchool * 0.5 * 4 / 3600.0 AS DECIMAL(8, 1));
GO
The four numbers at the top are assumptions, clearly marked. Change them, run again, and the whole estimate updates. The bytes per row are measured from SchoolDb.
TableName RowsNow BytesPerRow RowsPerYear GBPerYear
AttendanceRecords 600036 99.5 176000000 16.3
Marks 120024 104.8 24000000 2.3
Students 10012 184.1 800000 .1
Enrollments 10024 97.3 800000 .1
Moment RequestsPerSecond
Attendance registers, 08:45-09:15 11.1
Results day, first hour 444.4
Step 3: read the numbers
About 19 GB a year. That is small for SQL Server. A single database can hold many years of it, and backups of that size take minutes.
Attendance is 87% of it. 176 million rows a year, one per student per school day. That table decides our indexing (Lesson 103) and is the first candidate for archiving old years.
The morning is easy; results day is not. Attendance is about 11 requests a second, even with every school saving at once.
Results day is about 444 a second, 40 times more. We design for that peak, not the average.
Example: when an assumption is wrong
Every row of the projection grows with the number of students. If the typical school has 1,500 students instead of 800, the data nearly doubles, to about 35 GB a year, and results day passes 800 requests a second.
That is still one database, so the decision below survives. Checking whether a decision survives a wrong assumption is part of the job.
Step 4: choose and write it down
With these numbers, option A wins: one database with a SchoolId column. 19 GB a year fits one database for
years, and one schema means one upgrade script, which matters most to a small team.
Its weak point, a missed filter, is serious, so the decision includes two locks. Lesson 100 adds both: every query
filters by SchoolId, and SQL Server itself blocks other schools' rows if a filter is ever missing.
The decision goes into an architecture decision record (ADR) in the code repo:
## Decision
**Option A.** One `SchoolDb`, a `Schools` table, and `SchoolId INT NOT NULL` on every table that belongs to a
school, with unique keys that include `SchoolId` (sms-reference.md section 6).
19 GB a year fits easily in one SQL Server database for many years. One schema means one upgrade script for every
school, which matters more to a small team than anything else here.
An ADR is short: the situation, the options, the choice, and what it costs. A year later, when someone asks "why not a database per school?", the answer is already written, with the numbers that led to it.
Example: what the next lessons build
| Lesson | Requirement it meets |
|---|---|
| 98 | the Schools table and SchoolId everywhere |
| 99 | "which school": the school in the sign-in token |
| 100 | isolation: filters plus row-level security |
| 101 | per-school caching, limits, jobs, and settings |
| 102 | onboarding a new school |
| 103 | results-day load and the noisy neighbour |
| 104–106 | running it: Docker, CI, and monitoring |
Common mistakes
- Copying the system once per customer. Ten copies become ten different versions. Fix: one product, per-school settings.
- Requirements without numbers. Nobody can test "fast" or "secure". Fix: a target for each one.
- Guessing sizes. Guesses are usually off by 10 times. Fix: measure real rows, then project with written assumptions.
- Designing for the average. The average day is calm; results day is not. Fix: size for the peak.
- Deciding without writing it down. The same debate returns every few months. Fix: an ADR.
In real projects
A team sold their school app to its second customer by copying the database and the code. By the tenth customer, there were ten slightly different versions, and every bug fix had to be made ten times.
One customer's copy missed a security fix for four months. Moving everyone onto one shared system took the team most of a year.
The school product we build in this module starts the other way round: one system and one schema from the first extra school. Customers differ only in their data and settings, never in their code.
Best practices
- Treat isolation between tenants as the first requirement, not a feature.
- Give every non-functional requirement a number you can test.
- Measure from real data, and keep the assumptions in one visible place.
- Size for the busiest hour, not the average day.
- Record big decisions in short ADRs next to the code.
Recap
- SaaS: one system, many tenants; in the SMS, each school is a tenant.
- Requirements now include isolation, onboarding, per-school settings, and numbers for speed, uptime, and cost.
- Measured on SchoolDb, 1,000 schools add about 19 GB a year; attendance is 87% of it.
- The peak is results day, about 444 requests a second.
- ADR 0001: one database with a
SchoolIdon every school table, protected by two locks.
Homework
Library module task: The Library module will also serve 1,000 school libraries. Estimate its first year: each library has 5,000 books with 1.5 copies each, and 9,600 loans a year (800 students, 12 loans each).
The library tables hold only a few sample rows, so page counts cannot give you bytes per row. Use
sys.dm_db_index_physical_stats instead, which measures the average size of a real row.
A correct result looks like:
TableName BytesPerRow RowsAfterOneYear MB
Books 206.7 5000000 985.5
BookIssues 76.0 9600000 695.8
BookCopies 55.3 7500000 395.2
Show the solution
-- Library homework, Lesson 97: how much data the Library module would make in 1,000 school libraries.
-- The library tables hold only a few sample rows, so page counts say nothing useful (one page is 8 KB even for
-- three rows). Instead, sys.dm_db_index_physical_stats measures the average size of a real row in every index,
-- and the projection adds them up per table.
USE SchoolDb;
GO
DECLARE @Libraries INT = 1000;
DECLARE @BooksPerLibrary INT = 5000;
DECLARE @CopiesPerBook DECIMAL(4, 1) = 1.5;
DECLARE @IssuesPerLibraryPerYear INT = 9600; -- 800 students x 12 loans a year
WITH RowSize AS (
-- One row per index (the table itself and each other index): its average leaf row size, plus the 2-byte
-- slot every row needs on its page. Their sum is what one new row costs across the whole table.
SELECT OBJECT_NAME(s.object_id) AS TableName,
SUM(s.avg_record_size_in_bytes + 2) AS BytesPerRow
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS s
WHERE s.object_id IN (OBJECT_ID(N'dbo.Books'), OBJECT_ID(N'dbo.BookCopies'), OBJECT_ID(N'dbo.BookIssues'))
AND s.index_level = 0
GROUP BY s.object_id
)
SELECT TableName,
CAST(BytesPerRow AS DECIMAL(6, 1)) AS BytesPerRow,
CAST(Rows AS BIGINT) AS RowsAfterOneYear,
CAST(Rows * BytesPerRow / POWER(1024.0, 2) AS DECIMAL(10, 1)) AS MB
FROM RowSize
CROSS APPLY (SELECT CASE TableName
WHEN N'Books' THEN @Libraries * @BooksPerLibrary * 1.0
WHEN N'BookCopies' THEN @Libraries * @BooksPerLibrary * @CopiesPerBook
ELSE @Libraries * @IssuesPerLibraryPerYear * 1.0
END AS Rows) AS r
ORDER BY MB DESC;
GO
About 2 GB in the first year, most of it the book catalogue itself. Unlike attendance, books are added once, so the library grows mainly through loans, by about 0.7 GB a year.
Quiz
In a SaaS product, what is a tenant?
Show answer
Answer: B. A customer, such as one school, sharing the system but not the data Each school is a tenant of the one SMS.
Which table makes most of the SMS's data?
Show answer
Answer: C. AttendanceRecords One row per student per school day: 176 million rows and 16.3 GB a year.
Why design for results day rather than an average day?
Show answer
Answer: B. The system must survive its busiest hour, about 40 times the morning load About 444 requests a second, against 11 for morning attendance.
What is the main risk of one database with a SchoolId column?
Show answer
Answer: B. A query that forgets the SchoolId filter shows other schools' data That is why Lesson 100 adds filters and row-level security.
What does an ADR record?
Show answer
Answer: B. One important decision: context, options, choice, and consequences So the reasons survive after the meeting is forgotten.
Interview questions
Questions are grouped by experience level. Click a question to see a model answer. All questions: System Design interview questions.
Fresher (0-2 yrs)
F What is a SaaS product, and what is a tenant? concept
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.
F What is the difference between functional and non-functional requirements? Give an example of each. concept
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.
F What is an architecture decision record (ADR)? concept
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.
Mid (3-5 yrs)
M Compare shared database, schema per tenant, and database per tenant. concept
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.
M How would you estimate the database size of a system before it is built? scenario
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.
M Why design for the peak hour instead of the average load? concept
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.
Senior (6-9 yrs)
S Your company sold its app to each new customer by copying the code and database. What problems follow, and how do you fix them? scenario
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.
How to answer: Name the operational and security costs of copies, then describe the target design and a safe migration path.
S You chose a shared database with a SchoolId column. What risks does that bring, and how do you control them? design
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.
How to answer: Name each risk with a concrete failure, then a preventive control, a detective control, and an escape hatch.
Architect / Lead (10+ yrs)
A Design the tenancy model for an education platform expected to grow from 50 to 10,000 schools. design
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.
How to answer: Show how the model evolves with scale and cost, covering isolation, routing through a tenant catalogue, sharding, per-tenant exceptions, and operations such as upgrades and backups.
Resources
- Code for this lesson: git tag
lesson-97-saas-requirements(code repository coming soon) - Files:
queries/097-sizing.sql,library-assignment/097-library-sizing.sql - Video: coming soon
What's next
The decision is made: one database, a SchoolId on every row. Next, SchoolDb gets its Schools table and every
school table gets its label. Lesson 98 (coming next).
Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.