Data Modelling
Part of No-code data modelling
Choosing tables, records and relationships
Decide what one row represents and how to connect records with one-to-many, many-to-many or self-referencing relationships.
Give each table one clear kind of record, then connect records according to how many of each can belong together. Test the table boundary by finishing the sentence “one row represents …” without switching between meanings.
Start with the rows the task needs
Imagine a team tracking client projects and their tasks. If each task belongs to one project and each client can have several projects, a starting model has Clients, Projects and Tasks. Each project refers to a client; each task refers to a project. A task's due date belongs to the task, even if it is displayed on a project page.
Decide what counts as the same row over time. A reassigned task can remain the same task if its identity and history must continue. Recurring work with a fresh due date and completion state may need one row per occurrence.
Steps to Define Table Structure and Relationships
- Identify the core record typeDefine what one row represents—e.g., one task, one project, or one client.
- Determine how records connectAssess whether relationships are one-to-many, many-to-many, or self-referencing based on business rules.
- Decide on edge casesSpecify behaviour when a parent record is deleted, merged, or cancelled—e.g., reassign children or delete them.
- Validate with real-world scenariosTest the model using fictional but realistic cases (e.g., a contractor on two projects with different roles).
Choose the relationship shape
| Question | Likely shape | Record to consider |
|---|---|---|
| Can one parent have several children, while each child has one parent? | One to many | Put a parent reference on each child. |
| Can each side have several of the other? | Many to many | Link the records; consider an association record if the connection has its own facts. |
| Are both rows the same kind of thing? | Self-reference | Keep one table and connect rows within it. |
A project may involve several contractors, and a contractor may work on several projects. If each assignment needs a start date, role or agreed rate, a Project assignment record can connect one project and one contractor. The row then represents that assignment, rather than another copy of the project or contractor.
Product features do not set the business rule for how many links a record should have; define that rule and check how the configured app enforces it.
Relationship Shapes in Data Modelling
- One to ManyOne parent can have multiple children; each child has one parent. Place a parent reference on each child record.
- Many to ManyEach side can have multiple instances of the other. Use a linking record (e.g., Project assignment) if the connection has its own facts like start date or rate.
- Self-ReferenceBoth rows are the same kind of thing. Connect records within a single table, such as a task with a parent task.
Decide what happens at the edges
For each reference, decide whether it may be blank when a row is created. Can a task wait for a project, or would that make it unusable?
Specify what should happen if a client record is merged, a project is cancelled or a referenced row is removed. Do not assume that deleting a parent will leave, delete or reassign its children: behaviour depends on the product and configuration.
Use fictional cases to check the sketch: a client with two projects, a project with several tasks, a contractor on two projects and an assignment that ends. For each case, identify the rows and links that should change.
If a report needs a contractor's current details, consider displaying them through the relationship. Decide separately whether a past assignment needs a historical snapshot.
Key Questions When Designing Tables and Relationships
- Can a task exist without a project?If not, enforce non-null references in the app configuration.
- What happens if a client is merged?Ensure all related projects and tasks are updated or reassigned appropriately.
- Should a project’s cancellation delete its tasks?Decide whether to preserve history or remove data based on business needs.
- Do past assignments need historical snapshots?Consider storing contractor details at the time of assignment for accuracy.
