A practical guide to customers, work orders, and bookings, with an example covering integrity, transactions, concurrent requests, index costs, and safe schema changes.
Database problems often arrive as ordinary customer complaints: an edited address changes an old quotation, two employees confirm the same appointment, or a repeated submission creates two jobs. These can look like interface bugs, but the underlying question is often what a record means and which business rules must always hold. A useful data model keeps that meaning clear across screens, integrations, and staff changes.
Before drawing tables, identify decisions the business makes with its information. What makes a job confirmed? Who may cancel it? Which details describe the present, and which document an earlier agreement? Put these answers in the website project brief so they become acceptance criteria.
Start with a testable business story
Consider a hypothetical company maintaining restaurant equipment. Its office receives a request, issues a quotation, and reserves a crew after approval. For this example, the company offers two fixed daily slots, morning and afternoon, with one job per crew per slot. This is a deliberate simplification: appointments with overlapping durations need a different scheduling model.
Translate the story into rules. A job belongs to a customer in the same organization. An accepted quotation preserves its agreed details. A current reservation cannot duplicate the crew, date, and slot. Cancellation records who acted and why. Ask for counterexamples too, such as attaching a job to another organization's customer.
Separate entities and preserve historical meaning
A starting model could contain organizations, customers, work orders, quotation lines, reservations, and activity records. Give records stable identifiers; treat customer names and telephone numbers as editable attributes. A shared telephone number should not block a second customer unless the business explicitly requires that rule.
Keep current contact information on the customer, but copy the agreed service address, customer name, and prices into the accepted quotation version. This duplication preserves history. Copying an address everywhere without identifying the authoritative version creates a different problem. Make repricing a new quotation version rather than silently rewriting an accepted agreement.
Define missing values too. Does an absent appointment mean unscheduled, or does the service need no visit? Name monetary fields with their currency and storage unit, agree a rounding policy, and document the timezone used for appointments. Reports should not have to guess these meanings.
Enforce essential rules in the database
PostgreSQL offers not-null, unique, and foreign-key constraints to protect values and relationships; see its official constraints documentation. In this example, a customer relationship can include both organization and customer identifiers, preventing a reference to an existing customer from the wrong organization.
For each rule, document where it is enforced and the useful error the user receives. Rejecting a negative quantity belongs among data rules. Deciding which supervisor may revise an accepted quotation also requires authorization. A valid relationship does not establish permission to read or change the record; include the application authorization design.
Make confirmation one complete operation
A transaction groups database steps into an operation that succeeds or rolls back together, as explained in the PostgreSQL transaction tutorial. Here, confirming the job, creating its reservation, and recording the confirmation event need a shared success boundary. The interface should not celebrate while reservation creation can still fail.
Do not extend that database promise to an external email delivery. Save a pending notification alongside the confirmation, then let a separate worker deliver it and track attempts. Staff should distinguish a confirmed appointment from an undelivered notice. Design repeated delivery attempts deliberately using the integration reliability guide.
Test requests that arrive together
Imagine two employees viewing an available morning slot and confirming different jobs simultaneously. Checking availability on screen does not reserve it. In this fixed-slot model, current reservations need uniqueness across organization, crew, date, and slot. The losing request should receive an understandable conflict and offer another slot without removing the winning booking.
Transactions do not resolve every concurrency problem automatically; isolation affects behavior and some cases require retries. The transaction isolation documentation explains these distinctions. Write a concurrent test with an explicit expected outcome. For a repeated network request, store a request key and its result; reject reuse of that key with different input.
Choose indexes from operational questions
Indexes can improve row retrieval while adding overhead to data changes, according to the PostgreSQL introduction to indexes. List meaningful queries: a crew's appointments for a day, pending approvals for an organization, or a customer's chronological job history. Propose indexes for those queries and measure with representative data.
Do not index every column just because it appears in a filter. Record the question each index supports, expected read and write activity, and evidence of benefit. A complex monthly report may deserve a separate reading path. Make that decision after measurement and define how fresh its reported figures need to be.
Plan schema changes and recovery
If the company moves from one customer address to multiple service locations, begin by introducing the new model. Migrate in verifiable stages, define how incomplete addresses are handled, and update reads and writes before retiring the old dependency. Rehearse with sensitive information removed in a nonproduction environment, and identify the rollback point and decision owner.
A successful backup alone is insufficient evidence of recoverability. Restore into a separate environment and check customers, work orders, and relationships. Include this in the maintenance and security plan, and include data behavior in the launch checklist.
Questions before approving the design
- Does each record have a definition, owner, and editing or deletion policy?
- Do customer updates preserve earlier agreements?
- What happens when requests conflict or a connection drops after saving?
- Can staff explain a job's state from its records?
- Have migration and restoration been rehearsed, with someone responsible for performance review?
The goal is a small model that explains today's business, protects its important rules, and provides an understandable route for change.
Prepared with AI assistance; technical references listed for verification.