Designing data models for small web applications

A small web application often starts with a list. Projects, contacts and tasks arrive later. If each extension becomes another text column, duplicated information and unclear relationships soon appear. A change then has to stay consistent in several places simultaneously.

This guide develops a deliberately small model for customers, projects and tasks. All sample data is fictional. Executable SQL uses SQLite so rules can be explored in isolation. Moving to MySQL is treated as a separate implementation step rather than an unverified compatibility promise.

1. Describe operations before tables

Begin with the application's operations. A customer is created, then a project for that customer and tasks within the project. Each task has a processing status. A project without tasks is allowed; a task without an existing project is not.

Those statements already contain important decisions. They are not universal truths for every agency application. Another organisation might need internal projects without customers or tasks belonging to several projects. Resolve such differences before implementation rather than concealing them with special values later.

Also record excluded responsibilities. This example is not accounting, billing or a complete customer management system. Invoice amounts and contract periods are not added incidentally. Each additional business task can introduce further rules and lifecycles.

Short examples with expected results are enough to begin. Reassigning an existing project to another customer is an important question: should it be allowed and must the former relationship remain traceable? Those answers affect the model more than an attractive table name.

2. Distinguish objects from properties

Customers, projects and tasks can each be described independently. A project title is a property of its project. Storing multiple tasks as comma-separated text in a project column makes independent editing and reliable relationships difficult.

This separation supports changes. Correcting a customer name does not require changing every task. A task refers to the customer through its project. The application can query related information together when required.

Not every word needs its own table. A small fixed status set can initially be a controlled value. If statuses later acquire properties or configurable workflows, another structure may become appropriate. Actual requirements should determine that decision.

3. Separate identity from visible labels

A name is rarely a suitable technical key. Different customers can share a name and names can change. The model therefore uses an internal ID. A visible customer number is another business property with its own uniqueness rule.

An email address is not automatically a company's identity either. Several people may use one functional address, while a company may have several addresses. This example therefore does not assume email as a customer key. Contact information would be a separately defined extension.

For every uniqueness rule, establish its scope. Customer numbers might be globally unique or unique within a particular organisation. In a multi-organisation application, a global comparison could enforce the wrong business boundary.

Determine who may assign or change a key. An automatically generated internal ID differs from a reference entered by staff. A visible number should not silently take on the role of an immutable technical relationship.

4. Test relationships with concrete quantities

One customer can have several projects. Each project belongs to exactly one customer. A project can have several tasks, and each task belongs to exactly one project. A few boxes and connecting lines can express this initial structure.

Customer 1 ─── n Project
Project  1 ─── n Task

The numbers describe permitted relationships rather than measured quantities. Check boundaries: is zero allowed, may a relationship change later and can an object be assigned more than once? Small examples often expose uncertainty faster than a large diagram.

A many-to-many relationship usually needs an explicit association. If tasks gain several assignees, a list of person numbers in one text field is a weak foundation. An association table can store each connection separately and prevent duplicate assignments.

That extension is deliberately absent from the SQL below. The learning model remains bounded to the three established objects. Its actual rules can be checked rather than presenting an extensive supposedly universal system without business acceptance.

5. Create an executable schema with constraints

This example is written for SQLite. Foreign keys are enabled explicitly for the connection. The official SQLite foreign key documentation explains that requirement. Other database systems need their own relevant settings and prerequisites checked separately.

PRAGMA foreign_keys = ON;

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  customer_number TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL CHECK (length(trim(name)) > 0)
);

CREATE TABLE projects (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  title TEXT NOT NULL CHECK (length(trim(title)) > 0),
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
);

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  project_id INTEGER NOT NULL,
  title TEXT NOT NULL CHECK (length(trim(title)) > 0),
  status TEXT NOT NULL DEFAULT 'open'
    CHECK (status IN ('open', 'in_progress', 'done')),
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE RESTRICT
);

CREATE INDEX tasks_project_status ON tasks(project_id, status);

The CREATE TABLE documentation explains the constraints used. The schema prevents a task referencing a nonexistent project, for example. It does not determine which signed-in person may edit that task. Authorisation remains another application responsibility.

The non-empty title check is deliberately simple. It does not replace comprehensive business validation of appropriate length or specialised character rules. Database constraints and application validation should support the same agreement while keeping their respective limits visible.

Observe the precise scope of each rule: NOT NULL does not reject an empty string. This learning schema has no equivalent content check for customer numbers. If empty numbers are prohibited by the business rules, add and test that separate requirement explicitly.

6. Handle missing values deliberately

Required values use NOT NULL here. The business still decides whether a value genuinely must be available at every operation. Unnecessary mandatory fields often encourage substitutes such as “unknown”, which later processes may mistake for genuine information.

An optional date may be unknown, not yet scheduled or inapplicable. Those meanings are not necessarily identical. If the application needs to distinguish them, define suitable rules. A single empty string does not explain the distinction.

A default is a business decision too. Task status begins as “open” because newly created tasks in this model are initially open. An approval workflow might require another initial state. Defaults should not hide a decision that somebody actually needs to make.

Discuss incomplete inputs with future users. What can be supplied later, and what prevents the operation? The answers determine required fields and intermediate states. Model the actual work rather than merely a formally complete screen.

7. Treat related changes as an operation

Creating a project and its first task may be one operation. If only one step succeeds, the resulting state may be unintended. A transaction can help confirm or undo the related changes together.

BEGIN;

INSERT INTO customers (id, customer_number, name)
VALUES (1, 'C-001', 'Fictional example company');

INSERT INTO projects (id, customer_id, title)
VALUES (1, 1, 'Website maintenance');

INSERT INTO tasks (id, project_id, title)
VALUES (1, 1, 'Review service information');

COMMIT;

Fixed IDs make the example easy to follow. A real application needs suitable assignment and concurrency handling. These statements contain fixed teaching data only. User input requires appropriate prepared statements and server-side validation.

The SQLite transaction documentation describes the operations. A transaction does not replace a correct business boundary. If an external message is also sent, its state cannot simply be undone through the same database transaction.

8. Consider typical queries during design

A model should answer needed questions as well as accept data. Here, tasks need to be shown with their project and customer. An explicit query joins those relationships. Names are not duplicated inside every task merely to make them available.

SELECT
  c.customer_number,
  c.name AS customer_name,
  p.title AS project_title,
  t.title AS task_title,
  t.status
FROM customers AS c
JOIN projects AS p ON p.customer_id = c.id
LEFT JOIN tasks AS t ON t.project_id = p.id
WHERE c.id = 1
ORDER BY p.id, t.id;

The left join preserves an existing project with no tasks in this result. An inner join would handle that case differently. Choose according to the intended list and check the output with empty and multiply populated relationships.

An index supports particular access patterns, not a universal speed guarantee. The example index suits project and status together. Real index choices follow actual queries and data volumes. Additional structures also affect writes and maintenance.

9. Distinguish deletion from archiving early

What should happen when a customer still has projects? RESTRICT prevents deletion across that existing relationship in this model. The application needs a deliberate next step. Silent removal of all related tasks is not part of the agreed behaviour.

An archived object remains present but may be treated differently in normal lists. Archiving is not a universal substitute for deletion. Define whether archived projects can change, whether their tasks remain visible and who can restore active status.

Appropriate retention depends on the actual data and processes. This technical model does not establish general legal retention periods. Relevant obligations should be clarified with responsible people before production implementation and then represented explicitly.

Check deletion effects with sample data before release. An accidental automatic deletion can be more damaging than a faulty list display. Recovery belongs in backups and controlled procedures rather than relying on someone reconstructing relationships from memory.

10. Test rules with deliberately invalid inputs

A successful insert proves only part of the design. Also attempt a duplicate customer number, empty title, invalid status and nonexistent project reference. Observe the intended rejection rather than assuming the declared constraint works.

These tests do not use production customer records. Run them in an isolated learning or test database. A useful report identifies the system, version and settings. In particular, disabled foreign key enforcement can otherwise create a misleading impression of successful protection.

11. Evolve the model in controlled steps

New requirements can change the meaning of existing data rather than merely adding a form field. Several contacts per customer introduce another relationship. A second “Contact 2” column may look convenient but creates an artificial upper limit.

A migration describes the actual structural change and treatment of existing records. Adding a required field retrospectively needs a justified mapping. An arbitrary default can make data formally valid while remaining factually wrong.

Try changes first with a suitable copy or synthetic data. Production information needs appropriate backup, access and rollback handling. New table structures must not replace intervening customer input with an old complete snapshot.

Moving this example to MySQL requires reviewing types, key generation, character sets, comparison behaviour and the storage engine. The MySQL foreign key documentation explains system-specific prerequisites. A SQLite test does not establish the same installation in MySQL.

12. Maintain a small data dictionary

Document important fields' meanings, origins and permitted changes. Here, the customer number is a unique business reference, while task status describes current progress. Those definitions matter when an export or endpoint is introduced later.

Identify information derived through relationships too. A task list can show the current customer name without storing it repeatedly. Preserving a historical name at a particular time is a different requirement and needs a deliberately designed historical state.

The dictionary should agree with the schema. Lengthy documentation left behind several migrations is not especially helpful. Keep decisive rules current and link their test cases so the business reason for a technical restriction remains understandable.

13. Do not confuse current state with history

Task status here records only current progress. It does not explain when a task changed from open to in progress or who changed it. If that traceability is required, design it explicitly. A last-modified timestamp is not a complete history.

A history could store relevant state transitions as events, with necessary fields and access controls defined separately. Not every small application needs this extension. It should not later claim to prove past decisions when it has stored only the latest state.

Statistics depend on the distinction too. Counting completed tasks whose last-modified date is today is not necessarily the same as counting completion events today. A later wording correction could also change that timestamp.

Define the question before adding a field. Which fact must be demonstrable later, which changes matter and how should an accidental transition be handled? The answers determine whether a timestamp, separate event or another structure is required.

The learning model keeps this limit visible. It stores current tasks with controlled statuses, not a comprehensive audit trail. That is an explicit scope decision rather than a concealed deficiency. A suitable first design describes its actual capabilities precisely.

One reason to keep appropriate invariants in the database is that an application may eventually have more than one writing path. A form, import and background process should not establish different meanings for the same customer number. Each path still needs useful validation and error handling, while the shared constraint remains a final consistency boundary.

Application-side checks alone also need care when requests overlap. A preliminary query finding no matching number does not reserve that number for a later insert. The uniqueness constraint can reject a conflicting write, and the application must translate that result appropriately. This is another reason to test rejected operations rather than only the normal successful screen.

14. First-model checklist

• Typical operations are described before tables.
• Objects and properties are distinguished appropriately.
• Technical identity is separate from visible labels.
• Relationships include zero and multiple cases.
• Uniqueness, required values and states are explicit.
• Constraints were checked in the actual database environment.
• Permissions are not confused with foreign keys.
• Deletion, archiving and history are distinct.
• Queries and indexes follow real requirements.
• Changes have a traceable migration and verification process.

Common questions

How many tables does a small application need?

Enough to represent meaningful objects and relationships. A fixed count says little about quality. A few overloaded tables can be as problematic as many unclear structures.

Does a foreign key enforce permissions?

No. It can enforce a defined existence relationship. The application must also decide who may read or change a record.

Should every value be duplicated for faster queries?

Not without a concrete reason and a clear consistency strategy. Establish an understandable business model first, then address demonstrated bottlenecks deliberately.

Sources and related reading

Checked on 9 November 2026: official SQLite documentation on tables, foreign keys and transactions, plus the MySQL foreign key manual. The model and teaching data were developed for this article.

The wider planning context is covered in What a professional business website needs to achieve. Presenting the resulting information also needs an understandable interface, introduced in Semantic HTML through a complete example page.