Database Design

Experience with Database Design

Database design is the foundational data architecture skill that Pablo Diaz applies across every Blossend platform, ensuring that data is stored efficiently, queried performantly, secured comprehensively, and structured to evolve gracefully as products grow. His database design expertise covers relational modeling (PostgreSQL across all platforms), schema normalization and strategic denormalization, indexing strategies optimized for actual query patterns rather than theoretical access paths, migration management for zero-downtime schema changes on live production databases, and multi-tenant data isolation using Supabase Row Level Security. OpenMyPro's database schema is the most complex in the Blossend ecosystem, managing interconnected entities including patients (profiles, preferences, booking history, insurance information), providers (credentials, specialties, availability calendars, pricing, verification status), appointments (bookings, cancellations, rescheduling, completion status), reviews (ratings, text, verification that reviewer actually booked), payments (Stripe subscription state, transaction history, refunds), and the matching algorithm's feature data (preference weights, historical match scores, geographic data). The schema uses foreign keys with cascading rules for referential integrity, check constraints for data validation at the database level (ensuring that appointment times are in the future, ratings are 1-5, prices are positive), unique constraints preventing duplicate bookings, and partial indexes for queries that only need to scan active records. Pablo's database design process starts with the data model — understanding what entities exist, how they relate, and what queries will be most frequent — before writing any application code. This data-first approach ensures the database supports the application rather than the application working around database limitations. Advanced patterns include materialized views for dashboard analytics (precomputing expensive aggregations), database functions for atomic business logic (ensuring booking and payment happen in a single transaction), event sourcing patterns for audit trails (appending rather than updating to maintain complete history), and connection pooling through PgBouncer for efficient resource utilization under high concurrency.

Frequently Asked Questions