Database design case studies

In short

A database design case study takes a real business statement, turns it into entities, relationships and constraints, normalises them, and then settles the physical choices - keys, indexes, data types, partitions and audit columns - that make the schema work at the required load. It is the applied end of database theory: the same requirement handled badly produces redundancy, update anomalies, wrong reports and slow queries. Indian university syllabi set it as a mini-project plus a 10 to 15 mark design question (library, railway reservation, hospital, payroll, university, banking), and GATE tests the reasoning fragments - key identification, dependency-preserving decomposition, constraint choice. In placement rounds at TCS, Infosys, Wipro, Cognizant, Accenture, Zoho, Freshworks, Paytm and Zoho-style product firms, "design the schema for X" is a standard 20-minute whiteboard question.

Explained

The design lifecycle - six stages

1. Requirement analysis: collect nouns (candidate entities), verbs (candidate relationships), reports needed, volumes, growth rate, retention period and who may see what. Output: a written requirement list with volumes.
2. Conceptual design: an ER / EER diagram - entity sets, attributes, cardinality (1:1, 1:N, M:N), participation (total/partial), weak entities, specialisation/generalisation. Independent of any DBMS.
3. Logical design: map ER to relations, choose primary and foreign keys, normalise to 3NF/BCNF, state constraints (NOT NULL, UNIQUE, CHECK, referential actions).
4. Physical design: data types and widths, clustering, indexes, partitioning, fill factor, tablespaces, storage engine (InnoDB vs MyISAM), collation.
5. Implementation and population: DDL scripts, reference/master data seeding, views, stored procedures, roles and grants.
6. Tuning and evolution: measure actual queries, add or drop indexes, denormalise selectively, version the schema with migration scripts.

ER-to-relational mapping rules used in every case study

• Strong entity: one table; its key attribute becomes the primary key.
• Composite attribute: flatten into parts (address → line1, city, state, pincode). Multivalued attribute: separate table with a foreign key (student_phone).
• 1:N relationship: post the primary key of the "1" side into the "N" side as a foreign key; no third table needed.
• M:N relationship: a junction/bridge table with a composite primary key of both foreign keys, plus any relationship attributes (issue_date, marks, quantity).
• 1:1 relationship: merge into one table, or keep the foreign key with a UNIQUE constraint on the side with total participation.
• Weak entity: table whose primary key = owner's key + partial (discriminator) key; the foreign key carries ON DELETE CASCADE.
• Ternary relationship: one table with three foreign keys; do not break it into three binaries unless the dependency genuinely splits.
• Specialisation: three strategies - a single table with a type column and nullable attributes; a superclass table plus one table per subclass, each repeating the superclass key; or one table per concrete subclass only, with the superclass attributes copied into each and no shared table. Choose by how often queries need all subtypes together.

Design decisions that recur, and how to settle them

• Natural vs surrogate key: PAN, Aadhaar, roll number, ISBN are natural keys but they change, repeat across institutions, or are sensitive. Default to a surrogate id (AUTO_INCREMENT / IDENTITY / sequence) and keep the natural key as a UNIQUE column.
• Never put Aadhaar in a primary key - it is regulated, it should be stored only with a stated purpose, and preferably as a hash or last-4 reference. Under the Digital Personal Data Protection Act 2023, personal data needs purpose limitation, consent records and deletion once the purpose ends; the detailed rules have been notified in stages, so treat specific timelines as checkable rather than fixed.
• Status as a column vs state table: a status CHECK-constrained column is enough for 3 to 5 fixed states; a lookup table is better when states carry attributes or are added by users.
• Soft delete: mark is_active / deleted_at rather than DELETE, where history or law requires retention; unique indexes then need the flag included.
• Audit columns: created_at, created_by, updated_at, updated_by on every transactional table; this is assumed in any Indian enterprise or BFSI schema.
• Storing money: DECIMAL(12,2) in rupees, never FLOAT; store amount and currency separately if multi-country. GST needs taxable value, rate, CGST, SGST, IGST and HSN/SAC code as separate columns on the invoice line - GST law requires the invoice to show them separately.
• Derived data: do not store age (store date_of_birth); do not store line total if it is quantity × rate - unless it is a financial document, where the printed value must be frozen because rates change later.
• Snapshot vs reference: an invoice must snapshot the price, the address and the tax rate at the time of the sale; a wishlist only references the product.
• Temporal design: validity columns (valid_from, valid_to) for fee structures, salary scales, interest rates and fare slabs; this keeps history without duplicating the master.

Normalisation in practice, and controlled denormalisation

• Normalise to 3NF/BCNF by default - it removes insert, update and delete anomalies; it is the examinable baseline and the safe answer in interviews.
• Denormalise only after measuring, and only in these patterns: a cached aggregate (order_total, available_copies, seats_available), a duplicated lookup value for reporting (city_name on fact rows), a pre-joined reporting table or materialised view, and a star schema (fact + dimension tables) for analytics.
• Every denormalised value needs a stated maintenance mechanism - trigger, application transaction or scheduled rebuild - and a reconciliation query. "A denormalised value with no owner" is how reports start disagreeing.
• OLTP vs OLAP: OLTP wants narrow normalised tables, many short transactions, indexes on selective predicates. OLAP wants wide dimension tables, bulk scans, columnar storage, measures by period.

Case study 1 - Library management system

• Entities: BOOK (title, publisher, subject), BOOK_COPY (accession number - the physical item), MEMBER, STAFF, ISSUE, RESERVATION, FINE.
• The key insight: separate the title from the copy. ISBN identifies a title; the accession number identifies a copy. Issue happens against a copy, reservation against a title.
• Relationships: BOOK 1:N BOOK_COPY; MEMBER 1:N ISSUE; BOOK_COPY 1:N ISSUE (over time); BOOK M:N AUTHOR via BOOK_AUTHOR.
• Constraints: a copy can have at most one open issue - enforce with a partial/filtered unique index on (copy_id) WHERE return_date IS NULL (PostgreSQL, SQL Server), or a separate current_issue table where the DBMS has no filtered index.
• Fine rule belongs in a fine_policy table with validity dates (for example ₹2 per day after 14 days), not hardcoded in a trigger.

Case study 2 - Railway reservation (IRCTC-style)

• Entities: TRAIN, STATION, ROUTE_STOP (train, station, sequence, arrival, departure, distance), COACH_LAYOUT, SCHEDULE (train + journey date), SEAT_INVENTORY, PNR, PASSENGER, PAYMENT, CANCELLATION.
• Why journey date matters: inventory is per train per date per class per segment, not per train - so SCHEDULE is a separate entity and seat availability hangs off it.
• Segment availability: a booking from Pune to Nagpur occupies the seat only on the stops between them; availability is computed over route segments, which is why a simple seats_left column is wrong for intermediate stations.
• PNR structure: PNR is the booking (1) and PASSENGER_DETAIL the many - a PNR carries up to six passengers, each with name, age, gender, berth preference, allotted berth and status (CNF, RAC, WL).
• Concurrency: the hot row is the availability counter; design for short transactions, row-level locking and an idempotent payment reference, otherwise double-booking occurs at tatkal opening.

Case study 3 - Hospital management system

• Entities: PATIENT, DOCTOR, DEPARTMENT, APPOINTMENT, VISIT/ENCOUNTER, DIAGNOSIS, PRESCRIPTION, PRESCRIPTION_LINE, LAB_TEST, LAB_ORDER, BED, ADMISSION, BILL, BILL_LINE, INSURANCE_CLAIM.
• Encounter is the spine: every clinical fact (diagnosis, prescription, test, bill line) links to an encounter, not directly to the patient - that is what makes a visit-wise record possible.
• Weak entity: PRESCRIPTION_LINE is identified by prescription_id + line_no.
• Immutability: clinical records are append-only; corrections are new rows with a reason, never UPDATE over history.
• Indian context: ABHA (Ayushman Bharat Health Account) number under ABDM is stored as a UNIQUE nullable identifier; ICD-10 codes for diagnosis; health data is personal data under the DPDP Act 2023 (which has no separate sensitive class; its duties apply from 13 May 2027) and sensitive personal data under the IT Act's SPDI Rules 2011 until then, so access control and consent artefacts are part of the design.

Case study 4 - Payroll, university and e-commerce in brief

• Payroll: EMPLOYEE, DEPARTMENT, DESIGNATION, SALARY_STRUCTURE (valid_from/valid_to), PAY_COMPONENT (earning/deduction, taxable flag), PAYROLL_RUN (month, status), PAYSLIP, PAYSLIP_LINE, ATTENDANCE, LEAVE_BALANCE. Statutory components - EPF (12% of basic wages plus DA, within the wage ceiling, currently ₹15,000 for mandatory coverage), ESI, professional tax (state-wise), TDS - are rows in PAY_COMPONENT with rate tables by period, never constants in code. A payroll run must be re-runnable and lockable once paid.
• University: STUDENT, PROGRAMME, COURSE, SEMESTER, OFFERING (course + semester + faculty + section), ENROLMENT (student + offering, with internal and external marks), GRADE_SCALE, FEE_HEAD, FEE_PAYMENT. Marks hang on ENROLMENT; CGPA is derived, not stored (or stored as a cached aggregate per semester with a rebuild job). A re-appear is a new enrolment row, which is why student+course is not a unique key - student+offering is.
• E-commerce: CUSTOMER, ADDRESS, CATALOGUE (PRODUCT, VARIANT/SKU, PRICE_HISTORY), CART, CART_LINE, ORDER, ORDER_LINE (snapshot of price, tax, HSN), SHIPMENT, PAYMENT, RETURN. Stock lives on SKU per warehouse; the order line stores the price as sold. Payment gateway reference (Razorpay/UPI) is UNIQUE for idempotency on retries.

Evaluating a design

• Completeness: every requirement and report is answerable by a query - verify by writing the top 10 queries against the schema.
• Correctness: keys and dependencies are right; no anomaly on insert/update/delete; referential actions chosen deliberately.
• Minimality and clarity: no redundant attribute, consistent naming (snake_case, singular or plural used consistently, <table>_id for foreign keys), a data dictionary for every column.
• Performance: indexes support the real predicates and sorts; estimated row counts and growth documented.
• Security and compliance: least-privilege roles, encryption of sensitive columns, retention rules, audit trail.

Key points

• Conceptual design - DBMS-independent ER/EER model from the requirement statement.
• Logical design - ER mapped to relations with keys, normal forms and constraints.
• Physical design - types, indexes, clustering, partitioning, storage parameters.
• 1:N mapping - foreign key on the many side; M:N mapping - a junction table with a composite key.
• Weak entity - primary key = owner key + discriminator, with cascading delete.
• Surrogate key - system-generated id; keep the natural key as UNIQUE instead of as the primary key.
• Title vs copy - the single most-tested insight in library design; ISBN names a title, accession number names a copy.
• Journey date - railway inventory is per train per date per class per segment, not per train.
• Encounter/visit - the hub that clinical and billing records attach to in hospital schemas.
• Snapshot rule - invoices and payslips freeze price, tax and address as of the transaction date.
• Validity columns - valid_from/valid_to keep fee, fare, salary and rate history without duplication.
• Derived data - compute age, totals and CGPA; store them only as declared caches with a rebuild job.
• Denormalisation - a measured decision with a named maintenance mechanism and a reconciliation query.
• Star schema - fact table of measures plus dimension tables, for reporting rather than transactions.
• Audit columns - created_at/by, updated_at/by on transactional tables; soft delete where retention is required.
• Compliance by design - DPDP Act 2023 (purpose limitation, consent, deletion), GST invoice fields, ABHA/ABDM identifiers.
• Evaluate by query - a design is complete only if the required reports can be written against it.

Formulas and examples

Example 1 - ER to tables, library. Entities BOOK, BOOK_COPY, MEMBER, AUTHOR; relationships BOOK 1:N BOOK_COPY, BOOK M:N AUTHOR, MEMBER 1:N ISSUE, BOOK_COPY 1:N ISSUE.

• 4 entity tables + 1 junction (BOOK_AUTHOR) + 1 table for ISSUE (it carries attributes and participates in two 1:N relationships) = 6 tables.
• The 1:N relationships add no tables - only foreign keys.

-- PostgreSQL dialect throughout (MySQL: INT AUTO_INCREMENT for the ids,
-- and no filtered index, so use a current_issue table in place of ux_open_issue)
CREATE TABLE book (
  book_id      INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  isbn         CHAR(13) UNIQUE,
  title        VARCHAR(200) NOT NULL,
  publisher_id INT REFERENCES publisher(publisher_id)
);
CREATE TABLE book_copy (
  copy_id   INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  book_id   INT NOT NULL REFERENCES book(book_id),
  accession VARCHAR(20) UNIQUE NOT NULL,
  status    VARCHAR(10) NOT NULL CHECK (status IN ('ON_SHELF','ISSUED','LOST','REPAIR'))
);
CREATE TABLE issue (
  issue_id    INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  copy_id     INT NOT NULL REFERENCES book_copy(copy_id),
  member_id   INT NOT NULL REFERENCES member(member_id),
  issue_date  DATE NOT NULL,
  due_date    DATE NOT NULL,
  return_date DATE,
  CHECK (due_date >= issue_date)
);
CREATE UNIQUE INDEX ux_open_issue ON issue(copy_id) WHERE return_date IS NULL;

Example 2 - fine computation and policy table. Policy: 14 days free, ₹2 per day after that, cap ₹200. A member returns on day 61.

• Overdue days = 61 − 14 = 47 → fine = 47 × ₹2 = ₹94 (below the ₹200 cap).
• Returned on day 130: overdue = 116 → 116 × 2 = ₹232 → capped at ₹200.
• In SQL: LEAST(GREATEST(return_date - due_date, 0) * rate, cap_amount) in PostgreSQL, or DATEDIFF(return_date, due_date) in place of the subtraction in MySQL - with rate and cap read from fine_policy for the issue date, so a policy change next year does not rewrite history.

Example 3 - storage estimate, railway seat inventory. 13,000 trains, average 18 coaches, average 70 berths = 13,000 × 18 × 70 ≈ 16.38 million berth rows per journey date. Booking window 60 days.

• Rows = 16.38 million × 60 ≈ 982.8 million.
• At 40 bytes per row: 982.8 × 10⁶ × 40 ≈ 39.3 GB ≈ 36.6 GiB of raw data, before indexes.
• Design response: do not materialise every berth for every date - store a counter per (schedule, class, segment) and materialise berths only on booking; partition by journey date and drop old partitions.

Example 4 - normalising a flat sheet (payroll). Flat table: (emp_id, emp_name, dept_name, month, basic, hra, pf, net), key (emp_id, month).

• Dependencies: emp_id → emp_name, dept_id; dept_id → dept_name; (emp_id, month) → basic, hra, pf, net.
• emp_name and dept_name depend on emp_id alone, a proper part of the key → partial dependency → the table is not even in 2NF; dept_name is in addition transitively dependent through dept_id, which is what 3NF removes.
• 3NF decomposition: employee(emp_id, emp_name, dept_id), department(dept_id, dept_name), payslip(emp_id, month, net), payslip_line(emp_id, month, component_id, amount) - basic, hra and pf are component rows in payslip_line, not repeated columns.
• net is derived - keep it because the payslip is a financial document whose printed figure must be frozen, and reconcile it against the sum of lines.
• PF check: basic ₹18,000, PF at 12% of basic = ₹2,160; if the employer limits contribution to the ₹15,000 wage ceiling, 12% of 15,000 = ₹1,800. The rate and ceiling live in a rate table with validity dates - EPFO ceiling figures have been under revision discussion, so read it from data, never from code.

Example 5 - comparison of specialisation strategies for PERSON → STUDENT, FACULTY, STAFF (10,000 students, 600 faculty, 400 staff):

Where it is used

• IT services and product firms: schema design rounds and data-modelling roles at TCS, Infosys, Wipro, HCLTech, Cognizant, Accenture, Zoho, Freshworks, Zoho/Zoho-style ERP teams.
• Government and public digital systems: IRCTC reservation, UIDAI-linked service portals, GSTN invoice data, state land-record and e-governance databases, university ERPs for Anna University, VTU, SPPU-affiliated colleges.
• Banking, NBFC and fintech: core-banking account, transaction and KYC schemas; UPI and Razorpay/PayU payment ledgers where idempotency keys and immutable ledgers are mandatory.
• Healthcare: HIS/EMR products (Apollo, AIIMS systems, ABDM-integrated apps) where encounter-centric, append-only design and consent artefacts are required.
• Retail and e-commerce: Flipkart/Myntra-style catalogue, SKU, inventory and order schemas; GST-compliant invoicing in Tally, Zoho Books and Marg.
• Roles: database designer, data modeller, backend developer, DBA, data engineer, business analyst writing the requirement-to-entity mapping.

Interview questions

• Q: How do you start designing a database for a new application? A: Collect requirements and the reports needed with volumes, draft an ER model from the nouns and verbs, map it to relations with keys, normalise to 3NF, then do physical design against the actual query list.
• Q: In a library system, why separate BOOK from BOOK_COPY? A: A title has many physical copies; issues, loss and repair apply to a copy while reservation, author and subject apply to the title - merging them duplicates title data per copy.
• Q: Why is seat availability in a railway database not a single seats_left column? A: Availability is per train per journey date per class per route segment, so a Pune-Nagpur booking leaves the berth free on other segments; a single counter cannot represent that.
• Q: Should Aadhaar be the primary key of a citizen table? A: No - it is sensitive regulated data, not always available, and keys should be stable and non-meaningful; use a surrogate id and store Aadhaar (hashed or masked) as a unique nullable column with a stated purpose under the DPDP Act 2023.
• Q: When is denormalisation justified? A: When a measured read pattern cannot meet its target with a normalised schema - and only with a defined maintenance mechanism (trigger, transaction or rebuild job) and a reconciliation check.
• Q: Why store the price on the order line when the product table already has it? A: The order must record the price as sold; the product price changes later, and a re-print of the invoice must show the original amount.
• Q: How do you model a many-to-many relationship with attributes, such as student marks? A: A junction table whose primary key is the pair of foreign keys (or an offering id plus student id), with the attributes - marks, grade, attempt - as columns on that table.
• Q: How would you store a fee structure that changes every year? A: A fee_head master plus a rate table keyed by (fee_head, programme, valid_from, valid_to), so past bills remain reproducible.
• Q: What is the difference between a natural and a surrogate key, and which do you prefer? A: A natural key comes from the data (ISBN, roll number), a surrogate is generated by the system; prefer surrogate for the primary key and enforce the natural key with UNIQUE.
• Q: How do you handle deletes when history must be kept? A: Soft delete with an is_active or deleted_at column plus audit columns, and include the flag in unique indexes; hard delete only where retention law and referential integrity allow it.
• Q: Your reports disagree with the transaction tables. What is the likely design fault? A: A denormalised or cached aggregate with no single owner or reconciliation - updated by some paths and not others.
• Q: How do you decide which columns to index in a new schema? A: From the actual query list - predicates, join columns and sort keys with low selectivity values - balancing read gain against write cost, and verifying with the plan rather than by guesswork.

Common mistakes

• Jumping to tables without an ER diagram, then discovering an M:N relationship after the keys are fixed.
• Creating a separate table for every 1:N relationship, or forcing a junction table where a foreign key suffices.
• Using a meaningful natural key (mobile number, Aadhaar, email, roll number) as the primary key and then having to change it.
• Storing derived values - age, totals, CGPA, availability - with no rebuild or reconciliation mechanism, and no note that they are caches.
• Hardcoding rates, fines, fees, PF and tax percentages in triggers or application code instead of a rate table with validity dates.
• Ignoring the journey date, encounter or validity period, so the design cannot answer "as on which date".
• Declaring foreign keys without choosing referential actions, or omitting them entirely and enforcing integrity only in application code.
• Designing for the forms on screen rather than for the queries and reports the system must answer.

Related topics

ER model and normalisation · SQL queries and joins · Transactions, ACID and indexing · Query processing and optimisation · NoSQL databases · Software engineering: requirement analysis and SRS