Product data model
Related entities, keys and integrity rules.
← All technologies
SQL · BIG FISH
Database design and SQL development
We design data structures, queries and reports for digital products, addressing integrity, concurrent changes and measurable performance.
BIG FISH / SQL
01Report parameters
02Access plan
03Filter and aggregate
04Verifiable result
Design × technology × your workflow
PRODUCT CAPABILITIES
Technology becomes
a working product.
Management reporting
Filtering and aggregation with a clear meaning for each metric.
Measured optimisation
Queries and indexes designed around actual operations and data volume.
INTERACTIVE EXAMPLE
Try the workflow.
See the outcome.
A paid-order report across branches. Choose a branch and lookup strategy: the result is identical while the educational model examines different numbers of records.
Educational example with fictional data. Actions stay in your browser. This models the workflow; it is not a connected CMS, server or database.
SQL
120 fictional orders. Only paid orders are included.
SQLWorkflow result
The example is ready to explore.
WORKFLOW ARCHITECTURE
Every step has
a responsibility.
01
Report parameters
02
Access plan
03
Filter and aggregate
04
Verifiable result
| Layer | Outcome |
|---|---|
| Interface | Input, limits, loading and errors are clear. Keyboard operation is available. |
| Data | Values have an owner, format and freshness rules. |
| Release | Critical journeys, backups and recovery have been checked. |
BIG FISH · SQL
Data people can read.
SQL database work shapes reporting interfaces: which metrics to compare, over what period and at which level of detail. We design tables, filters and charts so totals can be traced back to their source records.
For analytics dashboards and CRM systems, we define units, missing values and update timestamps. An empty result is different from zero, and changing a filter should not silently change what a metric means.
PRACTICAL GUIDE
Building a product
with SQL.
Architecture, constraints and testable workflows. The exact configuration follows the project requirements.
SQL / 01
Domain model
Tables represent entities and relationships: customer, order, line and payment. Define keys, required fields and allowed states. Avoid independent copies of a total without reconciliation rules. Keep a price snapshot on the order line so catalogue updates do not change historical financial results.
Acceptance check
Order history survives a price-list update.
SQL / 02
Data integrity
Unique constraints, foreign keys and value checks reinforce rules in the database. Exact capabilities depend on the database engine and version. Test imports and administrative operations that can bypass the application interface. Detect invalid records before they reach reporting or integrations.
Acceptance check
The database rejects duplicate keys.
SQL / 03
Joins and aggregation
Joining an order to its lines can repeat the order total. Define the report’s row granularity before choosing grouping and aggregates. Test customers without orders, cancelled transactions and empty periods. Compare totals against a small manually verified dataset before trusting a large report.
Acceptance check
A join does not double an order total.
SELECT branch_id, SUM(amount_minor) AS paid_total
FROM orders
WHERE status = :status AND branch_id = :branch
GROUP BY branch_id;SQL / 04
Indexes and plans
Design indexes around actual filters, joins and sort order. Column order matters in composite indexes. Execution plans reveal the chosen access strategy but must be interpreted alongside data volume and distribution. The demo counts examined rows in a JavaScript model; it is not a real database performance benchmark.
Acceptance check
Both access strategies produce the same total.
SQL / 05
Transactions and concurrency
Two users may modify the same order or reserve the final item simultaneously. Select suitable isolation and locking or version-control strategies. Test deadlocks and safe retries. Keep transactions appropriately short: waiting for an external HTTP service while holding a lock can obstruct other work.
Acceptance check
Concurrent updates do not lose data.
SQL / 06
Schema migrations
Adding a required column to a large table needs a backfill plan. Deploy changes with temporary coexistence of old and new application code in mind. Test lock duration against comparable volumes. A reverse migration cannot necessarily recover deleted values, so destructive transformations need a separate backup plan.
Acceptance check
A large table migrates according to the agreed plan.
SQL / 07
Access and queries
Give the application database user only the necessary privileges. Parameterise values and allowlist dynamic table or column names. Check company isolation in every query, including exports. A hidden interface filter is not protection against accessing another company’s records. Query logs must not indiscriminately expose sensitive data.
Acceptance check
Exports contain no other organisation’s records.
SQL / 08
Recovery and monitoring
Agree acceptable data loss and recovery time. A backup is useful only when it can be restored and validated. Monitor slow queries, table growth and locking errors. Replication can support availability and reads, but does not replace backups: an accidental deletion may propagate to replicas too.
Acceptance check
Recovery time is tested on a copy.
FAQ
Before we start
Is this a ready-made product?
No. The example illustrates a proposed workflow for SQL. We design the production system around your requirements, data and integrations.
Can we retain our current systems?
We first review their interfaces, data ownership and constraints. The result may be an integration, a phased migration or a focused replacement of one component.
What do we receive at handover?
The agreed deliverables include design assets, source code, environment requirements and operational instructions. Scope, ownership and support are recorded in the contract.
Let’s design your product
Tell us about your workflow, audience and existing systems. We will define the interface, technical boundaries and delivery stages.