Skip to content

Database

The PetFolio Service uses MySQL as its primary data store, accessed through Entity Framework Core with the Pomelo MySQL provider. All database operations go through the Infrastructure Layer - the rest of the application never interacts with the database directly.

Technology

Component Version Purpose
MySQL 8.0+ Relational database
Pomelo.EntityFrameworkCore.MySql 9.0 EF Core provider for MySQL
Entity Framework Core 9.0 ORM and migration management

Tables

Table Description Owner
Accounts Business accounts for pet owners and professionals Aggregate root
Users User profiles with OAuth authentication information Belongs to Account
Animals Pet records - species, breed, gender, description Belongs to Account
Weight Historical weight records with unit conversion Owned by Animal

Entity relationships

erDiagram
    Account ||--o{ User : "has many"
    Account ||--o{ Animal : "has many"
    Animal ||--o{ Weight : "has many"

Relationship rules

1
2
3
Account (1) --< Users (N)
   |
   +--< Animals (N) --< Weight (N)
  • Account to Users: One-to-many, RESTRICT delete (prevents orphaned users)
  • Account to Animals: One-to-many, RESTRICT delete (prevents orphaned animals)
  • Animal to Weight: One-to-many, CASCADE delete (weight records are owned by the animal)

Key features

  • GUID primary keys - all entities use Guid identifiers
  • RESTRICT delete on Account relationships - prevents accidental deletion of accounts that have users or animals
  • CASCADE delete on Weight - when an animal is deleted, its weight records are removed automatically
  • Indexes on foreign keys (AccountId) and email fields for query performance
  • Check constraints on Weight values (must be positive)
  • Unique constraint on (AuthProvider, AuthProviderUserId) - ensures each OAuth identity maps to exactly one user

EF Core configurations

Each entity has a dedicated configuration class in Infrastructure/Persistence/Configurations/:

  • AccountConfiguration: Table mapping, relationships, indexes
  • AnimalConfiguration: Value object conversions (Name, Species, Breed as owned types), owned Weight records
  • UserConfiguration: Email index, OAuth provider indexes, unique constraint

These configurations are automatically discovered by EF Core when PetfolioDbContext is initialised.

Migrations

Database schema changes are managed through EF Core migrations. See Adding or Updating Endpoints for the migration commands.