XenonStack Data Insights on DevOps, Cloud and Data Science

Data Modelling: Types, Steps and Best Practices

Written by Navdeep Singh Gill | 21 November 2023

What is data modelling, and why does it matter to enterprise leaders?

Data modelling is the process of creating a visual representation of a whole information system or parts of it, showing how data points and structures connect and how data is grouped, formatted and described (IBM). In practice it is the design step that decides what your business records, how those records relate, and what every report, application and AI agent can rely on later.

This guide covers the three levels of a data model, the main model types, the six-step process, normalisation versus dimensional design, how to choose a tool, and how modelling changes when AI agents consume your data. It also links to our guides on databases, SQL, NoSQL and NewSQL and NoSQL databases.

What are the three levels of a data model?

IBM describes three levels of abstraction. Moving from conceptual to physical, each level adds detail and ties the design more closely to a specific system.

LevelWhat it showsTypical audienceWhen it fits
ConceptualMain entity types, key characteristics, relationships and business rules, in simple notationBusiness leaders, analysts, architectsAgreeing scope and vocabulary at the start of a project
LogicalAttributes with data types and field lengths, plus relationships, in formal notation; no technical system requirementsData architects, business analystsDetailing the design before choosing a platform. IBM notes agile and DevOps teams often skip this level
PhysicalHow data is stored in a database: primary and foreign keys, associative tables and DBMS-specific settings such as performance tuningDatabase administrators, developersBuilding and tuning the schema on a chosen database

Which types of data model exist, and when does each fit?

The model type decides how data is organised and queried. IBM lists hierarchical, relational, entity-relationship, object-oriented and dimensional models; the network and graph models below are covered in our own guides.

Model typeHow it worksWhere it fitsCurrent status
HierarchicalParent-child tree of one-to-many relationshipsData that is naturally a treeIBM notes it still appears in XML and GIS systems
Network and graphNodes and edges, allowing multiple parents and many-to-many links; see graph databases in big data analyticsHighly connected dataUsed through graph databases
RelationalTables joined by keys and queried with SQL; proposed by E.F. Codd in 1970Transactional systems needing integrity and low redundancyThe common default for operational data
Entity-relationship (ER)Formal diagrams of entities, attributes and relationshipsDesign and documentation of any databaseA design notation used for conceptual and logical work
Object-orientedReal-world entities as objects in class hierarchiesComplex data such as multimediaSpecialised
DimensionalFact tables surrounded by dimension tables (star or snowflake schemas), designed by Ralph KimballAnalytics and data warehouses, where fast retrieval matters more than storage efficiencyWidely used for warehouse and BI design

What are the six steps of data modelling?

IBM sets out a process that works at any level. Teams work with stakeholders to understand requirements, then refine the model as the business changes.

1. Identify the entities

List the things, events or concepts in the data set, such as Customer, Address and Order. Each entity should be distinct and consistent.

2. Identify each entity's attributes

Attributes distinguish one entity from another. A Customer might carry a name and phone number, and an Address a street, city and postal code.

3. Identify the relationships

Record how entities relate, for example each customer lives at an address and each order ships to one. Entity-relationship diagrams and UML are common notations.

4. Map attributes to entities

Check that the model reflects how the business actually uses the data, not only how a source system stores it.

5. Assign keys and choose a normalisation level

Keys link data groups without repeating data. The level of normalisation balances less redundancy against query performance.

6. Finalise and validate

Review the model with stakeholders and repeat the process as business needs change. A data model is never finished once and left.

Normalised or dimensional: how should you design for your workload?

Microsoft's normalisation guide describes normalisation as organising data into tables and relationships according to rules that protect the data and make the database more flexible by eliminating redundancy and inconsistent dependency. It describes third normal form as considered the highest level necessary for most applications. Normalised designs suit operational systems that write and update records.

Analytics workloads follow a different pattern. Microsoft Learn describes star schema as a mature approach widely adopted by relational data warehouses, where dimension tables enable filtering and grouping and fact tables enable summarisation. The Microsoft Fabric documentation adds that a star schema is optimised for analytic query workloads and often delivers high performance because of fewer table joins.

Design choiceOptimises forTrade-offFits when
Normalised (for example third normal form)Integrity and low redundancyMore joins at query timeOperational systems with frequent writes
Dimensional (star schema)Filtering, grouping and summarisingSome repeated descriptive data; needs a consistent grainWarehouses, BI and semantic models

What are the best practices for data modelling?

Set the grain first

The Kimball Group defines grain as what a single fact table row represents, and says it must be set before dimensions and facts are chosen. Starting at atomic grain, the lowest level at which the business process captures data, helps a design cope with unpredictable user queries. Keep each grain in its own fact table.

Name things so the business can read them

Agree naming conventions for entities, attributes and keys with business owners, and apply them in every layer. Consistent names make a model reviewable by people who do not write SQL.

Decide what is stored and what is computed

For each derived table or view, record whether it is materialised or computed at query time, who refreshes it and how stale it may be. This is a cost and freshness decision, not only a technical one.

Build permission and governance into the model

Mark sensitive attributes, assign owners and define who can read each entity before the model reaches production. A catalogue makes this visible: see our guides to enterprise data catalog tools, open and unified metadata platforms and why data quality matters.

What do data modelling tools do, and how do you choose one?

Data modelling tools help teams draw entity-relationship diagrams, generate schemas, compare models with live databases and share designs. The table summarises each product as its own site describes it; confirm current versions, database support and licensing on the vendor page before buying.

ToolWhat its site saysBest fit
ER/StudioAccording to its site, a data modeling platform that lets teams map business-facing designs and convert logical versions into database-specific physical structuresEnterprise teams moving from logical to physical design
DbSchemaAccording to its site, a graphical tool for modelling, documenting and querying relational and document-style databases, connecting to more than a hundred engines, mostly through JDBCTeams working across several database engines
HeidiSQLAccording to its site, free, open-source software for working with databases, supporting MariaDB, MySQL, MS SQL, PostgreSQL, SQLite and othersDevelopers managing MySQL-family and similar databases
Toad Data ModelerAccording to its site, software for designing, documenting and maintaining database structures, with logical and physical models, SQL generation and model comparison. The supported-version list on the page should be checked against your databaseTeams needing model comparison and synchronisation

Choose by the databases you run, whether you need logical and physical models, how models are versioned and reviewed, and whether the tool can read your metadata catalogue. Pilot with one real subject area rather than a sample schema. For teams putting models into production pipelines, see our data engineering solutions.

How does data modelling change when AI agents consume your data?

Agents ask questions and take actions through the data model, so ambiguous entities, undocumented keys and unclear ownership become direct risks: an agent will use whatever the model lets it reach. A model written for human analysts therefore needs more explicit business definitions, grain, relationships and access rules before an agent relies on it.

In practice that means treating the model, its definitions and its policies as governed context. ElixirData Context OS keeps governed context, versioned policy and decision traces in one decision layer above AI agents, and AgentAnalyst lets teams ask questions of enterprise data in plain language and get real-time insights. For the wider picture, read our guides on agentic AI for data quality and agentic AI for data management, and on data mesh adoption if domain teams own their models.

What are the governance, security and cost considerations?

AreaDecision to makePractical control
GovernanceWho owns each entity and definitionNamed owner, reviewed change process, catalogue entry
SecurityWhich attributes are sensitive and who may read themClassify attributes in the logical model and carry access rules into the physical model
QualityWhat valid data looks likeConstraints and checks defined with the model, not after it
CostRedundancy versus joins, stored versus computedRecord the trade-off for each materialised table

How should you adopt data modelling, and when should you not?

Start with one business domain and a conceptual model agreed with its owners. Add the logical model where several systems must agree, and the physical model for the chosen platform. Review the model whenever a source system, regulation or business process changes, and keep it in version control with the code that depends on it.

A full three-level model is not always worth the effort. A short-lived prototype, a single-application database or a throwaway analysis may need only a physical schema. IBM notes that agile and DevOps teams often skip the logical level. Skip it deliberately, and document the entities and keys that others will rely on.

To map one data decision your agents should make, and the model, context and policy it depends on, book a Context Workshop. See what happens in the workshop.

Frequently asked questions

What is data modelling?

Data modelling is the process of creating a visual representation of an information system or parts of it, showing how data is structured, grouped, related and described. It defines what data a business process needs and how to organise it.

What are the three levels of a data model?

Conceptual, logical and physical. The conceptual model maps entities, relationships and business rules. The logical model adds attributes, data types and formal notation. The physical model defines how data is stored in a specific database, including keys and tuning.

What are the types of data models?

Common types are hierarchical, network, relational, entity-relationship, object-oriented and dimensional. Relational suits operational systems and dimensional (star schema) suits analytics and warehouses.

What are the steps of data modelling?

Six steps: identify entities, identify their attributes, identify relationships, map attributes to entities, assign keys and choose a normalisation level, then finalise and validate the model. Repeat as business needs change.

What is normalisation in data modelling?

Normalisation organises data into tables and relationships by rules that remove redundancy and inconsistent dependency. Keys link data groups without repeating the data, for example a customer key linking to address and order history.

Why does data modelling matter for AI agents?

Agents work through the model, so unclear entities, keys, definitions and access rules become risks. Explicit definitions, grain, relationships and permissions give agents governed context to rely on.