Fees, Users, Audit Columns and Soft Delete – SQL Server | Lesson 14
Introduction
This lesson finishes the SMS tables. You create the fee tables and the user and role tables, then add two features that every serious business system needs.
Audit columns record who created or changed each row, and when. Soft delete marks rows as deleted instead of removing them, so nothing important is ever lost.
By the end of this lesson, you will be able to:
- Model invoices as a header table and a lines table, with part payments.
- Create user, role, and user-role tables, and store passwords safely.
- Add columns with defaults to existing tables using
ALTER TABLE ... ADD. - Add audit columns and soft delete, and explain their costs.
Why this matters
Money and access are where mistakes become serious. A fee payment that disappears, a receipt printed twice, or a parent who can log in as a teacher: each of these ends up in the principal's office.
The requirements from Lesson 3 asked for exactly this:
- NFR-02: passwords are never stored as plain text.
- NFR-03: deleted records can be restored.
- NFR-04: every change to marks and fees records who changed it and when.
This lesson turns those three into tables and columns.
The concept
Example: invoice header and lines
A real invoice has a header (number, date, student, total, status) and lines (Tuition 42,000; Library 1,500; Exam 2,000). Storing lines in their own table follows the normalization rules from Lesson 7: one fact per row.
| Table | One row is | Key rules |
|---|---|---|
FeeStructures | A fee type for a class in a year | UNIQUE (AcademicYearId, ClassId, FeeType) |
FeeInvoices | One bill for one enrollment | UNIQUE (InvoiceNumber), discount not above total |
FeeInvoiceItems | One line on a bill | UNIQUE (FeeInvoiceId, FeeStructureId) |
FeePayments | One payment against a bill | UNIQUE (ReceiptNumber), amount above 0 |
Example: the fee structure
FeeStructures is the school's price list for one year. Each row is one fee type for
one class. For 2026-27 at NexCoding Public School:
| Classes | Tuition | Library | Exam | Total |
|---|---|---|---|---|
| 1 to 5 | 30,000 | 1,500 | 2,000 | 33,500 |
| 6 to 8 | 36,000 | 1,500 | 2,000 | 39,500 |
| 9 to 10 | 42,000 | 1,500 | 2,000 | 45,500 |
That is 3 fee types for 10 classes: 30 rows. Next year gets 30 new rows with new prices, and this year's invoices keep pointing to this year's prices.
Example: an invoice as rows
Aarav's 2026-27 fees, as they are stored:
| Table | Row |
|---|---|
FeeInvoices | INV-2026-000001, enrollment 13 (Aarav, 10-A), total 45,500, status Paid |
FeeInvoiceItems | Tuition 42,000 |
FeeInvoiceItems | Library 1,500 |
FeeInvoiceItems | Exam 2,000 |
FeePayments | RCP-2026-000001, 45,500, Online, paid on 10 June |
The three lines add up to the header's total. One payment covers the whole invoice, so the status is Paid.
Example: part payments
An invoice has many payments. Diya's family pays 20,000 now and the rest later:
two rows in FeePayments against one invoice. The balance is always the invoice
total, minus the discount, minus the sum of its payments.
FeeInvoices.TotalAmount is the deliberate copy you met in Lesson 7: it is written
together with the lines and never edited on its own.
Example: users, roles, and passwords
A user is a login. A role is a group of permissions, such as Teacher or
Accountant. UserRoles is a junction table, because one user can have several roles.
Users.PasswordHash stores a hash, never the password. A hash is the output of a
one-way function: easy to compute from the password, practically impossible to turn
back into it.
At login, the app hashes what the user typed and compares the two hashes. You will build that in Module 12.
| Role | Can do in the SMS |
|---|---|
| Admin | Manage users, masters, and settings |
| Principal | See everything; approve corrections |
| Teacher | Attendance and marks for assigned sections |
| Accountant | Invoices and payments |
| Parent | See their own children's records |
Why a junction table and not a RoleId column on Users? Because real people wear
several hats: a principal who also teaches one class needs both the Principal and
Teacher roles.
With UserRoles, that is two rows. With a single RoleId, it is impossible without a
second login.
Example: one login per person
A user can be linked to a teacher or a guardian, but not both:
CONSTRAINT CK_Users_OnePerson CHECK (TeacherId IS NULL OR GuardianId IS NULL)
And a teacher can have at most one login. A plain UNIQUE (TeacherId) would allow
only one user with no teacher at all, so we use a filtered unique index, as in Lesson 10:
CREATE UNIQUE INDEX UQ_Users_TeacherId ON dbo.Users (TeacherId) WHERE TeacherId IS NOT NULL;
Example: audit columns
Audit columns are four columns added to important tables:
| Column | Meaning |
|---|---|
CreatedAt | When the row was created, in UTC; filled by a default |
CreatedBy | Which user created it (Users.Id); empty for seed data |
UpdatedAt | When it was last changed |
UpdatedBy | Who last changed it |
They answer "who and when" for the latest change. They do not keep the old values; a full history of every change needs an audit log, which comes later in the course.
Example: soft delete
Soft delete means a "deleted" row stays in the table with IsDeleted = 1 and a
DeletedAt time. Screens and reports simply leave those rows out. Restoring is one
update away.
Think of the recycle bin on your computer. Deleting a file moves it to the bin; it is gone from the folder but can come back. A real delete is emptying the bin.
Soft delete has costs, and you should know them:
- Every query must remember
WHERE IsDeleted = 0. Lesson 21 shows views that do it for you. - Unique rules still see deleted rows: a deleted student's admission number stays taken.
- The table keeps growing, because nothing is ever removed.
So the SMS uses it only where history matters: Students, Teachers, Guardians, Users, and FeePayments.
Example: soft delete and restore
This is what soft delete and restore look like. You learn UPDATE and SELECT with
WHERE in Module 4; for now, read what each statement does:
-- "Delete" a student: mark the row, keep the data.
UPDATE dbo.Students
SET IsDeleted = 1, DeletedAt = SYSUTCDATETIME()
WHERE AdmissionNumber = 'ADM-2017-0006';
-- Screens show only rows that are not deleted.
SELECT FirstName, LastName
FROM dbo.Students
WHERE IsDeleted = 0;
-- Restore: one update brings the student back.
UPDATE dbo.Students
SET IsDeleted = 0, DeletedAt = NULL
WHERE AdmissionNumber = 'ADM-2017-0006';
Nothing was removed, so all of the student's enrollments, attendance, and marks were safe the whole time.
Example: adding a column with a default
ALTER TABLE ... ADD adds new columns to an existing table. A new NOT NULL column
needs a DEFAULT, so existing rows get a value:
IF COL_LENGTH(N'dbo.Students', N'IsDeleted') IS NULL
ALTER TABLE dbo.Students ADD
IsDeleted BIT NOT NULL CONSTRAINT DF_Students_IsDeleted DEFAULT (0),
DeletedAt DATETIME2(0) NULL;
COL_LENGTH returns NULL when the column does not exist, so the script stays re-runnable.
Apply it to the SMS
The script has four parts: fees, users and roles, audit columns, and soft delete.
Save it as database\014-fees-users-audit.sql. It is long, so it is folded below;
open it, copy it, and run it with F5.
Example: the full script
Show database/014-fees-users-audit.sql
-- Lesson 14: fees, users and roles, then audit columns and soft delete.
-- Safe to run more than once.
USE SchoolDb;
GO
-- ── Part 1: fees ────────────────────────────────────────────────────────────
-- What each class pays per year, by fee type.
IF OBJECT_ID(N'dbo.FeeStructures', N'U') IS NULL
BEGIN
CREATE TABLE dbo.FeeStructures
(
Id INT IDENTITY(1, 1) NOT NULL,
AcademicYearId INT NOT NULL,
ClassId INT NOT NULL,
FeeType VARCHAR(10) NOT NULL,
Amount DECIMAL(10, 2) NOT NULL,
CONSTRAINT PK_FeeStructures PRIMARY KEY (Id),
CONSTRAINT FK_FeeStructures_AcademicYears FOREIGN KEY (AcademicYearId) REFERENCES dbo.AcademicYears (Id),
CONSTRAINT FK_FeeStructures_Classes FOREIGN KEY (ClassId) REFERENCES dbo.Classes (Id),
CONSTRAINT UQ_FeeStructures_AcademicYearId_ClassId_FeeType UNIQUE (AcademicYearId, ClassId, FeeType),
CONSTRAINT CK_FeeStructures_FeeType CHECK (FeeType IN ('Tuition', 'Transport', 'Library', 'Exam')),
CONSTRAINT CK_FeeStructures_Amount CHECK (Amount > 0)
);
END;
GO
-- Invoice header: one bill for one enrollment.
IF OBJECT_ID(N'dbo.FeeInvoices', N'U') IS NULL
BEGIN
CREATE TABLE dbo.FeeInvoices
(
Id INT IDENTITY(1, 1) NOT NULL,
InvoiceNumber VARCHAR(20) NOT NULL, -- INV-2026-000001
EnrollmentId INT NOT NULL,
InvoiceDate DATE NOT NULL,
DueDate DATE NOT NULL,
TotalAmount DECIMAL(10, 2) NOT NULL,
DiscountAmount DECIMAL(10, 2) NOT NULL CONSTRAINT DF_FeeInvoices_DiscountAmount DEFAULT (0),
Status VARCHAR(13) NOT NULL CONSTRAINT DF_FeeInvoices_Status DEFAULT ('Unpaid'),
CONSTRAINT PK_FeeInvoices PRIMARY KEY (Id),
CONSTRAINT FK_FeeInvoices_Enrollments FOREIGN KEY (EnrollmentId) REFERENCES dbo.Enrollments (Id),
CONSTRAINT UQ_FeeInvoices_InvoiceNumber UNIQUE (InvoiceNumber),
CONSTRAINT CK_FeeInvoices_DueDate CHECK (DueDate >= InvoiceDate),
CONSTRAINT CK_FeeInvoices_TotalAmount CHECK (TotalAmount >= 0),
CONSTRAINT CK_FeeInvoices_DiscountAmount CHECK (DiscountAmount BETWEEN 0 AND TotalAmount),
CONSTRAINT CK_FeeInvoices_Status CHECK (Status IN ('Unpaid', 'PartiallyPaid', 'Paid', 'Cancelled'))
);
END;
GO
-- Invoice lines: one row per fee type on the invoice.
IF OBJECT_ID(N'dbo.FeeInvoiceItems', N'U') IS NULL
BEGIN
CREATE TABLE dbo.FeeInvoiceItems
(
Id INT IDENTITY(1, 1) NOT NULL,
FeeInvoiceId INT NOT NULL,
FeeStructureId INT NOT NULL,
Amount DECIMAL(10, 2) NOT NULL,
CONSTRAINT PK_FeeInvoiceItems PRIMARY KEY (Id),
CONSTRAINT FK_FeeInvoiceItems_FeeInvoices FOREIGN KEY (FeeInvoiceId) REFERENCES dbo.FeeInvoices (Id),
CONSTRAINT FK_FeeInvoiceItems_FeeStructures FOREIGN KEY (FeeStructureId) REFERENCES dbo.FeeStructures (Id),
CONSTRAINT UQ_FeeInvoiceItems_FeeInvoiceId_FeeStructureId UNIQUE (FeeInvoiceId, FeeStructureId),
CONSTRAINT CK_FeeInvoiceItems_Amount CHECK (Amount > 0)
);
END;
GO
-- Payments: an invoice can be paid in parts.
IF OBJECT_ID(N'dbo.FeePayments', N'U') IS NULL
BEGIN
CREATE TABLE dbo.FeePayments
(
Id INT IDENTITY(1, 1) NOT NULL,
ReceiptNumber VARCHAR(20) NOT NULL, -- RCP-2026-000001
FeeInvoiceId INT NOT NULL,
Amount DECIMAL(10, 2) NOT NULL,
PaidOn DATETIME2(0) NOT NULL, -- UTC
PaymentMode VARCHAR(12) NOT NULL,
TransactionReference VARCHAR(50) NULL,
CONSTRAINT PK_FeePayments PRIMARY KEY (Id),
CONSTRAINT FK_FeePayments_FeeInvoices FOREIGN KEY (FeeInvoiceId) REFERENCES dbo.FeeInvoices (Id),
CONSTRAINT UQ_FeePayments_ReceiptNumber UNIQUE (ReceiptNumber),
CONSTRAINT CK_FeePayments_Amount CHECK (Amount > 0),
CONSTRAINT CK_FeePayments_PaymentMode CHECK (PaymentMode IN ('Cash', 'Card', 'Online', 'BankTransfer', 'Cheque'))
);
END;
GO
-- ── Part 2: users and roles ─────────────────────────────────────────────────
IF OBJECT_ID(N'dbo.Roles', N'U') IS NULL
BEGIN
CREATE TABLE dbo.Roles
(
Id INT IDENTITY(1, 1) NOT NULL,
Name VARCHAR(20) NOT NULL,
CONSTRAINT PK_Roles PRIMARY KEY (Id),
CONSTRAINT UQ_Roles_Name UNIQUE (Name)
);
END;
GO
IF OBJECT_ID(N'dbo.Users', N'U') IS NULL
BEGIN
CREATE TABLE dbo.Users
(
Id INT IDENTITY(1, 1) NOT NULL,
UserName VARCHAR(50) NOT NULL,
Email VARCHAR(100) NOT NULL,
PasswordHash VARCHAR(255) NOT NULL, -- never the plain password
TeacherId INT NULL,
GuardianId INT NULL,
IsActive BIT NOT NULL CONSTRAINT DF_Users_IsActive DEFAULT (1),
LastLoginAt DATETIME2(0) NULL,
CONSTRAINT PK_Users PRIMARY KEY (Id),
CONSTRAINT UQ_Users_UserName UNIQUE (UserName),
CONSTRAINT UQ_Users_Email UNIQUE (Email),
CONSTRAINT FK_Users_Teachers FOREIGN KEY (TeacherId) REFERENCES dbo.Teachers (Id),
CONSTRAINT FK_Users_Guardians FOREIGN KEY (GuardianId) REFERENCES dbo.Guardians (Id),
CONSTRAINT CK_Users_OnePerson CHECK (TeacherId IS NULL OR GuardianId IS NULL)
);
END;
GO
-- A teacher or a guardian has at most one login. Empty (NULL) values are ignored.
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UQ_Users_TeacherId')
CREATE UNIQUE INDEX UQ_Users_TeacherId ON dbo.Users (TeacherId) WHERE TeacherId IS NOT NULL;
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UQ_Users_GuardianId')
CREATE UNIQUE INDEX UQ_Users_GuardianId ON dbo.Users (GuardianId) WHERE GuardianId IS NOT NULL;
GO
IF OBJECT_ID(N'dbo.UserRoles', N'U') IS NULL
BEGIN
CREATE TABLE dbo.UserRoles
(
UserId INT NOT NULL,
RoleId INT NOT NULL,
CONSTRAINT PK_UserRoles PRIMARY KEY (UserId, RoleId),
CONSTRAINT FK_UserRoles_Users FOREIGN KEY (UserId) REFERENCES dbo.Users (Id),
CONSTRAINT FK_UserRoles_Roles FOREIGN KEY (RoleId) REFERENCES dbo.Roles (Id)
);
END;
GO
-- ── Part 3: audit columns (who created or changed a row, and when) ──────────
IF COL_LENGTH(N'dbo.Teachers', N'CreatedAt') IS NULL
ALTER TABLE dbo.Teachers ADD
CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_Teachers_CreatedAt DEFAULT (SYSUTCDATETIME()),
CreatedBy INT NULL,
UpdatedAt DATETIME2(0) NULL,
UpdatedBy INT NULL;
IF COL_LENGTH(N'dbo.Students', N'CreatedAt') IS NULL
ALTER TABLE dbo.Students ADD
CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_Students_CreatedAt DEFAULT (SYSUTCDATETIME()),
CreatedBy INT NULL,
UpdatedAt DATETIME2(0) NULL,
UpdatedBy INT NULL;
IF COL_LENGTH(N'dbo.Guardians', N'CreatedAt') IS NULL
ALTER TABLE dbo.Guardians ADD
CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_Guardians_CreatedAt DEFAULT (SYSUTCDATETIME()),
CreatedBy INT NULL,
UpdatedAt DATETIME2(0) NULL,
UpdatedBy INT NULL;
IF COL_LENGTH(N'dbo.Enrollments', N'CreatedAt') IS NULL
ALTER TABLE dbo.Enrollments ADD
CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_Enrollments_CreatedAt DEFAULT (SYSUTCDATETIME()),
CreatedBy INT NULL,
UpdatedAt DATETIME2(0) NULL,
UpdatedBy INT NULL;
IF COL_LENGTH(N'dbo.SubjectAllocations', N'CreatedAt') IS NULL
ALTER TABLE dbo.SubjectAllocations ADD
CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_SubjectAllocations_CreatedAt DEFAULT (SYSUTCDATETIME()),
CreatedBy INT NULL,
UpdatedAt DATETIME2(0) NULL,
UpdatedBy INT NULL;
IF COL_LENGTH(N'dbo.ClassTeachers', N'CreatedAt') IS NULL
ALTER TABLE dbo.ClassTeachers ADD
CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_ClassTeachers_CreatedAt DEFAULT (SYSUTCDATETIME()),
CreatedBy INT NULL,
UpdatedAt DATETIME2(0) NULL,
UpdatedBy INT NULL;
IF COL_LENGTH(N'dbo.Exams', N'CreatedAt') IS NULL
ALTER TABLE dbo.Exams ADD
CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_Exams_CreatedAt DEFAULT (SYSUTCDATETIME()),
CreatedBy INT NULL,
UpdatedAt DATETIME2(0) NULL,
UpdatedBy INT NULL;
IF COL_LENGTH(N'dbo.ExamSubjects', N'CreatedAt') IS NULL
ALTER TABLE dbo.ExamSubjects ADD
CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_ExamSubjects_CreatedAt DEFAULT (SYSUTCDATETIME()),
CreatedBy INT NULL,
UpdatedAt DATETIME2(0) NULL,
UpdatedBy INT NULL;
IF COL_LENGTH(N'dbo.Marks', N'CreatedAt') IS NULL
ALTER TABLE dbo.Marks ADD
CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_Marks_CreatedAt DEFAULT (SYSUTCDATETIME()),
CreatedBy INT NULL,
UpdatedAt DATETIME2(0) NULL,
UpdatedBy INT NULL;
IF COL_LENGTH(N'dbo.FeeStructures', N'CreatedAt') IS NULL
ALTER TABLE dbo.FeeStructures ADD
CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_FeeStructures_CreatedAt DEFAULT (SYSUTCDATETIME()),
CreatedBy INT NULL,
UpdatedAt DATETIME2(0) NULL,
UpdatedBy INT NULL;
IF COL_LENGTH(N'dbo.FeeInvoices', N'CreatedAt') IS NULL
ALTER TABLE dbo.FeeInvoices ADD
CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_FeeInvoices_CreatedAt DEFAULT (SYSUTCDATETIME()),
CreatedBy INT NULL,
UpdatedAt DATETIME2(0) NULL,
UpdatedBy INT NULL;
IF COL_LENGTH(N'dbo.FeePayments', N'CreatedAt') IS NULL
ALTER TABLE dbo.FeePayments ADD
CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_FeePayments_CreatedAt DEFAULT (SYSUTCDATETIME()),
CreatedBy INT NULL,
UpdatedAt DATETIME2(0) NULL,
UpdatedBy INT NULL;
IF COL_LENGTH(N'dbo.Users', N'CreatedAt') IS NULL
ALTER TABLE dbo.Users ADD
CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_Users_CreatedAt DEFAULT (SYSUTCDATETIME()),
CreatedBy INT NULL,
UpdatedAt DATETIME2(0) NULL,
UpdatedBy INT NULL;
-- AttendanceRecords is the busiest table: it records only when and by whom it was created.
IF COL_LENGTH(N'dbo.AttendanceRecords', N'CreatedAt') IS NULL
ALTER TABLE dbo.AttendanceRecords ADD
CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_AttendanceRecords_CreatedAt DEFAULT (SYSUTCDATETIME()),
CreatedBy INT NULL;
GO
-- ── Part 4: soft delete (rows are marked deleted, never removed) ───────────
IF COL_LENGTH(N'dbo.Students', N'IsDeleted') IS NULL
ALTER TABLE dbo.Students ADD
IsDeleted BIT NOT NULL CONSTRAINT DF_Students_IsDeleted DEFAULT (0),
DeletedAt DATETIME2(0) NULL;
IF COL_LENGTH(N'dbo.Teachers', N'IsDeleted') IS NULL
ALTER TABLE dbo.Teachers ADD
IsDeleted BIT NOT NULL CONSTRAINT DF_Teachers_IsDeleted DEFAULT (0),
DeletedAt DATETIME2(0) NULL;
IF COL_LENGTH(N'dbo.Guardians', N'IsDeleted') IS NULL
ALTER TABLE dbo.Guardians ADD
IsDeleted BIT NOT NULL CONSTRAINT DF_Guardians_IsDeleted DEFAULT (0),
DeletedAt DATETIME2(0) NULL;
IF COL_LENGTH(N'dbo.Users', N'IsDeleted') IS NULL
ALTER TABLE dbo.Users ADD
IsDeleted BIT NOT NULL CONSTRAINT DF_Users_IsDeleted DEFAULT (0),
DeletedAt DATETIME2(0) NULL;
IF COL_LENGTH(N'dbo.FeePayments', N'IsDeleted') IS NULL
ALTER TABLE dbo.FeePayments ADD
IsDeleted BIT NOT NULL CONSTRAINT DF_FeePayments_IsDeleted DEFAULT (0),
DeletedAt DATETIME2(0) NULL;
GO
-- Check: every table and how many columns it has.
SELECT
t.name AS TableName,
COUNT(c.column_id) AS ColumnCount
FROM sys.tables AS t
JOIN sys.columns AS c ON c.object_id = t.object_id
GROUP BY t.name
ORDER BY t.name;
GO
Example: check the result
The last query counts the columns of every table. It uses JOIN and GROUP BY,
which you learn in Lessons 17 and 18. Part of the result:
| TableName | ColumnCount |
|---|---|
| AttendanceRecords | 7 |
| FeeInvoices | 12 |
| FeePayments | 13 |
| Students | 15 |
| Teachers | 17 |
| Users | 14 |
There are now 22 tables: the full SMS design. Students grew from 9 to 15
columns: four audit columns and two soft-delete columns. AttendanceRecords has only
CreatedAt and CreatedBy, because it is the busiest table and is rarely edited.
Common mistakes
- Storing passwords, even encrypted. Encryption can be reversed with the key. Fix: store a slow, salted hash; Module 12 shows how.
- Adding a
NOT NULLcolumn without a default. It fails on a table with rows. Fix: add aDEFAULTin the same statement. - Soft delete everywhere. Every query gets an extra filter, and tables never shrink. Fix: use it only where history or restore matters.
- One
Amountcolumn on the invoice instead of lines. You cannot tell tuition from exam fees later. Fix: a lines table. - Trusting the screen to fill
UpdatedBy. A forgotten field leaves it empty. Fix: set audit columns in one shared place in the API (Module 12). - Editing an invoice's total on its own. The header then disagrees with its lines, and the balance is wrong. Fix: change lines and total together, in one transaction, or cancel the invoice and issue a new one.
In real projects
The audit columns paid for themselves the first time a payment was disputed. The
accountant said "I never changed that", and UpdatedBy and UpdatedAt settled it in
a minute. Without them, the meeting would have been about trust instead of facts.
I have also seen soft delete go wrong. One team added IsDeleted to every table, and
a year later reports were counting deleted rows because someone forgot the filter.
Use it where you can name the reason, hide it behind views, and test reports for it.
Best practices
- Model invoices as header, lines, and payments.
- Store only password hashes, never passwords.
- Add audit columns to every table whose rows people create or change.
- Use soft delete only where restore or history is a real requirement.
- Give every new
NOT NULLcolumn a namedDEFAULT. - Give users several roles through a junction table, never a single role column.
Recap
- Fees use four tables: structures, invoice headers, invoice lines, payments.
- Part payments are several payment rows for one invoice.
Users,Roles, andUserRolesmodel logins and permissions.- Audit columns record who created or changed a row, and when.
- Soft delete keeps rows with
IsDeleted = 1, at the cost of filtering everywhere. - The SMS now has all 22 tables.
- An invoice's total is written with its lines and never edited on its own.
Homework
Library module task: Add the four audit columns and soft delete to Books. A
librarian who removes a damaged book by mistake must be able to restore it.
A correct result looks like: Books has CreatedAt, CreatedBy, UpdatedAt,
UpdatedBy, IsDeleted, and DeletedAt, and the script runs twice without errors.
Show the solution
-- Library homework, Lesson 14: audit columns and soft delete for Books.
USE SchoolDb;
GO
IF COL_LENGTH(N'dbo.Books', N'CreatedAt') IS NULL
ALTER TABLE dbo.Books ADD
CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_Books_CreatedAt DEFAULT (SYSUTCDATETIME()),
CreatedBy INT NULL,
UpdatedAt DATETIME2(0) NULL,
UpdatedBy INT NULL;
IF COL_LENGTH(N'dbo.Books', N'IsDeleted') IS NULL
ALTER TABLE dbo.Books ADD
IsDeleted BIT NOT NULL CONSTRAINT DF_Books_IsDeleted DEFAULT (0),
DeletedAt DATETIME2(0) NULL;
GO
Quiz
Why are invoice lines stored in FeeInvoiceItems instead of columns on FeeInvoices?
Show answer
Answer: B. Each fee type is one fact, one row: easy to add types and report on them A lines table follows normalization and handles any number of fee types.
How does the SMS record a part payment?
Show answer
Answer: B. As one of several FeePayments rows for the same invoice An invoice has many payments; the balance is total minus discount minus payments.
What does Users.PasswordHash store?
Show answer
Answer: C. A one-way hash of the password A hash cannot be turned back into the password.
Which is a cost of soft delete?
Show answer
Answer: B. Every query must filter out deleted rows Forgetting WHERE IsDeleted = 0 makes reports count deleted rows.
Why does ALTER TABLE ... ADD IsDeleted BIT NOT NULL need a DEFAULT?
Show answer
Answer: B. So existing rows get a value for the new NOT NULL column Existing rows must have a value; the default supplies 0.
Interview questions
Questions are grouped by experience level. Click a question to see a model answer. All questions: Security interview questions, SQL Server interview questions.
Fresher (0-2 yrs)
F What are audit columns? concept
Columns that record who created a row and when (CreatedAt, CreatedBy), and who last
changed it and when (UpdatedAt, UpdatedBy). CreatedAt usually has a default of
SYSUTCDATETIME(). They answer "who and when" for the latest change, not the full history.
F What is soft delete? concept
Instead of removing a row with DELETE, you mark it as deleted with IsDeleted = 1 and
a DeletedAt time. Screens and reports leave those rows out, and the row can be restored
by setting the flag back.
F Why are invoices stored as a header table and a lines table? design
The header (FeeInvoices) holds facts about the whole bill: number, date, student,
total, status. Each line (FeeInvoiceItems) holds one fee type and amount. One fact per
row means any number of fee types per invoice, and reports can total tuition or exam
fees across all invoices.
Mid (3-5 yrs)
M Why do we store a password hash instead of the password, and how is a hash different from encryption? concept
A hash is a one-way function: easy to compute from the password, practically impossible to reverse. At login, the app hashes the typed password and compares hashes, so the real password is never stored. Encryption can be reversed with a key, so a stolen key exposes every password.
M What are the downsides of soft delete? concept
Every query must filter IsDeleted = 0, and forgetting it shows deleted rows in
reports. Unique constraints still see deleted rows, so a deleted record's unique value
stays taken. Tables never shrink. Use it only where restore or history is required, and
hide the filter in views.
M How do you add a NOT NULL column to a table that already has rows? coding
Add it with a default in the same statement, for example ALTER TABLE dbo.Students ADD IsDeleted BIT NOT NULL CONSTRAINT DF_Students_IsDeleted DEFAULT (0). Existing rows get the
default value. Without a default, the statement fails because existing rows would have
no value.
M How does the SMS support part payments, and how do you calculate an invoice's balance? concept
An invoice can have many rows in FeePayments. The balance is the invoice total, minus
its discount, minus the sum of its payments. The invoice status (Unpaid, PartiallyPaid,
Paid) is updated in the same transaction that records a payment.
Senior (6-9 yrs)
S When are audit columns enough, and when do you need a full audit history? design
Audit columns record only the latest change, which is enough for "who touched this last". When you must know every old value, such as for marks or payments, you need a history: an audit table filled by triggers, a system-versioned temporal table, or change data capture. Each keeps old versions at the cost of storage.
How to answer: Start from the question the business needs answered, then match a tool: audit columns, temporal tables (automatic history per table), or an audit log (custom, can include the reason). Mention storage growth and how long history must be kept.
S A parent double-clicks 'Pay' and two identical payments are recorded. How do you prevent that? scenario
Make the payment request idempotent: the client sends a unique payment reference, and a unique constraint on it refuses the second insert. Record the payment and update the invoice status in one transaction, and have the screen disable the button after the first click. The database rule is what makes it reliable.
How to answer: Explain the cause (retries and double clicks happen), then layer the defences: UI, API idempotency key, unique constraint, and a transaction. Name the trade-off: you need a reference that the client generates once per payment attempt.
Architect / Lead (10+ yrs)
A How would you design roles and permissions so they can grow with the system? design
Start with users, roles, and a user-role junction, as in the SMS. When permissions get finer, add a permissions table and a role-permission junction, and check permissions rather than role names in code. Data rules, such as "a parent sees only their children", are checked separately from roles.
How to answer: Describe role-based access control and its limits, then the step to permission-based checks. Use SMS examples: Teacher and Accountant roles, and the parent-sees-own-child rule as a data-level check. Mention the trade-off: more flexibility means more admin screens and more testing of who can do what.
Resources
- Code for this lesson: git tag
lesson-14-fees-users-audit(code repository coming soon) - Files:
database/014-fees-users-audit.sql,library-assignment/014-books-audit.sql - Video: coming soon
What's next
All 22 tables exist, but they are empty. Next, you fill them with the sample school, and then with 10,000 students. Lesson 15: Sample data
Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.