What Is Data Modeling? From Requirements to Conceptual, Logical and Physical Models

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0, Cumulative Update 10), and the C# example with .NET 10 and Entity Framework Core (EF Core) 10.0.15.

Guess First: Who Designed This Schema?

A small bookshop has kept its orders in an Excel file for months. A new web application is on the way, and a developer on the team looks at the order screen and writes this class. Whatever is on the screen is in the class: the customer's name, email and city, the titles of the books in the basket, their prices and the total.

public class Order
{
    public int Id { get; set; }
    public DateTime OrderDate { get; set; }
    public string CustomerName { get; set; } = "";
    public string CustomerEmail { get; set; } = "";
    public string City { get; set; } = "";
    public List<string> BookTitles { get; set; } = [];
    public List<decimal> Prices { get; set; } = [];
    public decimal Total { get; set; }
}

public class ScreenContext : DbContext
{
    public DbSet<Order> Orders => Set<Order>();

    protected override void OnConfiguring(DbContextOptionsBuilder options) =>
        options.UseSqlServer(Environment.GetEnvironmentVariable("BOOKSTORE_DB")
                ?? "Server=localhost;Database=Bookstore;Trusted_Connection=True;TrustServerCertificate=True")
               .LogTo(Console.WriteLine, [SqlServerEventId.DecimalTypeDefaultWarning]);
}

Without looking at a migration, guess: what table will EF Core generate for this class? Where does the BookTitles list go? How many rows change when a book title is corrected? When the same customer orders a second time, in how many places is her name written?

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

using var db = new ScreenContext();
Console.WriteLine(db.Database.GenerateCreateScript());

db.Database.EnsureDeleted();
db.Database.EnsureCreated();
db.Orders.Add(new Order
{
    OrderDate = new DateTime(2026, 3, 2),
    CustomerName = "Ada Lovelace",
    CustomerEmail = "ada@example.com",
    City = "Istanbul",
    BookTitles = ["Dune", "Good Omens"],
    Prices = [390.50m, 340.90m],
    Total = 731.40m
});
db.SaveChanges();

var titles = db.Database
    .SqlQueryRaw<string>("SELECT BookTitles AS Value FROM Orders")
    .ToList();
Console.WriteLine($"Stored BookTitles: {titles[0]}");

warn: 14.11.2026 00:15:49.024 SqlServerEventId.DecimalTypeDefaultWarning[30000] (Microsoft.EntityFrameworkCore.Model.Validation)
      No store type was specified for the decimal property 'Total' on entity type 'Order'. This will cause values to be silently truncated if they do not fit in the default precision and scale. Explicitly specify the SQL server column type that can accommodate all the values in 'OnModelCreating' using 'HasColumnType', specify precision and scale using 'HasPrecision', or configure a value converter using 'HasConversion'.
CREATE TABLE [Orders] (
    [Id] int NOT NULL IDENTITY,
    [OrderDate] datetime2 NOT NULL,
    [CustomerName] nvarchar(max) NOT NULL,
    [CustomerEmail] nvarchar(max) NOT NULL,
    [City] nvarchar(max) NOT NULL,
    [BookTitles] nvarchar(max) NOT NULL,
    [Prices] nvarchar(max) NOT NULL,
    [Total] decimal(18,2) NOT NULL,
    CONSTRAINT [PK_Orders] PRIMARY KEY ([Id])
);
Stored BookTitles: ["Dune","Good Omens"]

One table. Every text is nvarchar(max), the book titles sit in one column as a JSON (JavaScript Object Notation) array, the prices in another JSON array, and nothing guarantees that the two stay in the same order. The primitive collection support that arrived in EF Core 8 (the "EF Core 8 Yenilikleri" post in the EF Core series) did its job perfectly here: the class had a list, so the database has a list. The problem is not EF Core. The problem is that nobody asked "Which things do we store in this database, and under which rules?" There is no book table, no author, no customer. Ada's email will be written again on her second order, and a typo in a book title will be fixed order by order. The warning line says something important too: nobody chose a precision for Total; the framework's default did.

This series teaches you to ask exactly that question. Throughout the series we take one flat "order spreadsheet" and turn it step by step into the Bookstore schema. In this first post we look at what data modeling is, the three levels of a model and how a model is extracted from requirement sentences.

What Is Data Modeling and Why Does It Come First?

Data modeling is deciding explicitly, before writing code, which data a system keeps, how its parts relate to each other and which rules they must obey. Its output is a model: things, their properties, the relationships between them and the constraints. The model can live in a diagram, a document or directly in a DDL (Data Definition Language) script; what matters is that the decisions were made on purpose.

The theoretical foundation of relational databases is E. F. Codd's 1970 paper "A Relational Model of Data for Large Shared Data Banks" in CACM (Communications of the Association for Computing Machinery). Codd's concern was that programs using the data should not be affected by how the data sits on disk. In 1976 Peter Chen published the ER (Entity-Relationship) model and proposed a drawing language that connects the business language of "things and the relationships between them" to tables. The data modeling practice we use today is built on top of these two.

Why does the model come first? Because a schema mistake ages differently from a code mistake. You fix a badly written method in one commit. To fix a badly built table you have to move its millions of rows and change every query, report and integration that reads it. An index, a cache or a bigger server can make a wrong model faster, but never right: if book titles live in a JSON array, the question "Who bought Dune the most?" means opening every order and looking inside, every time, and no index changes what that question means.

The Three Levels of a Model: Conceptual, Logical, Physical

In data modeling practice a model is usually thought of at three levels. Each level adds new decisions to the previous one, and each level has a different reader.

The conceptual model is for talking to the business. Questions such as "Can an order contain several books?" or "Can a book have several authors?" are asked and answered at this level. There are no columns here; even if there were, the business would not read them.

The logical model translates the concepts into the language of the relational model: the attributes of each entity, the cardinality of each relationship, the key and the dependencies of each table. It is still product independent; the same logical model can be built in SQL Server or in PostgreSQL. Normalization is the job of this level and fills the second block of the series.

The physical model is the logical model's counterpart on a particular DBMS (Database Management System): NVARCHAR or VARCHAR, INT or BIGINT, which constraint, which index, which collation. This series looks at it through SQL Server 2025 and adds notes where PostgreSQL 18 behaves differently.

A note: when database literature says "three levels", most people first think of the external, conceptual and internal schemas from the 1975 interim report of the SPARC (Standards Planning and Requirements Committee) study group of ANSI (American National Standards Institute). That split describes the architecture of a DBMS; the conceptual/logical/physical split here is about the steps of a design process. The names are similar, but they are not the same thing.

Extracting a Model from Requirement Sentences

A model does not start on a blank page; it starts from requirement sentences. Our meeting with the bookshop owner produced these notes:

• R1. A book can have several authors; the order of the authors on the cover matters.
• R2. Every book has an ISBN (International Standard Book Number), and no two books can carry the same ISBN.
• R3. A customer can buy several books in one order, and several copies of the same book.
• R4. The price at the time of the order never changes; even if the catalogue price changes, an old order shows the same amount.
• R5. Customers sign in with their email address and can change it.
• R6. An order can be cancelled but not deleted; accounting must see the history.
• R7. Every book belongs to a category, and categories are nested (Science Fiction sits under Fiction).

While reading these sentences we pick out three things. Nouns are entity candidates: book, author, customer, order, category. Verbs are relationship candidates: a customer places an order, an order contains books, an author writes books. Numbers, quantifiers such as "several", "at most" and "every", and words such as "never changes" and "not deleted" are rules; they become cardinality and constraints.

R3 hides an entity. "Several books in one order, several copies of the same book" contains no noun for it, but "copies" has to be an attribute of something. It is not an attribute of the order, because the order has several books. It is not an attribute of the book either, because the same book is sold in different quantities in different orders. Quantity is an attribute of "this book in this order". So there is a separate concept, the order item, and the business never gave it a name. Finding the entities that do not appear in the requirements is the most valuable part of modeling.

R4 says something else about the same item: the price at the time of the order is not the book's price but the item's price. The catalogue list_price and the item's unit_price are two different facts. One says "what this book sells for today", the other "what this book sold for in this order". Mistaking this for a repetition and collapsing it into one column is one of the classic mistakes we will meet later in the series.

R5 announces a key decision in advance: if the email can change, the email cannot be the customer's identity. The "order matters" in R1 says that the relationship itself has an attribute; the author order belongs neither to the book nor to the author, but to the relationship "this author on this book". R6 makes delete behaviour part of the model: for orders we will design a status transition, not a DELETE. R2 looks innocent, but we will come back to it in the 4th post of the series when we meet books without an ISBN.

Writing Down Access Patterns as Input to the Model

Requirements say what the data is; access patterns say how it will be touched. The logical model is not bent to fit access patterns, but if you do not have this list when you make physical decisions, you pick indexes, denormalization and even table boundaries by guesswork. The first list for the bookshop:

• Book detail page: one book by id or ISBN, with its authors, category and average rating. The most read page.
• Category page: active books in a category and its subcategories, paged and sorted.
• Placing an order: the order, its items and the stock decrement in one transaction. The most critical write.
• Customer order history: one customer's orders, newest first.
• Monthly sales report: revenue by category and month. Rare but heavy.

Writing a rough frequency and size next to each line pays off: "detail page dozens of times per second, orders a few per minute, report once a month". These numbers come from the calculation in the "Back-of-the-Envelope Estimation" post of the System Design series. The report line also carries a warning: heavy analytical reads do not have to live in the OLTP (Online Transaction Processing) schema that writes orders. We come back to this in the 10th post of the series, with dimensional models and the star schema.

From Logical to Physical: The Journey of a Decision

Let's build the physical counterpart of R1, R2 and R4 in SQL Server. The names match the Bookstore schema of the SQL Temelleri series: snake_case, plural table names, named constraints. The details of writing CREATE TABLE and constraints are in the 3rd and 4th posts of the SQL Temelleri series; the focus here is which requirement each line comes from.

CREATE TABLE dbo.books (
    book_id    INT IDENTITY(1,1) NOT NULL,
    isbn       CHAR(13)          NOT NULL,
    title      NVARCHAR(200)     NOT NULL,
    list_price DECIMAL(10,2)     NOT NULL,
    CONSTRAINT PK_books PRIMARY KEY (book_id),
    CONSTRAINT UQ_books_isbn UNIQUE (isbn),
    CONSTRAINT CK_books_list_price CHECK (list_price >= 0)
);

CREATE TABLE dbo.authors (
    author_id  INT IDENTITY(1,1) NOT NULL,
    first_name NVARCHAR(100)     NOT NULL,
    last_name  NVARCHAR(100)     NOT NULL,
    CONSTRAINT PK_authors PRIMARY KEY (author_id)
);

CREATE TABLE dbo.book_authors (
    book_id      INT     NOT NULL,
    author_id    INT     NOT NULL,
    author_order TINYINT NOT NULL,
    CONSTRAINT PK_book_authors PRIMARY KEY (book_id, author_id),
    CONSTRAINT FK_book_authors_books FOREIGN KEY (book_id) REFERENCES dbo.books (book_id),
    CONSTRAINT FK_book_authors_authors FOREIGN KEY (author_id) REFERENCES dbo.authors (author_id)
);

CREATE TABLE dbo.orders (
    order_id   INT          NOT NULL,
    ordered_at DATETIME2(0) NOT NULL,
    CONSTRAINT PK_orders PRIMARY KEY (order_id)
);

CREATE TABLE dbo.order_items (
    order_item_id INT IDENTITY(1,1) NOT NULL,
    order_id      INT               NOT NULL,
    book_id       INT               NOT NULL,
    quantity      INT               NOT NULL,
    unit_price    DECIMAL(10,2)     NOT NULL,
    CONSTRAINT PK_order_items PRIMARY KEY (order_item_id),
    CONSTRAINT UQ_order_items_order_book UNIQUE (order_id, book_id),
    CONSTRAINT FK_order_items_orders FOREIGN KEY (order_id) REFERENCES dbo.orders (order_id),
    CONSTRAINT FK_order_items_books FOREIGN KEY (book_id) REFERENCES dbo.books (book_id),
    CONSTRAINT CK_order_items_quantity CHECK (quantity > 0)
);

UQ_books_isbn is the counterpart of R2, the book_authors table with its author_order of R1, order_items.quantity and UQ_order_items_order_book of R3, and order_items.unit_price of R4. I left the orders table with only two columns here; the customer and status columns come in later posts. Now let's see that R4 really holds: we create an order and then change the catalogue price.

INSERT INTO dbo.books (isbn, title, list_price) VALUES ('9798900000268', N'Dune', 390.50);
INSERT INTO dbo.orders (order_id, ordered_at) VALUES (1003, '2026-03-09T10:15:00');
INSERT INTO dbo.order_items (order_id, book_id, quantity, unit_price) VALUES (1003, 1, 2, 351.45);

UPDATE dbo.books SET list_price = 420.00 WHERE isbn = '9798900000268';

SELECT b.title, b.list_price, oi.order_id, oi.quantity, oi.unit_price
FROM dbo.books AS b
JOIN dbo.order_items AS oi ON oi.book_id = b.book_id;

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
title  list_price  order_id  quantity  unit_price
-----  ----------  --------  --------  ----------
Dune   420.00      1003      2         351.45

(1 rows affected)

The catalogue price is now 420.00, and the item of order 1003 still shows 351.45. No trigger or application code provides this behaviour; the model itself does, because the two prices are two separate facts stored in two separate places. In the EF Core model at the start, the Prices list lived with the order, not with the item, and the only answer to "Which book is this price for?" was the position in the array.

The Series' Working Material: The Order Spreadsheet

The bookshop's reality today is this file. One sheet, one row per order, the books in an order separated by commas, the authors nested with semicolons and commas. Exported from Excel as CSV (Comma-Separated Values):

order_no,order_date,customer,email,phone,city,region,shipping_address,isbns,books,authors,prices,quantities,total
1001,2026-03-02,Ada Lovelace,ada@example.com,+90 532 000 0001,Istanbul,Marmara,"Moda Cd. 12, Kadikoy","9798900000268, 9798900000428","Dune, Good Omens","Frank Herbert; Terry Pratchett, Neil Gaiman","390.50, 340.90","1, 1",731.40
1002,2026-03-05,Alan Turing,alan@example.com,+90 533 000 0002,Ankara,Central Anatolia,"Tunali Hilmi Cd. 5, Cankaya",9798900000572,The Pragmatic Programmer,"Andrew Hunt, David Thomas",1150.00,1,1150.00
1003,2026-03-09,Ada Lovelace,ada@example.com,+90 532 000 0001,Istanbul,Marmara,"Moda Cd. 12, Kadikoy","9798900000305, 9798900000268","The Left Hand of Darkness, Dune","Ursula K. Le Guin; Frank Herbert","660.00, 351.45","1, 2",1362.90
1004,2026-03-12,Grace Hopper,grace@example.com,+90 535 000 0003,Izmir,Aegean,"Kibris Sehitleri Cd. 40, Konak",9798900000411,A Wizard of Earthsea,Ursula Le Guin,200.50,3,601.50
1005,2026-03-15,Alan Turing,alan@example.com,+90 533 000 0002,Ankara,Central Anatolia,"Tunali Hilmi Cd. 5, Cankaya",9798900000428,Good Omens,"Terry Pratchett, Neil Gaiman",340.90,1,340.90

Don't look down on this file; most systems start like this in real life, and it did its job for months. But every topic of the series is hidden in it. In order 1003 Dune was sold for 351.45, in 1001 for 390.50, so the price belongs to the item, not to the book. Le Guin is written "Ursula K. Le Guin" in 1003 and "Ursula Le Guin" in 1004. Ada's phone repeats on two rows, and updating one while forgetting the other is a matter of time. The region follows from the city, yet both are written on every row. The total column can be computed from prices and quantities. In the 5th post of the series we will load this file into SQL Server as it is and watch insert, update and delete anomalies live; in the 6th post we will split it into tables step by step with 1NF, 2NF and 3NF and arrive at the Bookstore schema.

In Production

Keep the model next to the code. An ER diagram on a Confluence page last updated two years ago is more dangerous than no diagram at all, because people trust it. In the next post we will see how to keep the ER diagram as text in the repository with Mermaid and diff it in a pull request.

Count the cost of schema changes from the start. Changing the meaning of a column in a live table means adding a new column and moving the data, waiting for everyone who reads the old column to switch, and only then removing the old one. The deployment side of these steps is in the "Migration'ların Deploy'daki Yeri" post of the Deployment Stratejileri series; what you can do on the modeling side is to keep decisions that are likely to change (such as email being an identity) out of keys from the beginning.

Make the requirements and the access pattern list the input of the schema review. If the answers to "Why is this column nullable?" and "Why is this a separate table?" can be tied to a requirement number, the model can be defended. If they cannot, either a requirement is missing or a column is extra. In the last post of the series we turn this into a schema review checklist.

Make the ORM (Object-Relational Mapping) a tool of the model, not its owner. Working code-first in EF Core is perfectly legitimate, but read the SQL the migration generates every time and ask "Would I have built this table like this if I had designed it?" The "EF Core Migrations" post of the EF Core series shows how to get the script a migration produces.

Common Pitfalls

Deriving the schema "automatically" from ORM classes. The example at the start is exactly that: the class imitated the screen, the table imitated the class, and nobody put a decision in between. Defaults (every text nvarchar(max), every decimal decimal(18,2), every list JSON) produce a schema that technically works but knows nothing about the business rules.

Building tables around the screen design. A screen is an access pattern, not a model. The fact that the customer name, the books and the total sit on the same card of the order screen today does not mean they must sit in the same table; tomorrow the same data will be shown in a different layout in the mobile app and with a completely different grouping on the report screen.

Missing the invisible entity in the requirements. Concepts the business never named, such as the order item, usually show up when you ask where an attribute belongs. Ask "Whose fact is this?" for every attribute.

Mistaking a copy for a repetition, or a repetition for a copy. Even when unit_price and list_price hold the same value, they are two separate facts. Conversely, a customer phone written again on every row is not a separate fact but a repetition of the same one. Which is which is decided by the business rule, not by looking at the data.

Never writing down access patterns, or building the model around them. Both extremes are bad: physical design without the list is guesswork, and a logical model bent to fit the list breaks with the first new screen. The logical model cares about the data, the physical design about access.

A Small Experiment

★☆☆ The bookshop wants to sell gift vouchers: every voucher has a code and an amount, can be given to a customer and can be spent piece by piece across several orders. Extract entity, relationship and rule candidates from these sentences. Is there an invisible entity?

★★☆ Split the Order class from the start into Customer, Book and OrderItem classes, take the GenerateCreateScript() output again and compare the two scripts. For which columns does the framework still decide the type?

★★★ The bookshop is adding an "author page": the author's biography, all their books, each book's average rating and the other authors they co-wrote with. Write the access pattern for this page and list which tables you need to join in the current model. Is something missing from the model?

We have seen the three levels of a model and how to extract entities from requirements. In the next post we move to ER modeling, the common language of the conceptual and logical models: entity and attribute types, cardinality and optionality, how the Chen and Crow's Foot notations draw the same model, and keeping the first ER draft of the Bookstore in code with Mermaid.