Database schema design prompt

When to use it

Use this when you need a relational schema design and want a concrete starting point rather than generic entity lists. Replace the fictional details with your own confirmed business rules before using the result in a real setting.

The prompt

## Role
Act as a backend database architect. Apply professional judgment while keeping your technical decisions clear to an intermediate software engineer.

## Context
KiteFleet operates a multi-tenant equipment rental service. Customers rent heavy machinery by the day or week. The system must track inventory across depots, multi-item customer bookings, security deposits, and maintenance inspections. Equipment cannot be rented while under active inspection. KiteFleet uses PostgreSQL 16.

## Objective
Design a normalized relational schema that prevents double-booking, records status transitions for assets, and separates customer billing from reservation history.

## Audience
The primary users are backend engineers. Make the deliverable immediately usable for migration scripts, with enough technical context to justify data types and indexing decisions.

## Requirements
Provide clean PostgreSQL DDL; table definitions with primary keys, foreign keys, and column constraints; state enum types; partial or unique indexes to prevent overlapping bookings; and a brief explanation of relational cardinality for core entities.

## Constraints
Do not introduce external microservices, event streams, or document stores. Work strictly with relational tables in PostgreSQL. Do not invent pricing formulas or payment gateway integrations beyond standard monetary balances. Clearly state all normalization assumptions.

## Tone & style
Be precise, idiomatic, and technical. Prefer explicit constraint names, standard SQL conventions, and snake_case naming. Provide brief inline comments for non-obvious constraints.

## Output format
Present the PostgreSQL DDL script first in a dedicated code block. Follow with a table summary detailing entity relationships. Finish with an index rationale table and a short list of edge cases requiring application-level enforcement.

## What to avoid
Avoid loose TEXT types for constrained statuses, missing foreign key cascade rules, soft-delete mechanisms without index considerations, and generic UUID keys without performance justification.

## Success criteria
An engineer can run the DDL without syntax errors, understand how overlapping reservations are blocked, and verify that maintenance states prevent rental scheduling.

## Clarifying questions
Ask up to three focused questions only if missing schema boundaries would alter table normal forms. Otherwise proceed with the scenario, document assumptions, and highlight production trade-offs.

Why it works

• The scenario defines explicit domain logic (depots, overlapping rentals, maintenance locks) instead of an abstract CRUD model.
• The requirements demand production-ready DDL and concurrency protection rather than simple entity diagrams.
• The constraints keep the design focused on core transactional integrity without premature distributed architecture.
• The success criteria ensure runnable code that directly enforces the scenario's business rules.

Make it yours

Build a tailored version with PromptDeck by replacing the fictional scenario with your own context, audience, and constraints. Keep the verification steps so the final result reflects what you actually know.