Requirements Analysis — Schema Design Problems | OneNoughtOne

0/278

Database Management SystemsSchema Design Problems

Schema Design Problems

LevelAdvanced

Duration90 mins

TopicSchema Design Problems

1 / 5

Requirements Analysis

The Foundation That Determines Everything

Every failed database design can be traced back to a single root cause: inadequate requirements analysis . Before you draw a single entity in an ER diagram, before you write a CREATE TABLE statement, before you consider indexes or normalization—you must first understand what the database needs to accomplish . This isn't merely a procedural nicety. Requirements analysis is the intellectual foundation upon which every subsequent design decision rests. Get it wrong, and you'll build an elegant solution to the wrong problem. Get it right, and the path through ER modeling, normalization, and physical design becomes dramatically clearer.

What You Will Learn

By the end of this page, you will understand how to systematically extract, document, and validate database requirements from business stakeholders. You'll learn to distinguish between functional and non-functional requirements, recognize implicit requirements hidden in business processes, and produce a requirements specification that serves as the authoritative reference for all subsequent design work.

The Cost of Inadequate Requirements

Before diving into methodology, let's establish why requirements analysis deserves such emphasis. Consider the exponential cost curve of database design errors: Requirements phase error : Fix costs 1 unit of effort Design phase error : Fix costs 5-10 units of effort Implementation phase error : Fix costs 20-50 units of effort Production phase error : Fix costs 100-1000 units of effort This asymmetry exists because database schemas are deeply embedded in application code. Changing a table structure after deployment means modifying stored procedures, application queries, ORM mappings, API contracts, and potentially client applications. Migrations must handle existing data without downtime. The ripple effects are enormous.

Common Requirements Failures and Their Consequences
Requirements Gap	Design Consequence	Production Impact
Missing audit trail requirement	No temporal tracking in schema	Regulatory non-compliance, failed audits, legal liability
Underestimated read/write ratio	Schema optimized for updates	Query performance degrades 100x under production load
Ignored multi-tenancy need	No tenant isolation mechanism	Complete re-architecture or security breach risk
Assumed single currency	No currency metadata	International expansion blocked or requires expensive migration
Overlooked soft-delete requirement	No deletion flags or archives	Data loss when users delete 'accidentally'

The Interview Context

In technical interviews, requirements are often deliberately ambiguous. Interviewers want to see you ask clarifying questions . Jumping straight to ER diagrams without gathering requirements is a red flag that signals inexperience. Strong candidates spend 15-20% of their time ensuring they understand the problem before designing.

Types of Database Requirements

Database requirements fall into two fundamental categories, each demanding different elicitation techniques and having distinct impacts on schema design. Functional Requirements Functional requirements describe what data the system must store and what operations it must support . These directly translate into entities, relationships, and constraints in your schema.

Functional Requirement Categories

•Data Storage Requirements — What entities must be stored? What attributes characterize each entity? What are the relationships between entities? Example: 'Store customer information including name, email, phone, and multiple shipping addresses.'
•Data Manipulation Requirements — What CRUD operations are needed? What complex queries must be supported? What business transactions must be atomic? Example: 'Transfer inventory between warehouses while maintaining accurate counts.'
•Data Integrity Requirements — What uniqueness constraints exist? What referential integrity rules apply? What business rules must be enforced at the database level? Example: 'An order can only reference products that are currently active.'
•Data Retrieval Requirements — What reports must be generated? What search capabilities are needed? What aggregations and analytics are required? Example: 'Generate monthly sales reports grouped by region and product category.'

Non-Functional Requirements Non-functional requirements describe quality attributes and operational constraints . These influence physical design decisions, indexing strategies, and architecture choices.

Non-Functional Requirement Categories

•Performance Requirements — Response time expectations, throughput needs, concurrent user counts. Example: 'Product search must return results within 200ms for 95th percentile.'
•Scalability Requirements — Expected data volume growth, user base growth, geographic expansion. Example: 'Support 10 million products and 100 million orders within 3 years.'
•Availability Requirements — Uptime expectations, disaster recovery needs, backup windows. Example: '99.99% availability with maximum 15-minute data loss tolerance.'
•Security Requirements — Access control models, encryption needs, compliance requirements (GDPR, HIPAA, SOX). Example: 'PII must be encrypted at rest; access requires row-level security.'
•Compliance Requirements — Data retention policies, audit requirements, regulatory constraints. Example: 'Financial records must be retained for 7 years with full audit trail.'

The Hidden Third Category: Implicit Requirements

Stakeholders often don't mention requirements they consider 'obvious.' Every e-commerce system needs order history. Every SaaS platform needs user authentication. Part of requirements analysis is surfacing these implicit expectations through systematic questioning and domain knowledge.

Requirements Elicitation Techniques

Extracting requirements from stakeholders is a skill that separates experienced database designers from novices. Stakeholders rarely present requirements in a clean, organized manner. They describe business processes, pain points, and aspirations—your job is to translate these into structured requirements.

Elicitation Techniques and Their Applications
Technique	Description	Best Used For	Output
Stakeholder Interviews	One-on-one conversations with domain experts	Understanding business context and priorities	Business process narratives, priority rankings
Use Case Analysis	Document specific user interactions with the system	Identifying data operations and workflows	Use case diagrams, operation sequences
Document Analysis	Review existing systems, forms, reports, and procedures	Understanding current data and implicit requirements	Data dictionaries, existing schema analysis
Observation/Shadowing	Watch users perform their current processes	Uncovering tacit knowledge and workarounds	Process maps, pain point identification
Prototyping	Build quick mockups to validate understanding	Resolving ambiguity and unstated assumptions	Validated requirements, refined specifications
Workshops/JAD Sessions	Facilitated group sessions with multiple stakeholders	Resolving conflicts and building consensus	Agreed requirements, priority decisions

The CRUD Matrix Technique One of the most effective tools for database-specific requirements analysis is the CRUD Matrix . This systematically maps entities against operations to ensure complete coverage. For each potential entity, ask: Create : Who creates instances? Under what circumstances? What data is provided at creation? Read : Who reads this data? How often? What queries are performed? What joins are common? Update : What fields change over time? Who can modify them? Are updates frequent or rare? Delete : Can items be deleted? Soft or hard delete? What happens to related data?

crud_matrix_template

CRUD Matrix

| Entity        | Create           | Read                 | Update            | Delete           |
|---------------|------------------|----------------------|-------------------|------------------|
| Customer      | Registration     | Profile, Orders,     | Profile updates,  | Account          |
|               | (user-initiated) | Customer Service     | Address changes   | deactivation     |
| Order         | Checkout process | Order history,       | Status changes,   | Cancellation     |
|               | (user-initiated) | Admin, Reports       | Admin corrections | (soft delete)    |
| Product       | Admin/Vendor     | Catalog, Search,     | Inventory,        | Discontinue      |
|               | onboarding       | Recommendations      | Pricing, Details  | (soft delete)    |
| Inventory     | Product creation | Order validation,    | Order fulfillment,| N/A (track       |
|               | or receiving     | Stock reports        | Adjustments       | history)         |
| Payment       | Order checkout   | Order details,       | Status only       | Never (audit     |
|               | (system/gateway) | Reconciliation       | (system-managed)  | requirement)     |

Interview Application

In interview settings, verbally walk through the CRUD matrix for major entities. This demonstrates systematic thinking and often reveals edge cases the interviewer wants you to discover. 'Let me think about the lifecycle of an Order—who creates it, who reads it, when does it change, and can it ever be deleted?'

The Questioning Framework

Experienced database designers follow a structured questioning sequence that progressively deepens understanding. This framework ensures comprehensive coverage while building naturally from simple concepts to complex relationships.

Phase 1: Core Entities

•What are the main 'things' the business tracks? (Products, Customers, Orders, Employees...)
•For each thing: What information do you need to store about it?
•How do you uniquely identify each instance? (Natural key vs surrogate key considerations)
•Are there different 'types' of this entity with different attributes? (Hint at inheritance/subtyping)

Phase 2: Relationships

•How are these entities connected? (Customer places Order, Order contains Products...)
•For each relationship: Is it one-to-one, one-to-many, or many-to-many?
•Is the relationship mandatory or optional on each side?
•Does the relationship itself have attributes? (OrderItem has quantity, price at time of order...)

Phase 3: Business Rules and Constraints

•What combinations of values are valid/invalid?
•What sequences of operations must occur together atomically?
•What happens when related data is deleted or modified?
•Are there limits or quotas that must be enforced?

Phase 4: Time and History

•Which data changes over time? Which is immutable?
•Do you need to know the history of changes? (Audit trail, temporal data)
•What is the typical lifespan of data? (Archival and retention policies)
•How far back do queries typically look? (Hot/warm/cold data patterns)

Phase 5: Volume and Performance

•How many of each entity exist today? In 1 year? In 5 years?
•What is the ratio of reads to writes for each entity?
•What queries are most performance-critical?
•Are there peak loads or seasonal patterns to consider?

The 'And Then What?' Technique

When stakeholders describe a process, keep asking 'And then what happens?' This traces complete workflows and reveals entities and relationships they might not have mentioned upfront. 'A customer places an order.' 'And then what happens?' 'We check inventory.' 'And then what happens?' 'If in stock, we reserve it. If not, we backorder.' Each step reveals data requirements.

Documenting Requirements

Requirements are only valuable if they're documented in a way that can be validated by stakeholders and used by developers. A Database Requirements Specification (DRS) serves as the authoritative reference throughout the design process. Essential Components of a DRS A comprehensive Database Requirements Specification includes the following sections:

DRS Structure

•Executive Summary — High-level purpose, scope, and key stakeholders. Provides context for reviewers.
•Business Context — Description of the domain, existing systems, and business goals driving the database design.
•Entity Catalog — List of all entities with descriptions, key attributes, and estimated volumes.
•Relationship Map — Description of all relationships with cardinality and participation constraints.
•Business Rules — Formal specification of integrity constraints and validation rules.
•Access Patterns — Description of key queries, reports, and data access patterns with frequency and performance expectations.
•Non-Functional Requirements — Performance, scalability, security, compliance, and availability requirements with measurable criteria.
•Data Lifecycle — Retention policies, archival requirements, and data management procedures.
•Glossary — Definition of domain-specific terms to ensure shared understanding.

Entity Documentation Template Each entity should be documented with enough detail to drive schema design. Here's a template for comprehensive entity documentation:

entity_documentation_template
YAML
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
entity: Order
description: >
  Represents a customer's purchase request containing one or more products.
  Created when customer completes checkout. Tracks entire fulfillment lifecycle.
 
business_owner: Sales Department
data_steward: John Smith (john.smith@company.com)
 
attributes:
  - name: order_id
    description: Unique identifier for the order
    type: integer
    required: true
    identifier: true (primary key)
    
  - name: customer_id
    description: Reference to the customer placing the order
    type: integer
    required: true
    foreign_key: Customer.customer_id
    
  - name: order_date
    description: Timestamp when order was placed
    type: timestamp
    required: true
    default: current_timestamp
    
  - name: status
    description: Current state of order fulfillment
    type: enum
    allowed_values: [pending, confirmed, processing, shipped, delivered, cancelled]
    required: true
    default: pending
    
  - name: total_amount
    description: Total order value including tax and shipping
    type: decimal(12,2)
    required: true
    constraint: >= 0
 
relationships:
  - target: Customer
    cardinality: many-to-one
    participation: mandatory (every order must have a customer)
    
  - target: OrderItem
    cardinality: one-to-many
    participation: mandatory (order must have at least one item)
    cascade_delete: true
    
  - target: ShippingAddress
    cardinality: many-to-one
    participation: mandatory
    
  - target: Payment
    cardinality: one-to-many
    participation: optional (may be pending payment)
 
business_rules:
  - "Order cannot be cancelled after status = 'shipped'"
  - "total_amount must equal sum of OrderItem amounts plus tax and shipping"
  - "Order date cannot be in the future"
 
volume_estimates:
  current: 500,000 orders
  annual_growth: 40%
  year_5_projection: 3.8 million orders
  
access_patterns:
  - query: "Orders by customer (recent first)"
    frequency: 50,000/day
    performance_target: <100ms
    
  - query: "Orders by status for fulfillment dashboard"
    frequency: 10,000/day
    performance_target: <200ms
    
  - query: "Orders in date range for reporting"
    frequency: 100/day (batch)
    performance_target: <30s for 6-month range
 
lifecycle:
  retention: 7 years (financial compliance)
  archival: Move to cold storage after 2 years
  deletion: Permanently delete after retention period

Traceability Matters

Number your requirements (REQ-001, REQ-002, etc.) and maintain traceability throughout the design process. When you make a design decision, note which requirements drove that decision. This creates an audit trail that's invaluable during design reviews and when requirements change.

Validating Requirements

Requirements are only useful if they accurately reflect stakeholder needs. Validation ensures your understanding matches reality before you invest significant design effort.

Validation Checklist

•Completeness — Are all entities, relationships, and constraints documented?
•Consistency — Do requirements contradict each other?
•Correctness — Do stakeholders confirm accuracy?
•Feasibility — Can requirements be implemented with available technology?
•Testability — Can each requirement be verified?
•Necessity — Is each requirement actually needed?

Common Validation Failures

•Assumed understanding — Never validated with business users
•Single-source bias — Only gathered from IT, not business owners
•Happy path only — Didn't explore error cases and exceptions
•Static view — Ignored how data and access patterns evolve
•Technical projection — Assumed technical constraints that don't exist

Validation Techniques Requirement Reviews : Walk through the DRS with stakeholders, explaining each requirement and asking for confirmation. Pay attention to hesitation or confusion—these signal misunderstanding. Scenario Walkthroughs : Create realistic business scenarios and trace how the database would support them. 'A customer returns an item from a split shipment. Walk me through what data changes occur.' This reveals gaps. Prototype Queries : Write sample SQL queries for key access patterns and review with stakeholders. 'Here's how we'd generate that monthly report. Does this show the data you need?' Edge Case Exploration : Explicitly explore boundary conditions. What's the maximum order size? Can a customer have zero addresses? What happens to orphaned order items? These edge cases often reveal unstated requirements.

The Validation Mindset

Approach validation with the goal of finding errors, not confirming your work. Ask stakeholders: 'What did I get wrong?' rather than 'Does this look right?' The former invites critique; the latter invites polite agreement.

Interview Case Study: E-Commerce Platform

Let's apply requirements analysis to a typical interview scenario. The interviewer says: "Design a database for an e-commerce platform." An inexperienced candidate immediately starts drawing entities. A skilled candidate pauses and asks clarifying questions.

interview_dialogue
Interview Dialogue
CANDIDATE: Before I design the schema, I'd like to understand the requirements better.
          What type of e-commerce is this? B2C, B2B, marketplace?
 
INTERVIEWER: B2C, direct to consumer. We sell physical products.
 
CANDIDATE: Do you have a single warehouse or multiple? Do products ship from 
          different locations?
 
INTERVIEWER: Currently single warehouse, but we're planning to expand to 3.
 
CANDIDATE: That's important—we'll need location-aware inventory. Do you offer 
          different shipping options with different pricing?
 
INTERVIEWER: Yes, standard and express. Express uses different carriers.
 
CANDIDATE: For products, do you have variants—like size and color for clothing?
 
INTERVIEWER: Yes, products have multiple variants with different SKUs and inventory.
 
CANDIDATE: Do prices ever change? Do we need historical pricing for orders?
 
INTERVIEWER: Prices change frequently. Yes, we need to know what price was charged.
 
CANDIDATE: What about promotions and discounts? Coupons? Bundle pricing?
 
INTERVIEWER: We have coupon codes and occasional percentage discounts.
 
CANDIDATE: For customers, do you need to support multiple addresses? Guest checkout?
 
INTERVIEWER: Yes to multiple addresses. We require account creation, no guest checkout.
 
CANDIDATE: What are your approximate volumes? Orders per day? Product catalog size?
 
INTERVIEWER: About 5,000 orders/day peak, 50,000 products, 200,000 variants.
 
CANDIDATE: Any special reporting needs? What are the most common queries?
 
INTERVIEWER: Product search is critical. Also real-time inventory and order status.
 
CANDIDATE: Are there compliance requirements? PCI for payment data? GDPR?
 
INTERVIEWER: We use a third-party payment processor, so just store reference IDs.
          We do need GDPR compliance for EU customers.

After this dialogue, the candidate has gathered enough information to make informed design decisions: Key Insights Revealed: Multi-location inventory (future requirement affecting current design) Product variant model needed (not simple single-product) Historical pricing required (time-variant data pattern) Moderate scale (not massive, but significant) Search is critical (indexing strategy important) GDPR compliance (may need data isolation or deletion support) The candidate can now proceed with confidence, and the interviewer sees systematic, professional thinking.

Time Management in Interviews

In a 45-minute design interview, spend 8-10 minutes on requirements clarification. This investment pays dividends by preventing costly redesigns mid-interview. If an interviewer seems impatient, say: 'I want to make sure I understand the requirements so I design for your actual needs, not assumptions. Just a few more questions.'

Database Management SystemsSchema Design Problems

Schema Design Problems

LevelAdvanced

Duration90 mins

TopicSchema Design Problems

1 / 5

Requirements Analysis

The Foundation That Determines Everything

Every failed database design can be traced back to a single root cause: inadequate requirements analysis . Before you draw a single entity in an ER diagram, before you write a CREATE TABLE statement, before you consider indexes or normalization—you must first understand what the database needs to accomplish . This isn't merely a procedural nicety. Requirements analysis is the intellectual foundation upon which every subsequent design decision rests. Get it wrong, and you'll build an elegant solution to the wrong problem. Get it right, and the path through ER modeling, normalization, and physical design becomes dramatically clearer.

What You Will Learn

By the end of this page, you will understand how to systematically extract, document, and validate database requirements from business stakeholders. You'll learn to distinguish between functional and non-functional requirements, recognize implicit requirements hidden in business processes, and produce a requirements specification that serves as the authoritative reference for all subsequent design work.

The Cost of Inadequate Requirements

Before diving into methodology, let's establish why requirements analysis deserves such emphasis. Consider the exponential cost curve of database design errors: Requirements phase error : Fix costs 1 unit of effort Design phase error : Fix costs 5-10 units of effort Implementation phase error : Fix costs 20-50 units of effort Production phase error : Fix costs 100-1000 units of effort This asymmetry exists because database schemas are deeply embedded in application code. Changing a table structure after deployment means modifying stored procedures, application queries, ORM mappings, API contracts, and potentially client applications. Migrations must handle existing data without downtime. The ripple effects are enormous.

Common Requirements Failures and Their Consequences
Requirements Gap	Design Consequence	Production Impact
Missing audit trail requirement	No temporal tracking in schema	Regulatory non-compliance, failed audits, legal liability
Underestimated read/write ratio	Schema optimized for updates	Query performance degrades 100x under production load
Ignored multi-tenancy need	No tenant isolation mechanism	Complete re-architecture or security breach risk
Assumed single currency	No currency metadata	International expansion blocked or requires expensive migration
Overlooked soft-delete requirement	No deletion flags or archives	Data loss when users delete 'accidentally'

The Interview Context

In technical interviews, requirements are often deliberately ambiguous. Interviewers want to see you ask clarifying questions . Jumping straight to ER diagrams without gathering requirements is a red flag that signals inexperience. Strong candidates spend 15-20% of their time ensuring they understand the problem before designing.

Types of Database Requirements

Database requirements fall into two fundamental categories, each demanding different elicitation techniques and having distinct impacts on schema design. Functional Requirements Functional requirements describe what data the system must store and what operations it must support . These directly translate into entities, relationships, and constraints in your schema.

Functional Requirement Categories

•Data Storage Requirements — What entities must be stored? What attributes characterize each entity? What are the relationships between entities? Example: 'Store customer information including name, email, phone, and multiple shipping addresses.'
•Data Manipulation Requirements — What CRUD operations are needed? What complex queries must be supported? What business transactions must be atomic? Example: 'Transfer inventory between warehouses while maintaining accurate counts.'
•Data Integrity Requirements — What uniqueness constraints exist? What referential integrity rules apply? What business rules must be enforced at the database level? Example: 'An order can only reference products that are currently active.'
•Data Retrieval Requirements — What reports must be generated? What search capabilities are needed? What aggregations and analytics are required? Example: 'Generate monthly sales reports grouped by region and product category.'

Non-Functional Requirements Non-functional requirements describe quality attributes and operational constraints . These influence physical design decisions, indexing strategies, and architecture choices.

Non-Functional Requirement Categories

•Performance Requirements — Response time expectations, throughput needs, concurrent user counts. Example: 'Product search must return results within 200ms for 95th percentile.'
•Scalability Requirements — Expected data volume growth, user base growth, geographic expansion. Example: 'Support 10 million products and 100 million orders within 3 years.'
•Availability Requirements — Uptime expectations, disaster recovery needs, backup windows. Example: '99.99% availability with maximum 15-minute data loss tolerance.'
•Security Requirements — Access control models, encryption needs, compliance requirements (GDPR, HIPAA, SOX). Example: 'PII must be encrypted at rest; access requires row-level security.'
•Compliance Requirements — Data retention policies, audit requirements, regulatory constraints. Example: 'Financial records must be retained for 7 years with full audit trail.'

The Hidden Third Category: Implicit Requirements

Stakeholders often don't mention requirements they consider 'obvious.' Every e-commerce system needs order history. Every SaaS platform needs user authentication. Part of requirements analysis is surfacing these implicit expectations through systematic questioning and domain knowledge.

Requirements Elicitation Techniques

Extracting requirements from stakeholders is a skill that separates experienced database designers from novices. Stakeholders rarely present requirements in a clean, organized manner. They describe business processes, pain points, and aspirations—your job is to translate these into structured requirements.

Elicitation Techniques and Their Applications
Technique	Description	Best Used For	Output
Stakeholder Interviews	One-on-one conversations with domain experts	Understanding business context and priorities	Business process narratives, priority rankings
Use Case Analysis	Document specific user interactions with the system	Identifying data operations and workflows	Use case diagrams, operation sequences
Document Analysis	Review existing systems, forms, reports, and procedures	Understanding current data and implicit requirements	Data dictionaries, existing schema analysis
Observation/Shadowing	Watch users perform their current processes	Uncovering tacit knowledge and workarounds	Process maps, pain point identification
Prototyping	Build quick mockups to validate understanding	Resolving ambiguity and unstated assumptions	Validated requirements, refined specifications
Workshops/JAD Sessions	Facilitated group sessions with multiple stakeholders	Resolving conflicts and building consensus	Agreed requirements, priority decisions

The CRUD Matrix Technique One of the most effective tools for database-specific requirements analysis is the CRUD Matrix . This systematically maps entities against operations to ensure complete coverage. For each potential entity, ask: Create : Who creates instances? Under what circumstances? What data is provided at creation? Read : Who reads this data? How often? What queries are performed? What joins are common? Update : What fields change over time? Who can modify them? Are updates frequent or rare? Delete : Can items be deleted? Soft or hard delete? What happens to related data?

crud_matrix_template

CRUD Matrix

| Entity        | Create           | Read                 | Update            | Delete           |
|---------------|------------------|----------------------|-------------------|------------------|
| Customer      | Registration     | Profile, Orders,     | Profile updates,  | Account          |
|               | (user-initiated) | Customer Service     | Address changes   | deactivation     |
| Order         | Checkout process | Order history,       | Status changes,   | Cancellation     |
|               | (user-initiated) | Admin, Reports       | Admin corrections | (soft delete)    |
| Product       | Admin/Vendor     | Catalog, Search,     | Inventory,        | Discontinue      |
|               | onboarding       | Recommendations      | Pricing, Details  | (soft delete)    |
| Inventory     | Product creation | Order validation,    | Order fulfillment,| N/A (track       |
|               | or receiving     | Stock reports        | Adjustments       | history)         |
| Payment       | Order checkout   | Order details,       | Status only       | Never (audit     |
|               | (system/gateway) | Reconciliation       | (system-managed)  | requirement)     |

Interview Application

In interview settings, verbally walk through the CRUD matrix for major entities. This demonstrates systematic thinking and often reveals edge cases the interviewer wants you to discover. 'Let me think about the lifecycle of an Order—who creates it, who reads it, when does it change, and can it ever be deleted?'

The Questioning Framework

Experienced database designers follow a structured questioning sequence that progressively deepens understanding. This framework ensures comprehensive coverage while building naturally from simple concepts to complex relationships.

Phase 1: Core Entities

•What are the main 'things' the business tracks? (Products, Customers, Orders, Employees...)
•For each thing: What information do you need to store about it?
•How do you uniquely identify each instance? (Natural key vs surrogate key considerations)
•Are there different 'types' of this entity with different attributes? (Hint at inheritance/subtyping)

Phase 2: Relationships

•How are these entities connected? (Customer places Order, Order contains Products...)
•For each relationship: Is it one-to-one, one-to-many, or many-to-many?
•Is the relationship mandatory or optional on each side?
•Does the relationship itself have attributes? (OrderItem has quantity, price at time of order...)

Phase 3: Business Rules and Constraints

•What combinations of values are valid/invalid?
•What sequences of operations must occur together atomically?
•What happens when related data is deleted or modified?
•Are there limits or quotas that must be enforced?

Phase 4: Time and History

•Which data changes over time? Which is immutable?
•Do you need to know the history of changes? (Audit trail, temporal data)
•What is the typical lifespan of data? (Archival and retention policies)
•How far back do queries typically look? (Hot/warm/cold data patterns)

Phase 5: Volume and Performance

•How many of each entity exist today? In 1 year? In 5 years?
•What is the ratio of reads to writes for each entity?
•What queries are most performance-critical?
•Are there peak loads or seasonal patterns to consider?

The 'And Then What?' Technique

When stakeholders describe a process, keep asking 'And then what happens?' This traces complete workflows and reveals entities and relationships they might not have mentioned upfront. 'A customer places an order.' 'And then what happens?' 'We check inventory.' 'And then what happens?' 'If in stock, we reserve it. If not, we backorder.' Each step reveals data requirements.

Documenting Requirements

Requirements are only valuable if they're documented in a way that can be validated by stakeholders and used by developers. A Database Requirements Specification (DRS) serves as the authoritative reference throughout the design process. Essential Components of a DRS A comprehensive Database Requirements Specification includes the following sections:

DRS Structure

•Executive Summary — High-level purpose, scope, and key stakeholders. Provides context for reviewers.
•Business Context — Description of the domain, existing systems, and business goals driving the database design.
•Entity Catalog — List of all entities with descriptions, key attributes, and estimated volumes.
•Relationship Map — Description of all relationships with cardinality and participation constraints.
•Business Rules — Formal specification of integrity constraints and validation rules.
•Access Patterns — Description of key queries, reports, and data access patterns with frequency and performance expectations.
•Non-Functional Requirements — Performance, scalability, security, compliance, and availability requirements with measurable criteria.
•Data Lifecycle — Retention policies, archival requirements, and data management procedures.
•Glossary — Definition of domain-specific terms to ensure shared understanding.

Entity Documentation Template Each entity should be documented with enough detail to drive schema design. Here's a template for comprehensive entity documentation:

entity_documentation_template
YAML
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
entity: Order
description: >
  Represents a customer's purchase request containing one or more products.
  Created when customer completes checkout. Tracks entire fulfillment lifecycle.
 
business_owner: Sales Department
data_steward: John Smith (john.smith@company.com)
 
attributes:
  - name: order_id
    description: Unique identifier for the order
    type: integer
    required: true
    identifier: true (primary key)
    
  - name: customer_id
    description: Reference to the customer placing the order
    type: integer
    required: true
    foreign_key: Customer.customer_id
    
  - name: order_date
    description: Timestamp when order was placed
    type: timestamp
    required: true
    default: current_timestamp
    
  - name: status
    description: Current state of order fulfillment
    type: enum
    allowed_values: [pending, confirmed, processing, shipped, delivered, cancelled]
    required: true
    default: pending
    
  - name: total_amount
    description: Total order value including tax and shipping
    type: decimal(12,2)
    required: true
    constraint: >= 0
 
relationships:
  - target: Customer
    cardinality: many-to-one
    participation: mandatory (every order must have a customer)
    
  - target: OrderItem
    cardinality: one-to-many
    participation: mandatory (order must have at least one item)
    cascade_delete: true
    
  - target: ShippingAddress
    cardinality: many-to-one
    participation: mandatory
    
  - target: Payment
    cardinality: one-to-many
    participation: optional (may be pending payment)
 
business_rules:
  - "Order cannot be cancelled after status = 'shipped'"
  - "total_amount must equal sum of OrderItem amounts plus tax and shipping"
  - "Order date cannot be in the future"
 
volume_estimates:
  current: 500,000 orders
  annual_growth: 40%
  year_5_projection: 3.8 million orders
  
access_patterns:
  - query: "Orders by customer (recent first)"
    frequency: 50,000/day
    performance_target: <100ms
    
  - query: "Orders by status for fulfillment dashboard"
    frequency: 10,000/day
    performance_target: <200ms
    
  - query: "Orders in date range for reporting"
    frequency: 100/day (batch)
    performance_target: <30s for 6-month range
 
lifecycle:
  retention: 7 years (financial compliance)
  archival: Move to cold storage after 2 years
  deletion: Permanently delete after retention period

Traceability Matters

Number your requirements (REQ-001, REQ-002, etc.) and maintain traceability throughout the design process. When you make a design decision, note which requirements drove that decision. This creates an audit trail that's invaluable during design reviews and when requirements change.

Validating Requirements

Requirements are only useful if they accurately reflect stakeholder needs. Validation ensures your understanding matches reality before you invest significant design effort.

Validation Checklist

•Completeness — Are all entities, relationships, and constraints documented?
•Consistency — Do requirements contradict each other?
•Correctness — Do stakeholders confirm accuracy?
•Feasibility — Can requirements be implemented with available technology?
•Testability — Can each requirement be verified?
•Necessity — Is each requirement actually needed?

Common Validation Failures

•Assumed understanding — Never validated with business users
•Single-source bias — Only gathered from IT, not business owners
•Happy path only — Didn't explore error cases and exceptions
•Static view — Ignored how data and access patterns evolve
•Technical projection — Assumed technical constraints that don't exist

Validation Techniques Requirement Reviews : Walk through the DRS with stakeholders, explaining each requirement and asking for confirmation. Pay attention to hesitation or confusion—these signal misunderstanding. Scenario Walkthroughs : Create realistic business scenarios and trace how the database would support them. 'A customer returns an item from a split shipment. Walk me through what data changes occur.' This reveals gaps. Prototype Queries : Write sample SQL queries for key access patterns and review with stakeholders. 'Here's how we'd generate that monthly report. Does this show the data you need?' Edge Case Exploration : Explicitly explore boundary conditions. What's the maximum order size? Can a customer have zero addresses? What happens to orphaned order items? These edge cases often reveal unstated requirements.

The Validation Mindset

Approach validation with the goal of finding errors, not confirming your work. Ask stakeholders: 'What did I get wrong?' rather than 'Does this look right?' The former invites critique; the latter invites polite agreement.

Interview Case Study: E-Commerce Platform

Let's apply requirements analysis to a typical interview scenario. The interviewer says: "Design a database for an e-commerce platform." An inexperienced candidate immediately starts drawing entities. A skilled candidate pauses and asks clarifying questions.

interview_dialogue
Interview Dialogue
CANDIDATE: Before I design the schema, I'd like to understand the requirements better.
          What type of e-commerce is this? B2C, B2B, marketplace?
 
INTERVIEWER: B2C, direct to consumer. We sell physical products.
 
CANDIDATE: Do you have a single warehouse or multiple? Do products ship from 
          different locations?
 
INTERVIEWER: Currently single warehouse, but we're planning to expand to 3.
 
CANDIDATE: That's important—we'll need location-aware inventory. Do you offer 
          different shipping options with different pricing?
 
INTERVIEWER: Yes, standard and express. Express uses different carriers.
 
CANDIDATE: For products, do you have variants—like size and color for clothing?
 
INTERVIEWER: Yes, products have multiple variants with different SKUs and inventory.
 
CANDIDATE: Do prices ever change? Do we need historical pricing for orders?
 
INTERVIEWER: Prices change frequently. Yes, we need to know what price was charged.
 
CANDIDATE: What about promotions and discounts? Coupons? Bundle pricing?
 
INTERVIEWER: We have coupon codes and occasional percentage discounts.
 
CANDIDATE: For customers, do you need to support multiple addresses? Guest checkout?
 
INTERVIEWER: Yes to multiple addresses. We require account creation, no guest checkout.
 
CANDIDATE: What are your approximate volumes? Orders per day? Product catalog size?
 
INTERVIEWER: About 5,000 orders/day peak, 50,000 products, 200,000 variants.
 
CANDIDATE: Any special reporting needs? What are the most common queries?
 
INTERVIEWER: Product search is critical. Also real-time inventory and order status.
 
CANDIDATE: Are there compliance requirements? PCI for payment data? GDPR?
 
INTERVIEWER: We use a third-party payment processor, so just store reference IDs.
          We do need GDPR compliance for EU customers.

After this dialogue, the candidate has gathered enough information to make informed design decisions: Key Insights Revealed: Multi-location inventory (future requirement affecting current design) Product variant model needed (not simple single-product) Historical pricing required (time-variant data pattern) Moderate scale (not massive, but significant) Search is critical (indexing strategy important) GDPR compliance (may need data isolation or deletion support) The candidate can now proceed with confidence, and the interviewer sees systematic, professional thinking.

Time Management in Interviews

In a 45-minute design interview, spend 8-10 minutes on requirements clarification. This investment pays dividends by preventing costly redesigns mid-interview. If an interviewer seems impatient, say: 'I want to make sure I understand the requirements so I design for your actual needs, not assumptions. Just a few more questions.'

Requirements Analysis

The Foundation That Determines Everything

The Cost of Inadequate Requirements

Types of Database Requirements

Functional Requirements

Non-Functional Requirements

Requirements Elicitation Techniques

The CRUD Matrix Technique

The Questioning Framework

Phase 1: Core Entities

Phase 2: Relationships

Phase 3: Business Rules and Constraints

Phase 4: Time and History

Phase 5: Volume and Performance

Documenting Requirements

Essential Components of a DRS

Entity Documentation Template

Validating Requirements

Validation Techniques

Interview Case Study: E-Commerce Platform

There’s more to understand.

Requirements Analysis

The Foundation That Determines Everything

The Cost of Inadequate Requirements

Types of Database Requirements

Functional Requirements

Non-Functional Requirements

Requirements Elicitation Techniques

The CRUD Matrix Technique

The Questioning Framework

Phase 1: Core Entities

Phase 2: Relationships

Phase 3: Business Rules and Constraints

Phase 4: Time and History

Phase 5: Volume and Performance

Documenting Requirements

Essential Components of a DRS

Entity Documentation Template

Validating Requirements

Validation Techniques

Interview Case Study: E-Commerce Platform

There’s more to understand.