Loading learning content...
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.
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.
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:
| 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:
Detailing Update Operations:
Detailing Delete Operations:
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.
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:
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 | 1. Validate cart 2. Create Order 3. Create OrderItems 4. Reduce Inventory 5. Charge Payment |
| 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:
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.
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
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.
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.
Tactical Reports: Support short-term management decisions. Regular frequency, summarized, recent data.
Strategic Reports: Support long-term planning. Lower frequency, highly aggregated, historical trends.
Compliance Reports: Meet regulatory or audit requirements. Specific formats and content mandated externally.
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.
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:
Store pre-calculated:
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 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:
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.
Most databases don't operate in isolation. They exchange data with other systems—enterprise applications, external partners, APIs, and data warehouses. Integration requirements specify these data flows.
Integration Patterns:
| Pattern | Description | Requirements to Document |
|---|---|---|
| Batch Import | Periodic bulk data loads from external sources | Source format, frequency, volume, validation rules, error handling |
| Batch Export | Periodic extraction for other systems | Target format, frequency, selection criteria, file naming |
| Real-time API | Synchronous data exchange via APIs | Endpoints, payloads, authentication, SLAs, error responses |
| Event Streaming | Published events on data changes | Event types, payload content, subscribers, ordering guarantees |
| Replication | Copying data to other databases | Latency tolerance, conflict resolution, subset selection |
| ETL to Warehouse | Extraction for analytics | Transformation rules, refresh frequency, historical tracking |
Integration Requirement Specification:
INTEGRATION: Supplier Product Catalog Import
Direction: Inbound (external → database)
Source System: Supplier EDI feed
Frequency: Daily at 2 AM
Format: CSV with fixed schema
Volume: ~50,000 product records per file
Process:
1. Receive file via SFTP
2. Validate format and required fields
3. Match to existing products by SupplierProductCode
4. For matches: update price, availability, description
5. For new products: create provisional record (requires approval)
6. For missing products: flag as potentially discontinued
Error Handling:
- Invalid format: Reject entire file, notify operations
- Invalid records: Log and skip, continue with valid records
- Processing failure: Retry 3x, then alert
Data Mapping:
- Supplier.ProductCode → Product.SupplierProductCode
- Supplier.Description → Product.Description
- Supplier.RetailPrice → Product.SuggestedPrice
- Supplier.Availability → Product.SupplierStockStatus
When multiple systems maintain similar data (customer info in CRM and billing), determine the 'system of record' for each data element. Who owns the master? How are changes propagated? Unclear ownership leads to data conflicts and inconsistencies.
Performance requirements specify how fast and how much. They're technically 'non-functional' requirements but so critical to database design that they're gathered alongside functional requirements.
| Metric | What It Measures | Typical Specification |
|---|---|---|
| Response Time | How fast operations complete | Order lookup < 200ms at 95th percentile |
| Throughput | How many operations per time unit | Support 500 orders/minute at peak |
| Concurrency | How many simultaneous users | Support 10,000 concurrent sessions |
| Data Volume | How much data to store | 5 million customers, 50 million orders, 500 million order items |
| Growth Rate | How fast data increases | 1 million new orders per month |
| Report Generation | Time to produce reports | Monthly sales report < 5 minutes |
| Batch Processing | Time to complete batch jobs | Nightly inventory sync < 2 hours |
Performance Requirement Specification:
PERFORMANCE REQUIREMENTS: Order Management System
Current State:
- 2 million active customers
- 10 million orders (2 years history)
- 80 million order items
- 100,000 products
Growth Projection (5 years):
- 8 million customers
- 50 million orders
- 400 million order items
Transaction Performance:
- Place Order: < 2 seconds end-to-end
- Customer Lookup: < 200ms
- Product Search: < 500ms for first page
- Order History (single customer): < 500ms
Concurrency:
- Normal: 5,000 concurrent users
- Peak (Black Friday): 50,000 concurrent users
- Order throughput at peak: 1,000 orders/minute
Reporting:
- Operational reports: < 30 seconds
- Monthly summary reports: < 5 minutes
- Ad-hoc queries: < 2 minutes for reasonable scope
Availability:
- 99.95% uptime (< 22 minutes downtime/month)
- Maintenance windows: Sunday 2-4 AM
Design Impact:
Performance requirements directly influence design:
Stakeholders often give unrealistic performance requirements ('everything should be instant') or vague ones ('fast enough'). Push for specific, measurable requirements based on actual business needs. What's the cost of a slower response? What's the real peak demand?
Functional requirements specify what the database must do—the operations, transactions, queries, reports, calculations, and integrations it must support. They complement data requirements to provide a complete specification for database design.
What's Next:
Data requirements and functional requirements describe what the database must capture and do. But the database must also operate within constraints—business rules, validation requirements, and data integrity rules. The next page explores Constraint Identification—specifying the rules that govern data validity and operations.
You now understand how to specify functional requirements for database design. Combined with data requirements, these specifications provide a complete picture of what the database must achieve. Next, we'll complete our requirements coverage with constraint identification—the rules that ensure data quality and operational integrity.