Chapter 05 of 41

Data Models

Before you build a house, you need a way to describe it: a floor plan, symbols for doors and windows, rules like "every room needs a door". Before you build a database, you need a similar way to describe the data. That "language for describing data" is called a data model.

Over the years, databases have used several data models: hierarchical, network, relational, entity-relationship, object-oriented, semi-structured and the various NoSQL models. In this chapter we will understand what a data model is, look at each major model using the same small example, and see why the relational model won for most applications.


What Is a Data Model?

A data model is a collection of concepts used to describe the structure of a database, the operations allowed on the data, and the constraints the data must satisfy.

So every data model has three parts:

Part

Question it answers

Relational example

Structure

How is data organised?

Tables (relations) with rows and columns

Operations

How do we read and change data?

Relational algebra / SQL: select, project, join, insert, update, delete

Constraints

What rules must data follow?

Primary keys, foreign keys, NOT NULL, CHECK

In the previous chapter, Three-Schema Architecture & Data Independence, we wrote a conceptual schema. That schema was written using a data model (the relational model). The schema is the house plan; the data model is the set of symbols and rules used to draw it.


Categories of Data Models

Data models are often grouped by how close they are to the user or to the machine:

Category

Also called

Describes

Examples

High-level

Conceptual

Data as users think about it: things and how they relate

ER model, UML class diagrams

Representational

Implementation / logical / record-based

Data as a DBMS organises it, but without byte-level detail

Relational, network, hierarchical, object models

Low-level

Physical

How data is stored: record formats, ordering, access paths

Storage structures, indexes, B+ trees

A typical design flow goes from top to bottom: draw an ER diagram (conceptual), convert it to tables (relational), and let the DBMS and DBA decide the physical storage.


The Running Example

We will represent the same small piece of data in every model:

  • Two departments: Engineering (Bengaluru) and Sales (Mumbai).

  • Engineering has employees Aarav, Priya and Rohan. Sales has Sneha, Vikram and Ananya.

  • To test each model, we add a many-to-many twist: employees work on projects, and Priya works on two projects, Payments and Search, while Payments also has Sneha.

Keep the many-to-many part in mind. It is exactly where older models struggled.


1. Hierarchical Model

The hierarchical model organises data as a tree. Each record has exactly one parent (except the root), and a parent can have many children. Relationships are therefore always one-to-many, parent to child.

                 Company
               /         \
     Engineering          Sales
     (Bengaluru)          (Mumbai)
     /    |    \          /   |    \
 Aarav  Priya  Rohan  Sneha Vikram Ananya
          |              |
       Projects       Projects
       /     \           |
  Payments  Search   Payments   <-- duplicated!

How data is accessed: by navigating from the root down: Company, then Engineering, then Priya, then her projects. The best-known hierarchical DBMS is IBM's IMS, built in the late 1960s and still running in some banks and large enterprises today.

The problem: a project with members from two departments cannot have two parents. So "Payments" must be stored twice, once under Priya and once under Sneha. That brings back redundancy and the risk of inconsistency (rename one copy and forget the other).

  • Pros: simple and very fast for data that is naturally a tree (an organisation chart, a file directory); parent-to-child lookups follow direct links.

  • Cons: cannot naturally represent many-to-many relationships; queries that do not follow the tree (e.g. "all employees on Payments") are awkward; programs depend on the navigation path, so changing the structure breaks them.


2. Network Model

The network model generalises the hierarchy into a graph: a record can have several parents. It was standardised by the CODASYL Data Base Task Group around 1971, and its ideas came from Charles Bachman's IDS system.

Relationships are represented as sets: each set type has an owner record type and a member record type (one owner, many members). A many-to-many relationship is handled by adding a link record that is a member of two sets:

Set "Employs" (owner: Department, members: Employees)

  Engineering --> Aarav, Priya, Rohan
  Sales       --> Sneha, Vikram, Ananya

Many-to-many through link records (W1, W2, W3 = Works_On records)

  Priya ---+---> [W1] ----------> Search
           |
           +---> [W2] ---+
                         +-----> Payments   (stored only once)
  Sneha -------> [W3] ---+

Each link record is a member of two sets: one owned by an
Employee and one owned by a Project.

Now "Payments" is stored once, and both Priya and Sneha point to it through link records.

How data is accessed: still navigational. A program follows pointers record by record: "find Priya, then get the first Works_On link, then get its project, then get the next link..." The programmer must know the exact graph structure.

  • Pros: represents many-to-many relationships without duplication; fast when following predefined links.

  • Cons: complex to design and program; adding a new kind of question often means new access code; changing the structure breaks programs (poor data independence).


3. Relational Model

In 1970, E. F. Codd at IBM proposed the relational model. His key idea: store all data in simple tables (relations) and connect them using values, not physical pointers.

Our example becomes four tables:

departments
+---------+-------------+-----------+
| dept_id | dept_name   | location  |
+---------+-------------+-----------+
|      10 | Engineering | Bengaluru |
|      20 | Sales       | Mumbai    |
+---------+-------------+-----------+

employees
+--------+--------------+---------+
| emp_id | name         | dept_id |
+--------+--------------+---------+
|      1 | Aarav Sharma |      10 |
|      2 | Priya Iyer   |      10 |
|      3 | Rohan Mehta  |      10 |
|      4 | Sneha Kapoor |      20 |
|      5 | Vikram Singh |      20 |
|      6 | Ananya Das   |      20 |
+--------+--------------+---------+

projects                   works_on
+---------+----------+     +--------+---------+
| proj_id | title    |     | emp_id | proj_id |
+---------+----------+     +--------+---------+
|     501 | Payments |     |      2 |     501 |
|     502 | Search   |     |      2 |     502 |
+---------+----------+     |      4 |     501 |
                           +--------+---------+

(The departments and employees rows are from our sample company database; projects and works_on are added just for this illustration.)

Notice:

  • The employee-to-department link is just the value dept_id in each employee row (a foreign key).

  • The many-to-many relationship is simply another table, works_on. "Payments" is stored once.

  • Any question can be asked, in any direction, with a declarative query. "Who works on Payments?" and "Which projects does Priya work on?" are equally easy. There is no fixed access path.

Why the relational model won:

  • Simplicity: everything is a table; easy to understand and teach.

  • Strong mathematical foundation: set theory, relational algebra and calculus, which make query optimization possible.

  • Declarative querying (SQL): you say what you want, not how to navigate.

  • Data independence: because there are no hard-coded pointers in programs, storage can change freely.

Early critics thought it would be too slow. Systems like IBM's System R and Berkeley's Ingres showed in the 1970s that good optimizers could make it fast, and by the 1980s relational DBMSs dominated. MySQL, PostgreSQL, Oracle, SQL Server and SQLite are all relational. The chapter Relational Model covers its terminology (relation, tuple, attribute, domain) in detail.

  • Pros: simple, flexible queries, strong theory, high data independence, mature tools.

  • Cons: deeply nested or highly connected data needs many joins; a fixed schema must be changed with ALTER TABLE; scaling writes across many servers is harder than in some NoSQL systems.


4. Entity-Relationship (ER) Model

The ER model, introduced by Peter Chen in 1976, is a conceptual (high-level) model. It is used to design a database on paper before choosing tables.

It describes the world using:

  • Entities: things, such as Employee, Department, Project.

  • Attributes: properties, such as name, salary, location.

  • Relationships: associations, such as Employee works in Department, Employee works on Project.

A simplified text version of the ER diagram for our example:

[Department] 1 ----< works_in >---- N [Employee] M ----< works_on >---- N [Project]
  dept_id                              emp_id                               proj_id
  dept_name                            name                                 title
  location

The ER model is not usually implemented directly by a DBMS. Instead, ER diagrams are converted into relational tables (exactly the four tables we saw above). This is covered across the chapters starting from ER Model and ending with Converting ER Diagrams to Tables.


5. Object-Oriented and Object-Relational Models

Object-Oriented Data Model

In the late 1980s and 1990s, object-oriented programming became popular. The object-oriented data model stores data as objects, just like objects in Java or C++: with attributes, methods, object identity, inheritance and references to other objects.

class Employee {
    int      empId;
    String   name;
    Department dept;          // direct reference to an object
    List<Project> projects;   // collection of references
}

Motivation: in relational systems, programmers constantly convert between objects in code and rows in tables. This mismatch is called the object-relational impedance mismatch. Object databases (for example db4o, ObjectDB, and earlier ObjectStore) remove it by storing objects directly.

Pure object databases stayed niche (used in CAD, telecom and some embedded systems). Today, most developers solve the mismatch with ORMs such as Hibernate, Django ORM or Prisma on top of a relational database.

Object-Relational Model

The object-relational model extends the relational model with object features: user-defined types, arrays and composite types, table inheritance and functions. PostgreSQL is the best-known example ("object-relational database"). It keeps the power of SQL and tables while adding richer types.


6. Semi-Structured Model (XML / JSON)

In the semi-structured model, data does not need a fixed schema decided in advance. Each record describes itself using tags or keys, and records of the same kind may have different fields. XML and JSON are the common formats.

Our Engineering department as a JSON document, generated by MySQL 8 from the sample database:

SELECT JSON_PRETTY(JSON_OBJECT(
         'dept_id', d.dept_id, 'dept_name', d.dept_name, 'location', d.location,
         'employees', JSON_ARRAYAGG(JSON_OBJECT('emp_id', e.emp_id, 'name', e.name))))
FROM departments d JOIN employees e ON e.dept_id = d.dept_id
WHERE d.dept_id = 10
GROUP BY d.dept_id;
{
  "dept_id": 10,
  "location": "Bengaluru",
  "dept_name": "Engineering",
  "employees": [
    {
      "name": "Aarav Sharma",
      "emp_id": 1
    },
    {
      "name": "Priya Iyer",
      "emp_id": 2
    },
    {
      "name": "Rohan Mehta",
      "emp_id": 3
    }
  ]
}

Two small observations: MySQL stores JSON objects in its own normalised form, so the keys come back in a different order from the one we wrote (key order in a JSON object is not meaningful); and the order of elements produced by JSON_ARRAYAGG is not guaranteed unless you control it.

The department and its employees are now one nested document instead of rows in two tables. That is convenient when an app always loads a department together with its employees, but the many-to-many projects part would again force us either to duplicate project data inside documents or to store references, much like the older models.

Modern relational DBMSs support semi-structured data too: MySQL and PostgreSQL both have a JSON column type.


7. NoSQL Data Models (A Quick Preview)

From the 2000s, web companies needed to store enormous amounts of data across many servers. This led to NoSQL ("not only SQL") systems, which use several data models:

NoSQL model

Structure

Examples

Good for

Key-value

A key maps to an opaque value

Redis, Amazon DynamoDB

Caching, sessions, counters

Document

Self-describing JSON-like documents

MongoDB, Couchbase

Catalogs, user profiles, content

Wide-column

Rows with flexible, grouped columns, spread across many nodes

Apache Cassandra, HBase

Huge write-heavy data, time series

Graph

Nodes and edges with properties

Neo4j, Amazon Neptune

Social networks, recommendations, fraud detection

Interestingly, the graph model is like a modern, declarative cousin of the old network model: it is excellent at highly connected, many-to-many data. These models are discussed properly in the NoSQL Databases chapter.


Comparison of the Main Data Models

Basis

Hierarchical

Network

Relational

Structure

Tree

Graph of records (owner-member sets)

Tables

How records are linked

Parent-child pointers

Pointers through sets

Matching values (keys)

Relationships

1:N only (M:N needs duplication)

1:N and M:N (via link records)

1:1, 1:N and M:N (via tables)

Querying

Navigational, procedural

Navigational, procedural

Declarative (SQL)

Data independence

Low

Low

High

Ease of use

Simple for tree data, rigid otherwise

Complex

Simple

Example system

IBM IMS

IDMS (CODASYL-based)

MySQL, PostgreSQL, Oracle


Common Mistakes Students Make

  • Calling the ER model an implementation model. ER is conceptual; it is used for design and then mapped to (usually) relational tables.

  • Confusing data model with schema. The data model is the toolkit (concepts and rules); the schema is a specific design built with it.

  • Saying the hierarchical model cannot represent M:N at all. It can, but only by duplicating records (or with special "virtual" pointers in IMS), which is why it is called unsuitable.

  • Thinking NoSQL means "no schema at all". The structure is flexible or implicit, but applications still assume some structure.


Interview Questions

1. What is a data model? What are its components?

A collection of concepts for describing the structure of a database, the operations on it and the constraints on the data. Its components are structure, operations and constraints.

2. Difference between hierarchical and network models?

Hierarchical organises records as a tree, so each child has one parent and only 1:N relationships are natural. Network organises records as a graph, where a record can have multiple owners, so M:N relationships can be represented through link records. Both are navigational.

3. Why did the relational model replace the hierarchical and network models?

It links data by values rather than pointers, uses a simple table structure, supports declarative queries in any direction, has a strong mathematical basis that enables optimization, and offers much better data independence.

4. Is the ER model a logical model?

No. It is a high-level conceptual model used during design. The ER design is later translated into a logical model, typically relational tables.

5. What is the object-relational impedance mismatch?

The difference between how programs represent data (objects with references, inheritance, collections) and how relational databases store it (flat rows linked by keys), which forces conversion code. ORMs and object databases address it.

6. Name the four main NoSQL data models.

Key-value, document, wide-column (column-family) and graph.


Summary

  • A data model defines structure, operations and constraints for describing data.

  • Models are conceptual (ER), representational (relational, network, hierarchical, object) or physical.

  • Hierarchical: tree, 1:N, duplicates data for M:N, navigational (IBM IMS).

  • Network: graph of owner-member sets, handles M:N, complex navigation (CODASYL).

  • Relational: tables linked by key values, declarative SQL, high data independence; the dominant model (Codd, 1970).

  • ER: conceptual design model (Chen, 1976). Object and object-relational models add object features. Semi-structured (JSON/XML) and NoSQL models offer flexible schemas and scale-out.

Next, in Database Languages (DDL, DML, DCL, TCL), we will see the different categories of commands we use to define, manipulate and control data in a relational DBMS.