How to design a database for a small business app, with examples
A practical method for designing a small business database, with three complete example schemas (bookings, field service jobs and inventory) and the decisions that matter most: money, dates, statuses and history.
Key Takeaways
- Start from the events, not the screens. List what happens in the business (a booking is made, a job is completed, stock is received). Each event usually becomes a table with a date and a status.
- Store money as exact decimals. PostgreSQL’s documentation recommends the numeric type for monetary amounts because floating-point types are inexact. Many teams store cents as whole numbers instead.
- Store times with a time zone. A booking at 9:00 means nothing without a zone. Timezone-aware timestamps are stored in UTC and shown in local time.
- Keep history instead of overwriting it. Record prices at the time of sale and stock movements as rows, so totals can always be explained.
- Add four columns to almost every table. id, created_at, updated_at and a status or archived flag cover identity, auditing and safe deletion.
Quick-Start Prioritization Framework
| Business type | Core tables | Hardest design decision |
|---|---|---|
| Salon, studio, clinic (bookings) | clients, staff, services, appointments | Preventing double-booked staff |
| Cleaning, trades, repairs (jobs) | customers, sites, jobs, job_items, invoices | Separating quotes, jobs and invoices |
| Shop, maker, wholesaler (inventory) | products, locations, stock_movements, orders | Calculating stock from movements, not a typed number |
| Membership, classes, gym | members, plans, memberships, sessions, attendance | Tracking membership periods over time |
Most small business apps are built around the same few patterns: people book things, staff do jobs, stock moves in and out, and money changes hands. If you get the tables right for those patterns, the screens, reports and permissions mostly follow. This guide gives you a repeatable design method, then applies it to three real kinds of business so you can copy the parts that fit yours. It assumes you know what tables, rows and keys are; if not, start with a database basics primer and come back.
A six-step design method
Microsoft’s database design guide lays out a sequence that holds up well for small apps: decide the purpose, gather the information, divide it into tables, turn items into columns, choose keys, set up relationships, test with sample data and refine. Here is a compressed version tuned for business apps.
- Write the purpose in one sentence, including who uses the app.
- List the events: what happens, to whom, and when.
- List the things those events involve: customers, staff, products, places.
- Give each thing and each event a table, then add columns for the facts you need.
- Link tables with IDs: who did it, for whom, involving what.
- Write five realistic sample rows per table, including edge cases, and fix whatever doesn’t fit.
Why events matter
Screens change often; events don’t. A booking is a booking whether it was made on the website, by phone or by a staff member. If your tables model the events, you can add a new screen or report without redesigning the data.
Example 1: a salon or studio with bookings
Purpose: clients book services with a specific staff member; staff see their day; the owner sees everything and the revenue.
| Table | Key columns | Notes |
|---|---|---|
| clients | id, name, phone, email, notes, created_at | Email unique if clients sign in |
| staff | id, name, user_id, active | user_id links to the login account |
| services | id, name, duration_minutes, price, active | Mark inactive instead of deleting |
| staff_services | staff_id, service_id | Who can perform what (many-to-many) |
| appointments | id, client_id, staff_id, service_id, starts_at, ends_at, status, price_charged | status: booked, completed, cancelled, no_show |
Preventing double bookings
The tricky rule is that one staff member can’t have two overlapping appointments. Checking this only in the booking screen is not enough, because two clients can book at the same moment. The check needs to happen on the server when the booking is saved; PostgreSQL also supports exclusion constraints that can enforce non-overlapping time ranges at the database level, which a developer or app builder can set up.
Why price_charged lives on the appointment
Services change price. Copying the price onto each appointment when it’s booked means last year’s revenue report still adds up after you raise prices.
Example 2: a field service business with jobs and invoices
Purpose: track quotes, scheduled jobs, the work done and invoices for a cleaning, repair or trades business where one customer can have several sites.
| Table | Key columns | Notes |
|---|---|---|
| customers | id, name, billing_email, phone | The paying party |
| sites | id, customer_id, address, access_notes | One customer, many addresses |
| jobs | id, site_id, assigned_to, scheduled_for, status, quote_id | status: quoted, scheduled, in_progress, done, cancelled |
| job_items | id, job_id, description, quantity, unit_price | Labor and materials lines |
| invoices | id, customer_id, number, issued_on, due_on, status | Number unique, never reused |
| invoice_lines | id, invoice_id, job_item_id, amount | Links billed amounts to the work |
| payments | id, invoice_id, amount, received_on, method | Partial payments become several rows |
Keep quotes, jobs and invoices separate
It’s tempting to have one jobs table with a “stage” column that goes from quote to invoice. It breaks as soon as a customer accepts half a quote, or one invoice covers three jobs. Separate tables with links keep each stage honest.
Balances are calculated
An invoice’s amount due is the sum of its lines minus the sum of its payments. Store payments as rows and calculate the balance; don’t keep an amount_due column someone edits by hand.
Example 3: a shop or maker with inventory
Purpose: know how many of each product are in each location, sell online and in person, and see what to reorder.
| Table | Key columns | Notes |
|---|---|---|
| products | id, sku, name, price, reorder_level, active | sku unique and stored as text |
| locations | id, name | Shop, warehouse, market stall |
| stock_movements | id, product_id, location_id, quantity_change, reason, reference_id, created_at | reason: received, sold, returned, adjusted, transferred |
| orders | id, customer_id, channel, placed_at, status | channel: online, in_store |
| order_lines | order_id, product_id, quantity, unit_price | Price at time of sale |
Stock as a ledger
The most common inventory mistake is a stock_count column that people overwrite. When the number is wrong, nobody can say why. Instead, record every change as a row in stock_movements: +48 received, −2 sold, −1 damaged. Current stock is the sum of movements for that product and location. If counting gets slow years later, a developer can add a cached total, but the ledger stays the source of truth.
SKUs are text
Product codes often have leading zeros or long digit strings. Store them as text so nothing rounds or trims them.
Decisions that apply to every small business database
Beyond the tables themselves, a few cross-cutting choices decide whether the data stays trustworthy.
Money
PostgreSQL’s documentation says the numeric type is “especially recommended for storing monetary amounts,” and warns that real and double precision are inexact. Either use numeric with two decimal places, or store integer cents (1999 for $19.99). If you sell in several currencies, add a currency column next to every amount.
Dates and times
Use a plain date for things without a time (an invoice due date) and a timezone-aware timestamp for moments (an appointment start). PostgreSQL stores timezone-aware values internally as UTC and converts them to local time for display, so a booking made from another time zone still lands at the right hour.
Statuses
Pick a short fixed list for each status column and write down what each means. “Completed” for a job should mean the same thing to every staff member and every report.
Archive instead of delete
Deleting a customer who has invoices either fails or takes the invoices with it, depending on how the link is set up. An active or archived_at column lets you hide old records without losing history. Keep true deletion for privacy requests.
Users, staff and customers in the design
Login accounts and business records are related but not the same thing. A good pattern is a users table for sign-in (email, role) and separate business tables (staff, clients) that point to a user when that person can log in.
Why keep them separate
Many clients never create an account, but you still need their records. Some users are admins who aren’t staff. Linking by user_id lets each record exist with or without a login, and lets permissions rules say “a client can see appointments where client.user_id is the signed-in user.”
Ownership columns
Add columns that answer “whose is this?” to any table people will see selectively: assigned_to on jobs, client_id on appointments, created_by on notes. Permissions are much easier to enforce when ownership is a column rather than a guess.
Common mistakes
These are the design choices small business apps most often regret.
A typed-in stock or balance number
Any total that someone edits by hand will drift. Record the events and calculate the total.
Prices only on the product
Without the price at time of sale on each order line, old totals change whenever prices do.
Times without time zones
Bookings shift by an hour at daylight-saving changes or when a customer books from another zone.
Floating-point money
Small rounding errors accumulate in reports. Use exact decimals or integer cents.
No ownership column
If a job doesn’t record who it’s assigned to, you can’t later give staff a view of only their own jobs.
If you’d rather not build it yourself
If you’d rather not set up the database and screens yourself, an AI app builder such as Floot can create these tables in a Postgres database from a description like the examples above, along with staff and customer logins. Paste the table list from this guide as your starting brief.
Frequently Asked Questions
What tables does a small business database need?
Usually tables for the people you deal with (customers, staff), the things you sell or do (products, services), the events (orders, appointments, jobs) and the money (invoices, payments). Most small business apps end up with between five and fifteen tables.
How should I store prices and money in a database?
Use an exact type. PostgreSQL recommends numeric for monetary amounts; storing whole cents as integers is another common approach. Avoid floating-point types, which PostgreSQL describes as inexact.
Should I delete old customers and products?
Usually no. Mark them inactive or archived so past orders and invoices stay intact. Delete for real when someone asks you to erase their personal data.
How do I track inventory in a database?
Record every change as a stock movement row with a reason, and calculate current stock as the sum. This keeps a full history and makes errors traceable.
Do customers and staff need separate tables from user accounts?
It helps. A users table handles sign-in and roles; customers and staff tables hold business details and link to a user when that person can log in. Not every customer needs an account.
How do I stop double bookings?
Enforce the rule on the server when the booking is saved, not only in the booking form. In PostgreSQL, exclusion constraints can prevent overlapping time ranges for the same staff member.
Sources
- Microsoft Support, Database design basics https://support.microsoft.com/en-us/office/database-design-basics-eb2159cf-1e30-401a-8084-bd4f9c9ca1f5
- PostgreSQL, Numeric Types (version 18), 2026 https://www.postgresql.org/docs/current/datatype-numeric.html
- PostgreSQL, Date/Time Types (version 18), 2026 https://www.postgresql.org/docs/current/datatype-datetime.html
- PostgreSQL, Constraints (version 18), 2026 https://www.postgresql.org/docs/current/ddl-constraints.html