Requirements Gathering for Database Design

Interviews reveal needs. Documents provide evidence. But neither interviews nor documents are directly usable for database design. The raw material of requirements gathering must be transformed into structured data requirements specifications—formal, precise descriptions of what data the database must store.

Data requirements are the bridge between the problem domain and the technical solution. They answer the fundamental question: What information must the database capture, organize, and preserve?

A well-specified data requirement is:

• Complete — Covering all identified data needs
• Precise — Leaving no ambiguity about meaning, format, or constraints
• Verifiable — Testable against future implementations
• Traceable — Linked to source stakeholders and documents
• Organized — Structured for easy reference and use in design

Data requirements define what information the system must persistently store to support organizational operations, decision-making, and compliance. They specify the types of facts, objects, and relationships that the database must represent.

Data Requirements vs. Other Requirement Types:

Database projects involve multiple requirement categories. Data requirements are distinct from:

• Functional Requirements — What the system must do (e.g., "process orders"). Covered in the next page.
• Non-Functional Requirements — Performance, security, availability constraints
• Interface Requirements — How users and systems interact with the database
• Data Requirements — What information must be stored

Data requirements focus specifically on the persistent information model that underlies all other functionality. You cannot process orders without storing order data. You cannot generate reports without the data they report on.

Entities are the fundamental objects about which the organization needs to store information. They represent things that have independent existence and identity within the business domain.

What Makes Something an Entity?

An entity typically:

1. Has independent existence in the business domain
2. Can be uniquely identified
3. Has multiple attributes worth recording
4. Participates in relationships with other entities
5. Has multiple instances (individual occurrences)

Not everything mentioned in requirements is an entity. Some are attributes (descriptions of entities), some are relationships, and some are values.

Entity Discovery Techniques:

Noun Analysis: Review interview transcripts and documents, highlighting nouns. Nouns that recur frequently and have associated facts are entity candidates.

Subject-Object Analysis: In statements like "Customers place Orders" or "Employees work in Departments," the subjects and objects are typically entities.

Form Field Analysis: Groups of related fields on forms often represent entities. A "Customer Information" section suggests a Customer entity with those fields as attributes.

Report Column Analysis: Column headers in reports often correspond to entity attributes. Grouping columns by their subject reveals entities.

Existing System Tables: Tables in legacy databases typically represent entities (or denormalized combinations thereof).

Event/Transaction Analysis: Business events that must be recorded (Order Placed, Payment Received, Shipment Sent) often become entities or relate to entities.

Attributes are the specific pieces of information that describe entities. Each attribute represents a single fact about an entity instance.

Attribute Specification Requirements:

Every attribute should be specified with:

1. Name — A clear, descriptive identifier
2. Definition — The business meaning of this attribute
3. Domain — The set of valid values (data type, length, format, constraints)
4. Optionality — Whether the attribute is mandatory or optional
5. Source — Where this information originates
6. Entity — Which entity this attribute describes

Types of Attributes:

Simple vs. Composite Attributes:

• Simple: Atomic values that cannot be meaningfully subdivided (e.g., BirthDate, UnitPrice)
• Composite: Can be split into sub-attributes (e.g., Address → Street, City, State, ZIP; Name → FirstName, LastName)

Design Decision: Whether to store composite attributes as single values or separate components depends on how they'll be used. If you need to query by City, store City separately.

Single-Valued vs. Multi-Valued Attributes:

• Single-Valued: One value per entity instance (e.g., one BirthDate per person)
• Multi-Valued: Multiple values per entity instance (e.g., multiple PhoneNumbers per customer)

Normalization Principle: Multi-valued attributes typically indicate that a separate entity is needed (e.g., CustomerPhone).

Stored vs. Derived Attributes:

• Stored: Values entered and persisted directly (e.g., UnitPrice, Quantity)
• Derived: Calculated from other attributes (e.g., LineTotal = UnitPrice × Quantity)

Design Decision: Whether to store derived values depends on calculation complexity, performance needs, and update frequency.

A domain is the set of all legal values that an attribute can assume. Domain specification is critical for data integrity—it defines the boundaries of valid data.

Types of Domains:

Domain Specification Best Practices:

Be Explicit: Don't assume everyone understands that 'Date' means a date without time, or that 'Amount' allows negative values. Specify precisely:

• Date vs. DateTime vs. Time
• Signed vs. unsigned numbers
• Precision for decimals (e.g., 2 decimal places for currency)

Future-Proof: Consider future needs when defining domains:

• Phone numbers may need international formats
• ZIP codes may need to include Canada, UK, or other countries
• Enumeration lists may need to expand

Capture Special Values: Document how to handle:

• NULL (unknown/not applicable)
• Not yet specified
• Explicitly empty
• Historical values no longer valid for new entries

Relationships define how entities are connected. They are as important as entities themselves—in fact, the relational model is named for its focus on relationships.

Relationship Specification Elements:

Every relationship should specify:

1. Participating Entities — Which entities are connected
2. Relationship Name — A verb phrase describing the association (Customer places Order)
3. Cardinality — How many instances participate (1:1, 1:N, M:N)
4. Participation — Whether participation is mandatory (total) or optional (partial)
5. Relationship Attributes — Any data about the relationship itself
6. Business Meaning — Why this relationship exists

Participation Constraints:

Total (Mandatory) Participation: Every instance of the entity must participate in the relationship.

• Example: "Every Order must have a Customer" — No orphan orders allowed.

Partial (Optional) Participation: Instances may or may not participate in the relationship.

• Example: "Employees may be assigned to a Project" — Some employees have no project assignment.

Minimum and Maximum Cardinality: More precisely, we can specify:

• Minimum: The least number of related instances (0 = optional, 1+ = mandatory)
• Maximum: The most related instances allowed (1 = single, N = unlimited, or a specific number)

Example: "Each Project must have at least 2 and at most 10 Team Members" → (2,10)

Every entity must have a way to uniquely identify each instance. Identifiers are attributes (or combinations of attributes) that distinguish one entity instance from another.

Types of Identifiers:

Identifier Selection Criteria:

When identifying keys during requirements, evaluate:

Uniqueness: Is the identifier truly unique across all instances, past, present, and future? SSN is unique for US individuals, but what about international employees?

Stability: Will the identifier value change? Email addresses change. Names change. Assigned numbers typically don't.

Minimality: Is any part of a composite key redundant? Use the minimum attributes necessary for uniqueness.

Existence: Is the identifier available when the entity is created? You can't use InvoiceNumber as a key if invoices are created before numbers are assigned.

Format: Is the identifier format consistent and well-defined? Free-form text identifiers are problematic.

Privacy: Are there privacy concerns with using this identifier? Using SSN as a customer key creates security risks.

Data requirements findings must be documented in a structured, usable format. The documentation serves multiple purposes:

• Communication with stakeholders for validation
• Input to database designers for conceptual and logical modeling
• Reference for developers during implementation
• Audit trail for requirements decisions

Core Data Requirements Document Sections:

Entity Specification Example:

ENTITY: Customer

Description: 
  A person or organization that purchases products or services 
  from the company.

Aliases: Client, Buyer, Account Holder

Primary Identifier: CustomerID (surrogate)
Alternate Identifiers: EmailAddress (for individuals), 
                       TaxID (for organizations)

Estimated Volume: 500,000 current; 50,000 new per year
Retention: Active customers indefinitely; Inactive >5 years archived

ATTRIBUTES:

  CustomerID
    Definition: System-assigned unique identifier
    Domain: PositiveInteger, Auto-generated
    Optionality: Required
    Source: System-generated at registration

  CustomerType
    Definition: Classification of customer
    Domain: {Individual, Business, Government}
    Optionality: Required
    Source: Customer selection during registration

  EmailAddress
    Definition: Primary email for communications
    Domain: EmailAddress (see Domain Definitions)
    Optionality: Required for Individual; Optional for Business
    Source: Customer provides
    Constraint: Must be unique across all customers

  RegistrationDate
    Definition: Date customer account was created
    Domain: Date, not future
    Optionality: Required
    Source: System-captured at registration

...(additional attributes)

Data requirements documentation must be validated before proceeding to design. Validation ensures that documented requirements accurately reflect stakeholder needs and organizational realities.

Validation Techniques:

Common Validation Issues:

Terminology Conflicts: Different stakeholders use the same term differently. "Account" might mean customer account to Sales but financial account to Accounting. Establish and document canonical definitions.

Missing Entities: "Wait—you don't have Warranty anywhere? We need to track warranties!" Validation reveals entities overlooked during initial gathering.

Cardinality Errors: "Actually, a product can belong to multiple categories, not just one." Stakeholder review catches relationship misunderstandings.

Constraint Conflicts: "You show OrderDate as required, but for phone orders, we don't always capture the exact date." Validation reveals requirements that conflict with operational reality.

Domain Issues: "Status needs a 'Hold' option that isn't in your list." Enumeration domains frequently need expansion.

Data requirements transform raw requirements gathering output into structured specifications that directly inform database design. They answer the fundamental question of what information must be stored.

What's Next:

Data requirements define what to store; Functional Requirements define what the system must do with that data. The next page explores functional requirements—the operations, queries, updates, and reports that the database must support. Together, data and functional requirements provide a complete specification for database design.