Requirements Gathering for Database Design
Functional Requirements: Defining What the Database Must Do
Read aloud is unavailable in this browser.
Beyond Storage: The Active Database
Data requirements tell us what to store. Functional requirements tell us what to do with that data—the operations, transactions, queries, and reports that the database must support.
A database that perfectly stores data but cannot efficiently support required operations is a failure. Consider an e-commerce database: it's not enough to store orders. The database must support creating orders, modifying orders, canceling orders, querying order history, calculating totals, generating shipping manifests, and producing sales reports—all with required performance characteristics.
Functional requirements bridge the gap between static data models and dynamic business processes. They ensure that the database design not only captures the right information but enables the right actions.
By the end of this page, you will understand how to specify functional requirements for database design. You'll learn to identify and document required operations, transactions, queries, and reports. You'll understand how functional requirements influence database design decisions like indexing, denormalization, and access patterns.
Understanding Functional Requirements for Databases
Functional requirements describe behaviors that the database must support. Unlike data requirements (which are structural), functional requirements are operational—they describe what happens when users and applications interact with the database.
Categories of Functional Requirements:
| Category | Description | Examples |
|---|---|---|
| Data Operations (CRUD) | Create, Read, Update, Delete operations on entities | Create new customer, Update order status, Delete inactive product |
| Transactions | Multi-step operations that must complete atomically | Transfer funds (debit + credit), Place order (create order + reduce inventory) |
| Queries | Information retrieval requirements | Find orders by customer, Search products by keyword, List overdue invoices |
| Reports | Formatted output summarizing data | Monthly sales summary, Inventory valuation, Customer aging report |
| Calculations/Derivations | Computed values the system must produce | Order total, Average order value, Days since last purchase |
| Workflow/Business Logic | Rules governing state changes and processes | Order approval workflow, Escalation procedures, Notification triggers |
| Integration Operations | Data exchange with external systems | Import supplier catalog, Export transactions to accounting, Sync with CRM |
Why Functional Requirements Matter for Database Design:
Functional requirements directly influence database design decisions:
Query Patterns → Index Strategy: If users frequently search products by name, the Product table needs an index on ProductName. If orders are always retrieved by customer, an index on CustomerID is essential.
Update Frequency → Normalization Decisions: Highly updated data should be normalized to avoid update anomalies. Infrequently changed data accessed together might benefit from denormalization.
Transaction Scope → Table Relationships: Operations that must be atomic influence how tables are related and what constraints are enforced.
Reporting Needs → Aggregation Strategy: Frequent aggregate reports (sums, counts, averages) may require materialized views or pre-calculated summary tables.
Concurrency Patterns → Locking Strategy: High-concurrency operations on the same data require careful transaction isolation design.
A database designed without considering functional requirements often requires significant rework. Indexes must be added, structures denormalized, or even schemas redesigned to support operations that weren't anticipated. Capturing functional requirements early prevents expensive retrofitting.
Specifying CRUD Operations
CRUD operations—Create, Read, Update, Delete—are the fundamental interactions with any database. For each entity identified in data requirements, specify what CRUD operations are required.
CRUD Specification Template:
For each entity, document:
- What operations are allowed?
- Who can perform each operation?
- What are the preconditions and postconditions?
- What happens to related entities?
- What validations must occur?
| Operation | Description | Authorization | Business Rules |
|---|---|---|---|
| Create | Create a new order for a customer | Customers, Sales staff | Customer must be active; Credit check for orders > $5000; At least one order item required |
| Read | Retrieve order details | Order owner, Sales staff, Managers | Customers see only their orders; Staff see orders in their territory |
| Update | Modify order before shipment | Order owner (limited), Sales staff | Cannot modify shipped orders; Quantity changes recalculate totals; Log all changes |
| Delete | Cancel/remove order | Managers only | Shipped orders cannot be deleted; Cancelled orders retained with status change; Restore inventory |
Detailing Create Operations:
Create operations are often more complex than simple inserts:
- Required Fields — What must be provided vs. can be defaulted?
- Validations — What checks must pass before creation?
- Generated Values — What values are system-generated (IDs, timestamps)?
- Related Records — What related records must be created simultaneously?
- Initial State — What is the default status/state for new records?
Detailing Update Operations:
- Updatable Fields — Which attributes can be changed and which are immutable?
- State Transitions — What status changes are valid? (Pending→Approved, not Shipped→Pending)
- Cascade Effects — What happens to related records when a parent changes?
- Change Tracking — Must updates be logged for audit purposes?
- Optimistic/Pessimistic Locking — How are concurrent updates handled?
Detailing Delete Operations:
- Soft vs. Hard Delete — Is data physically removed or just marked inactive?
- Cascade Behavior — What happens to related records? Cascade delete? Prevent delete? Set null?
- Retention Requirements — Must deleted data be archived? For how long?
- Restore Capability — Can deleted records be recovered?
Many business applications never truly delete data. Instead, records are marked with a status (IsDeleted, IsActive) and a deletion timestamp. This supports audit requirements, enables recovery, and maintains referential integrity. During requirements, clarify whether the organization needs true deletion or soft deletion for each entity.
Specifying Transaction Requirements
Transactions are sequences of operations that must succeed or fail as a unit. They ensure data consistency even when complex, multi-step processes are involved.
Identifying Transactions:
Look for business processes where:
- Multiple records must be updated together
- Failure partway through would leave data inconsistent
- 'All or nothing' behavior is required
- Financial or legal compliance requires atomicity
- •Financial Transfers — Debit one account AND credit another; both must succeed or neither
- •Order Placement — Create order, create order items, reduce inventory, create invoice; all must succeed together
- •Employee Transfer — Update employee department, update old department headcount, update new department headcount
- •Inventory Adjustments — Record adjustment, update on-hand quantity, create audit record
- •Appointment Booking — Create appointment, mark timeslot as booked, send confirmation; atomic to prevent double-booking
Transaction Specification Elements:
For each transaction, document:
| Element | Description | Example |
|---|---|---|
| Transaction Name | Descriptive identifier | PlaceOrder |
| Initiating Event | What triggers the transaction | Customer clicks 'Submit Order' |
| Preconditions | Required state before starting | Customer logged in; Cart not empty; Items in stock |
| Steps | Sequence of operations |
|
| Postconditions | Expected state after completion | Order created; Inventory reduced; Payment captured |
| Rollback Behavior | What happens on failure | All changes reversed; Cart preserved; Customer notified |
| Isolation Level | Concurrency requirements | Serializable (inventory must not be oversold) |
ACID Properties:
Transactions should maintain ACID properties:
- Atomicity — All or nothing; partial completion is not allowed
- Consistency — Database moves from one valid state to another
- Isolation — Concurrent transactions don't interfere
- Durability — Committed changes persist even after system failure
Some business processes span hours or days (order fulfillment, loan approval). These cannot hold database locks for the entire duration. Document which processes are long-running and require saga patterns or compensating transactions rather than traditional ACID transactions.
Specifying Query Requirements
Query requirements define how users and applications will retrieve data. They are critical for database design because they determine access patterns, required indexes, and potential denormalization needs.
Query Classification:
| Query Type | Characteristics | Design Implications |
|---|---|---|
| Point Queries | Retrieve specific records by key (Get customer 12345) | Primary key lookup; Very fast; No indexing concerns |
| Range Queries | Retrieve records within a range (Orders from Jan-Mar) | Range indexes on date/value columns; Consider partitioning |
| Search Queries | Find records matching criteria (Products containing 'wireless') | Text indexes; Full-text search capabilities; LIKE performance |
| Join Queries | Combine data from multiple entities (Order with Customer and Items) | Foreign key indexes; Denormalization for performance |
| Aggregate Queries | Compute summaries (Total sales by region) | Pre-aggregation; Materialized views; Indexing group-by columns |
| Analytical Queries | Complex analysis (Year-over-year growth by category) | OLAP structures; Star schemas; Specialized analytical databases |
Query Requirement Specification:
For each significant query, document:
QUERY: Find Customer Orders
Description:
Retrieve all orders for a specific customer, including order
details and line items, sorted by order date (newest first).
Input Parameters:
- CustomerID (required): The customer whose orders to retrieve
- DateFrom (optional): Filter to orders on or after this date
- DateTo (optional): Filter to orders on or before this date
- Status (optional): Filter by order status
Output:
- Order.OrderID, Order.OrderDate, Order.TotalAmount, Order.Status
- For each order: OrderItem.ProductName, OrderItem.Quantity,
OrderItem.UnitPrice, OrderItem.LineTotal
Expected Volume:
- Average customer has 15 orders
- Peak: power users may have 500+ orders
Performance Requirement:
- Response time: < 500ms for typical case; < 2s for worst case
Frequency:
- ~10,000 executions per day
Access Pattern:
- Called when customer logs in, views order history
- Critical path for customer service lookup
- •What data is needed? — List all fields/entities required in the output
- •What filters are applied? — List all selection criteria (WHERE conditions)
- •What sorting is required? — Default and optional sort orders
- •What aggregations? — Sums, counts, averages, groupings
- •How much data? — Expected result set size (10 records? 10,000?)
- •How often executed? — Frequency determines optimization priority
- •What performance is required? — Maximum acceptable response time
- •Who executes it? — Determines visibility/authorization requirements
Watch for queries that could return unbounded result sets: 'List all customers' or 'Show all transactions.' These may work in development with 100 records but fail in production with millions. Require pagination, date ranges, or mandatory filters for potentially large queries.
Specifying Reporting Requirements
Reports are formatted outputs that present data for analysis, decision-making, or compliance. Unlike ad-hoc queries, reports typically have fixed formats, regular schedules, and defined audiences.
Report Requirement Elements:
| Element | Description | Example |
|---|---|---|
| Report Name | Clear, descriptive title | Monthly Sales by Region |
| Purpose | Business objective served | Track regional performance against targets |
| Audience | Who receives/uses the report | Regional Managers, VP Sales |
| Frequency | How often generated | Monthly, on 3rd business day |
| Parameters | User-selectable filters | Year, Month, Region (optional) |
| Data Source Entities | Which tables/entities provide data | Orders, Customers, Products, Regions |
| Metrics/Calculations | Values computed for the report | Total Sales, Count of Orders, Average Order Value |
| Groupings | How data is organized | By Region, then by Product Category |
| Sorting | Order of presentation | Total Sales descending |
| Comparisons | Comparative data included | Same month previous year; Target values |
| Drill-down | Ability to see detail | Click region to see individual orders |
| Distribution | How delivered | Email PDF to managers; Dashboard for executives |
Report Categories:
Operational Reports: Support day-to-day operations. High frequency, detailed, current data.
- Daily shipment manifest
- Exception reports (overdue invoices, low stock)
- Work queue reports
Tactical Reports: Support short-term management decisions. Regular frequency, summarized, recent data.
- Weekly sales summary
- Monthly performance dashboards
- Project status reports
Strategic Reports: Support long-term planning. Lower frequency, highly aggregated, historical trends.
- Quarterly business review
- Annual performance analysis
- Multi-year trend analysis
Compliance Reports: Meet regulatory or audit requirements. Specific formats and content mandated externally.
- Tax filings
- Regulatory submissions
- Audit trail reports
- •Historical Data Retention — Reports spanning long periods require corresponding data retention policies
- •Pre-Aggregation — Frequently-run summary reports may benefit from materialized views or summary tables
- •Snapshot Requirements — Some reports need point-in-time data (month-end balances), requiring snapshot architecture
- •Denormalization — Reports joining many tables may require denormalized structures for performance
- •Report Database — High-volume reporting may justify a separate reporting database or data warehouse
Organizations often accumulate hundreds of reports—many redundant, outdated, or rarely used. Part of requirements gathering is identifying which reports are truly needed, which can be consolidated, and which should be retired. Don't design for reports nobody uses.
Calculation and Derivation Requirements
Many data values are not stored directly but derived from other data. Calculation requirements specify what computations the database or applications must perform.
Types of Derived Data:
| Type | Description | Examples |
|---|---|---|
| Simple Calculations | Arithmetic operations on stored values | LineTotal = Quantity × UnitPrice; TaxAmount = Subtotal × TaxRate |
| Aggregations | Summaries across multiple records | OrderTotal = SUM(OrderItem.LineTotal); CustomerOrderCount = COUNT(Orders) |
| Temporal Derivations | Based on dates/times | Age = Today - BirthDate; DaysOverdue = Today - DueDate |
| Status Derivations | Derived from other conditions | AccountStatus = 'Overdue' if any invoice > 30 days past due |
| Complex Formulas | Multi-factor calculations | CreditScore = f(PaymentHistory, OutstandingBalance, AccountAge, ...) |
| Rank/Position | Relative standing | SalesRank = position when sorted by total sales |
Store vs. Calculate Decision:
For each derived value, decide whether to:
Calculate on demand:
- Pro: Always current; no update maintenance; saves storage
- Con: Calculation overhead on every access; complex queries
- Best for: Simple calculations; rapidly changing source data; infrequent access
Store pre-calculated:
- Pro: Fast retrieval; simplifies queries; enables indexing on derived values
- Con: Must maintain when source data changes; potential staleness; uses storage
- Best for: Complex calculations; frequent access; historical snapshots needed
Calculation Requirement Specification:
DERIVATION: Customer Lifetime Value (CLV)
Formula:
CLV = Total of all OrderItem.LineTotal for all Orders
where Order.CustomerID = Customer.CustomerID
and Order.Status != 'Cancelled'
Source Fields:
- OrderItem.Quantity, OrderItem.UnitPrice
- Order.CustomerID, Order.Status
Update Trigger:
- When OrderItem is created, updated, or deleted
- When Order status changes to/from 'Cancelled'
Storage Decision:
- Store in Customer.LifetimeValue (updated by trigger)
- Rationale: Frequently displayed; expensive to calculate each time
Performance Note:
- Update can be incremental (add/subtract change)
rather than full recalculation
Document calculation precision requirements explicitly. Financial calculations typically require exact decimal arithmetic, not floating-point. Tax calculations may have specific rounding rules (round half up, round to nearest cent). Timing of rounding (per line item vs. on total) affects results.
Business Logic and Workflow Requirements
Business logic encompasses the rules, validations, and process flows that govern how data changes. While some business logic resides in applications, databases often enforce critical rules through constraints, triggers, and stored procedures.
Categories of Business Logic:
- •Validation Rules — Checks that data meets requirements before accepting it. Example: OrderQuantity must be positive; Email must be valid format.
- •State Transition Rules — Valid sequences of status changes. Example: Order can go Pending→Confirmed→Shipped→Delivered, but not Shipped→Pending.
- •Authorization Rules — Who can perform what actions. Example: Only managers can approve expenses over $1000.
- •Triggering Rules — Events that automatically cause actions. Example: When inventory falls below reorder point, create purchase request.
- •Constraint Rules — Invariants that must always hold. Example: Account balance cannot be negative for standard accounts.
- •Calculation Rules — How derived values are computed. (Covered in previous section)
State Machine Documentation:
Entities with lifecycle (Orders, Tickets, Applications) benefit from explicit state machine documentation:
ENTITY STATE MACHINE: Order
States:
- Draft: Order being assembled, not yet submitted
- Pending: Submitted, awaiting validation/approval
- Confirmed: Validated and accepted
- Processing: Being fulfilled
- Shipped: In transit to customer
- Delivered: Received by customer
- Cancelled: Order cancelled
- Returned: Customer returned product
Transitions:
Draft → Pending: Customer submits (validation required)
Pending → Confirmed: Payment verified, inventory allocated
Pending → Cancelled: Validation fails or customer cancels
Confirmed → Processing: Picking begins
Confirmed → Cancelled: Customer cancels (refund required)
Processing → Shipped: Carrier picks up
Shipped → Delivered: Carrier confirms delivery
Delivered → Returned: Return initiated within 30 days
Invalid Transitions:
Shipped → Pending: Cannot revert shipped order
Delivered → Processing: Cannot pick already delivered
Auto-Transitions:
Pending → Cancelled: After 24 hours without payment
Shipped → Delivered: 7 days without delivery confirmation
A key architectural decision is where to enforce business rules—in the database (constraints, triggers) or in application code. Database enforcement is more reliable (no bypassing) but less flexible. Document which rules are critical enough to enforce at the database level.
KEEP LEARNING
There’s more to understand.
You’re reading the introduction. Create a free account to read the complete lesson, save notes and keep track of your progress.