database-design

To design a relational database schema from requirements, extract core business nouns to identify tables, then map their one-to-one, one-to-many, many-to-many, and self-referential relationships into structured database tables with proper normalization.

Modeling many-to-many relationships in SQL requires junction tables, while hierarchies use self-referential foreign keys, ensuring proper normalization, constraints, and indexing strategies to maintain data integrity and query performance.

Set up indexes, constraints, and soft deletes in Prisma or Drizzle by defining schema attributes that map to SQL constraints, adding timestamp fields for soft deletion, and generating ORM mappings for implementation.

Yes, you can generate Drizzle or Prisma ORM mappings directly from your database schema design by translating structured tables, primary keys, foreign keys, and constraints into the corresponding ORM configuration syntax.

Use soft deletes instead of hard deletes in relational database design when you need to preserve data history for audit logs or recovery, implementing them via timestamp flags rather than permanently removing rows.