Streamlining the Software Lifecycle: Integrating AI Use Case Modeling with Visual Paradigm’s All-in-One Platform

⌘K
  2. Accueil
  4. Docs
  6. Streamlining the Software...
  8. 5. Behavioral and Structu...
  10. 5.4 Data Modeling (ERDs)

5.4 Data Modeling (ERDs)

Identifying MVC Parts and Building Entity-Relationship Diagrams to Keep Database Plans in Sync with Requirements

While Class Diagrams define the object-oriented structure and behavior of the system (including transient and persistent objects), Entity-Relationship Diagrams (ERDs) focus specifically on the persistent data layer — the part of the system that must be stored in a relational database (or similar persistence mechanism). The ERD ensures that:

  • All data required by use cases is captured
  • Relationships between entities reflect real business cardinality and constraints
  • No critical attributes or associations are overlooked
  • Database schema generation, normalization decisions, and indexing strategies remain traceable to functional requirements

In many modern applications following the Model-View-Controller (MVC) or similar layered architectures, the Model layer is split into two related but distinct concerns:

  • Domain Model (Class Diagram) — rich objects with behavior, validation, and business logic
  • Persistence Model (ERD) — simplified entities optimized for storage, querying, and relational integrity

Visual Paradigm’s AI-Powered Use Case Modeling Studio bridges these views intelligently. When generating an ERD, the tool:

  • Analyzes the Class Diagram (persistent classes become entities)
  • Scans use case specifications for nouns that represent stored data (e.g., “reservation record”, “account balance”, “certificate issued”)
  • Extracts attributes from postconditions, flows, and business rules
  • Infers relationships and cardinalities from interactions, pre/postconditions, and domain context
  • Suggests primary keys, foreign keys, and basic constraints
  • Distinguishes transient vs. persistent elements (e.g., excludes purely UI or session data)

You trigger ERD generation by selecting relevant use cases, classes, or the entire model and choosing “Generate ER Diagram” or “Derive Data Model”. The resulting diagram is a classic Chen-style or Crow’s Foot ERD, fully editable: add weak entities, supertype/subtype hierarchies, attributes to relationships, check constraints, or notes for denormalization decisions.

Practical Examples

Example 1: GourmetReserve – Mobile Dining Reservation App

AI-Generated ERD Core Entities & Relationships (Crow’s Foot notation):

  • Entities:
    • Customer (was Diner in class diagram) PK: customer_id Attributes: name, email, phone, loyalty_status (Gold/Silver/None), created_at
    • Restaurant PK: restaurant_id Attributes: name, address (street, city, zip), phone, cuisine_type, owner_id (FK)
    • RestaurantTable PK: table_id Attributes: restaurant_id (FK), table_number, capacity, location_description (e.g., “patio”, “window”)
    • Reservation (central aggregate) PK: reservation_id Attributes: customer_id (FK), table_id (FK), reservation_datetime, party_size, status (Pending/Confirmed/Cancelled/No-Show), deposit_amount, created_at, cancelled_at
    • Payment PK: payment_id Attributes: reservation_id (FK), amount, payment_method, transaction_id, status (Success/Failed/Refunded), processed_at
  • Relationships:
    • Customer (1) places Reservation (*)
    • Restaurant (1) has RestaurantTable (*)
    • Reservation (1) books RestaurantTable (1) ← strong 1:1 at booking time, with temporal constraint
    • Reservation (1) has Payment (0..1) ← optional if deposit waived
    • Payment (1) belongs to Reservation (1)

Typical refinements you apply:

  • Add composite unique constraint: (table_id, reservation_datetime) to prevent double-booking
  • Introduce weak entity ReservationHistory (log of status changes) with composite key (reservation_id + change_timestamp)
  • Add note: “Denormalize restaurant name into Reservation for faster reporting”

Example 2: SecureATM – Banking Domain (simplified)

Key ERD Elements:

  • Account PK: account_number Attributes: customer_id (FK), account_type (Checking/Savings), balance, daily_withdrawal_limit, last_updated
  • Customer PK: customer_id Attributes: name, ssn (encrypted), primary_phone, address
  • Card PK: card_number Attributes: customer_id (FK), account_number (FK), pin_hash, expiry_date, status (Active/Blocked/Lost)
  • Transaction PK: transaction_id Attributes: account_number (FK), amount, transaction_type (Withdrawal/Deposit/Transfer), timestamp, atm_id (FK), status (Success/Failed)
  • ATM PK: atm_id Attributes: location, installation_date, last_replenishment
  • Relationships:
    • Customer (1) owns Account (*)
    • Account (1) linked to Card (*)
    • Account (1) has Transaction (*)
    • Transaction references ATM (many-to-one)

Refinement example:

  • Add subtype hierarchy: CheckingAccount and SavingsAccount inherit from Account (using table-per-type or single-table inheritance pattern)
  • Add index on Transaction.timestamp + account_number for fast statement generation

Example 3: CorpLearn – E-Learning Platform

AI-Generated Fragment:

  • Learner PK: learner_id Attributes: employee_id, name, email, department, hire_date
  • Course PK: course_id Attributes: title, code, version, created_by, duration_hours
  • Enrollment (junction entity) PK: enrollment_id Attributes: learner_id (FK), course_id (FK), enrollment_date, due_date, status (InProgress/Completed/Failed)
  • Assessment PK: assessment_id Attributes: course_id (FK), title, passing_score, time_limit_minutes
  • AssessmentAttempt PK: attempt_id Attributes: enrollment_id (FK), assessment_id (FK), start_time, end_time, score, passed (boolean)
  • Certificate PK: certificate_id Attributes: attempt_id (FK), issue_date, expiry_date, certificate_number
  • Relationships:
    • Learner () enrolls in Course () via Enrollment (many-to-many)
    • Course (1) contains Assessment (*)
    • Enrollment (1) has AssessmentAttempt (*)
    • AssessmentAttempt (1) may produce Certificate (0..1)

Refinement example:

  • Add unique constraint on (learner_id, course_id) in Enrollment to prevent duplicate enrollments
  • Introduce ComplianceAuditLog entity to track certificate views/downloads for regulatory purposes

Best Practices for ERD Generation & Refinement

  • Focus on persistence only — Exclude transient/session data (e.g., current search filters)
  • Validate cardinalities — Walk through use case postconditions: “Does every Reservation have exactly one Payment if deposit required?”
  • Add constraints early — Unique keys, check constraints (e.g., balance ≥ 0), temporal validity
  • Plan for reporting — Add denormalized fields or summary tables when frequent queries justify it
  • Maintain traceability — Link each entity/relationship back to originating use cases
  • Review with DBA — Share the ERD for normalization feedback and indexing suggestions

By the end of Section 5.4, your data model is fully aligned with functional requirements, ensuring that every piece of information the system needs to remember is explicitly designed and traceable. The AI accelerates the initial structure; your domain knowledge and architectural decisions make it production-ready. With behavioral, interactional, static, and data models complete, the design phase is solid — ready for scenario analysis, validation, and quality assurance in the modules ahead.