Donut Shop Database: PostgreSQL Design for a Growing Business
What this is
A complete PostgreSQL database that runs a donut shop's daily work: stock, suppliers, staff, customers, orders and payments. It has 3 schemas, 25 tables, 32 procedures and 9 triggers.
Why PostgreSQL
I chose PostgreSQL because it is reliable and scalable. If the business gets bigger, with more customers, orders and staff, the database keeps working smoothly and doesn't need to be rebuilt.
How it is organized
The database is split into 3 sections (schemas), so everything has a clear place:
Inventory: donuts, ingredients, suppliers, purchases, stock levels, discounts, waste, and price history.
HR: employee roles, shifts, salaries, and payment history.
Sales: customers, orders, payments, and canceled items.
All the tables are connected, so the data stays consistent. There are also 4 ready-made reports (views), such as daily sales, sales by donut, and canceled items.
Procedures: ready-made, safe operations
Every change to the data goes through one of 32 stored procedures, such as record_items, record_payment and cancel_sale. Each procedure checks the data first and gives a clear error message if something is wrong, for example a negative price, a donut that doesn't exist, or an order bigger than the stock.
I built it this way for speed and safety. Running operations directly from Python is slower, so my Python functions call these procedures instead. The work then runs inside the database in a single call, and the application never writes to the tables directly. In my testing, this was really fast.
Triggers: the automatic watchmen
9 triggers react the moment data changes, with no extra code needed:
A new order item is added: the donut stock goes down automatically and the change is logged.
A donut or ingredient price changes: the old and new prices are saved in a history table.
An employee's salary or shift changes: the change is saved in a history table.
Stock gets low: a warning is created for donuts at 10 or fewer and for ingredients below their minimum level.
New ingredients are purchased: the stock level is updated automatically.
Built with data analysis in mind
Customers and employees are never deleted, only marked inactive, so history is kept. Together with the price, salary, waste and inventory logs, this gives clean data for reports, forecasting and dashboards.
Tools: PostgreSQL, PL/pgSQL, Python
0 likes · 0 views · Oct 21, 2026
Donut Shop Database: PostgreSQL Design for a Growing Business What this is A complete PostgreSQL database that runs a donut shop's daily work: stock, supplie...