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.
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.
| Level | What it shows | Typical audience | When it fits |
|---|---|---|---|
| Conceptual | Main entity types, key characteristics, relationships and business rules, in simple notation | Business leaders, analysts, architects | Agreeing scope and vocabulary at the start of a project |
| Logical | Attributes with data types and field lengths, plus relationships, in formal notation; no technical system requirements | Data architects, business analysts | Detailing the design before choosing a platform. IBM notes agile and DevOps teams often skip this level |
| Physical | How data is stored in a database: primary and foreign keys, associative tables and DBMS-specific settings such as performance tuning | Database administrators, developers | Building and tuning the schema on a chosen database |
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 type | How it works | Where it fits | Current status |
|---|---|---|---|
| Hierarchical | Parent-child tree of one-to-many relationships | Data that is naturally a tree | IBM notes it still appears in XML and GIS systems |
| Network and graph | Nodes and edges, allowing multiple parents and many-to-many links; see graph databases in big data analytics | Highly connected data | Used through graph databases |
| Relational | Tables joined by keys and queried with SQL; proposed by E.F. Codd in 1970 | Transactional systems needing integrity and low redundancy | The common default for operational data |
| Entity-relationship (ER) | Formal diagrams of entities, attributes and relationships | Design and documentation of any database | A design notation used for conceptual and logical work |
| Object-oriented | Real-world entities as objects in class hierarchies | Complex data such as multimedia | Specialised |
| Dimensional | Fact tables surrounded by dimension tables (star or snowflake schemas), designed by Ralph Kimball | Analytics and data warehouses, where fast retrieval matters more than storage efficiency | Widely used for warehouse and BI design |
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.
List the things, events or concepts in the data set, such as Customer, Address and Order. Each entity should be distinct and consistent.
Attributes distinguish one entity from another. A Customer might carry a name and phone number, and an Address a street, city and postal code.
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.
Check that the model reflects how the business actually uses the data, not only how a source system stores it.
Keys link data groups without repeating data. The level of normalisation balances less redundancy against query performance.
Review the model with stakeholders and repeat the process as business needs change. A data model is never finished once and left.
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 choice | Optimises for | Trade-off | Fits when |
|---|---|---|---|
| Normalised (for example third normal form) | Integrity and low redundancy | More joins at query time | Operational systems with frequent writes |
| Dimensional (star schema) | Filtering, grouping and summarising | Some repeated descriptive data; needs a consistent grain | Warehouses, BI and semantic models |
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.
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.
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.
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.
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.
| Tool | What its site says | Best fit |
|---|---|---|
| ER/Studio | According to its site, a data modeling platform that lets teams map business-facing designs and convert logical versions into database-specific physical structures | Enterprise teams moving from logical to physical design |
| DbSchema | According 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 JDBC | Teams working across several database engines |
| HeidiSQL | According to its site, free, open-source software for working with databases, supporting MariaDB, MySQL, MS SQL, PostgreSQL, SQLite and others | Developers managing MySQL-family and similar databases |
| Toad Data Modeler | According 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 database | Teams 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.
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.
| Area | Decision to make | Practical control |
|---|---|---|
| Governance | Who owns each entity and definition | Named owner, reviewed change process, catalogue entry |
| Security | Which attributes are sensitive and who may read them | Classify attributes in the logical model and carry access rules into the physical model |
| Quality | What valid data looks like | Constraints and checks defined with the model, not after it |
| Cost | Redundancy versus joins, stored versus computed | Record the trade-off for each materialised table |
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.
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.
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.
Common types are hierarchical, network, relational, entity-relationship, object-oriented and dimensional. Relational suits operational systems and dimensional (star schema) suits analytics and warehouses.
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.
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.
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.