Data Modelling
No-code data modelling
Plan the records, relationships and field ownership for a no-code app before building screens or importing data.
No-code data modelling means deciding what each record represents, which facts belong to it and how records connect. Start with a task, sketch the records needed to complete it, then check whether a fact can be updated in one place without leaving conflicting copies elsewhere.
Model the work behind the spreadsheet
Suppose an equipment team tracks items issued to staff. Its spreadsheet has one row per handover, with the person's phone number and item description copied into every row. That layout may be easy to read, but it leaves open who maintains those facts when they change.
Write one sentence for each proposed table: People holds one record per person, Items holds one record per physical item, and Handovers holds one record per transfer event. A handover date belongs to the handover; an item's serial number belongs to the item. If authoritative staff or item records already exist elsewhere, decide whether this app should refer to them before creating another editable copy.
Connect records according to the task
One person may have many handovers, while each handover identifies one recipient. That suggests a reference from the handover to the person. If a handover includes several items and an item can appear in several handovers over time, a separate Handover item record can connect each item to each event. It can also hold a fact specific to that connection, such as the item's condition at handover.
| Record | One row represents | Connects to |
|---|---|---|
| Person | One staff member in this example | Their handovers |
| Item | One physical item | Handovers involving that item |
| Handover | One transfer event | Its recipient and item entries |
| Handover item | One item in one transfer | One handover and one item |
Whatever reference mechanism the app uses, it does not decide whether a particular link is correct. Check what the chosen app should do when someone selects the wrong person, removes a link or changes a referenced record.
Make relationship cardinality explicit
Dataverse describes a one-to-many relationship from the parent record's perspective: one referenced row can be associated with many referencing rows. From the child row's perspective, the same relationship is many-to-one.
A many-to-many relationship connects multiple rows on each side, and the related rows are peers in a reciprocal relationship. Dataverse also supports relationships between rows in the same table, so decide whether a link represents a different kind of association from the parent-and-child pattern.
Give each fact an owner
A person's current phone number could be maintained in People and displayed in a handover through the relationship. If the handover must preserve the number used at the time, store that historical value deliberately and label it. The current number and a snapshot answer different questions.
A table does not need splitting merely because it has many columns. Consider another table when rows represent different things, a group can occur several times, or the same editable fact would otherwise need updating across several records. A linked display or lookup may meet a screen's needs without creating another editable address field.
Data ownership: current vs historical values
- Current phone number
- Stored in the People table; updated when changed
- Phone number at handover time
- Stored in the Handover item record as a snapshot; never changes
Use normalisation as a model check
A first-normal-form check asks whether each set of related data has its own table and each row has a primary key. It also rules out storing similar values in several fields in one table, such as a series of numbered fields for repeated entries.
Normalisation describes how tables and relationships are organised to protect data and make a database more flexible. Its first three rules are known as first, second and third normal form; third normal form is considered sufficient for most applications.
More tables can make a model cumbersome, so perfect compliance may not suit every real-world case. If you deliberately depart from a normalisation rule, check whether the app will create redundant data or inconsistent dependencies, and plan how it will handle those risks.
Check the model before importing records
Give each row a stable identifier for its links. That identifier does not, by itself, establish whether two rows represent the same real-world person or item.
Walk through fictional cases: one person with two handovers, a handover with two items, an item transferred again, and a cancelled handover. For each, identify which row changes and which links should remain.
Check deletion behaviour in the chosen product and configuration. If the team cannot agree what a row represents or which record owns a fact, settle that rule before building more screens.
Check what a reference exposes
In Dataverse, a lookup column creates a one-to-many relationship and stores the related record's ID; it also returns that record's primary-name value. Anyone who can access the record containing the lookup can view that name, even without access to the related record, so do not put sensitive information in a primary-name field.
Using lookup fields in Dataverse: pros and cons
- ProsQuick access to related record names; supports efficient navigation
- ConsPrimary-name field visibility may expose sensitive data even if full record access is restricted
In this guide
- Choosing tables, records and relationshipsDecide what one row represents and how to connect records with one-to-many, many-to-many or self-referencing relationships.
- Avoiding repeated data in several no-code tablesFind conflicting copies of a fact, give current values one owner and distinguish linked displays from historical snapshots.
- Defining a reliable record identifierChoose a stable row key, keep it distinct from a business identifier and plan how imports and relationships use it.
