Understanding the Messy Reality of Information

Data models are maps, not replicas of reality: why entities, attributes, relationships and the type/instance distinction are never as clean as they look, and how to model them anyway, from customer definitions to the German energy market.

Published

Surreal landscape.

The Modeling Myth

We live in a world obsessed with data. We collect it, store it, analyze it, and build entire systems around it. But it’s important to remember that the way we organize and represent this data (our data models) is never a completely accurate picture of the real world. They’re more like simplified maps than perfect replicas. And understanding that difference is absolutely crucial for building anything that uses data (which is pretty much everything these days!).

The Map is not the Territory

Think about a map of a city. It shows you the streets, maybe some landmarks, and perhaps the subway lines. Useful, right? But it doesn’t show you the smell of the bakery on the corner, the sound of the busker playing guitar, the feeling of the sun on your face, or the conversation happening in the cafe. The map is a representation, a useful tool, but it’s not the city itself.

The map is not the territory

As the saying goes, “The map is not the territory.” This isn’t just a interesting philosophical idea. It’s a fundamental truth about all representations, including data models. They’re artificial constructs, useful ways to deal with information, but they always involve simplification and abstraction. Just like a formal grammar doesn’t perfectly capture how we actually speak, a data model never fully captures the messy, subjective, ever-changing reality it tries to represent. We’re always making choices about what to include, what to leave out, and how to categorize things. And those choices are inherently subjective.

In the following sections, we’ll dive deeper into specific aspects of data modeling: defining “things”, dealing with ambiguous attributes, and understanding complex relationships, always keeping this “modeling myth” in mind. We’re not trying to create a perfect mirror of reality. We’re trying to create a useful map that helps us navigate it. By embracing the imperfection, we can build better, more insightful, and more useful information systems.

What Exactly Are We Talking About?

Data modeling seems straightforward, right? We identify the “things” (entities) we need to track and define their relationships. But figuring out what constitutes a single “thing” is surprisingly tricky.

The problem is that what looks like one “thing” in one situation might be several “things” in another. We humans are great at using context to understand what someone means. We usually don’t even think about it! But when we try to combine data from different systems or perspectives. That’s when these hidden assumptions cause major headaches. Suddenly, we have to be very specific about what our data represents in the real world.

Surreal profile pictures of two women.

Sounds straightforward, doesn’t it? But consider these scenarios:

There’s no single, universally “correct” answer. What counts as “one customer” depends entirely on why the data is being collected and used. A marketing team might want to track potential customers, while the accounting department only cares about those who have actually made a purchase.

graph TD
    classDef customerNode fill:#18230F,stroke:#1F7D53,stroke-width:2px,color:#fff
    classDef typeNode fill:#27391C,stroke:#1F7D53,stroke-width:1px,color:#fff
    classDef attributeNode fill:#255F38,stroke:#1F7D53,stroke-width:0px,color:#000
    linkStyle default stroke:#1F7D53

    Customer["Customer"]:::customerNode

    Customer --> Individual("Individual"):::typeNode
    Customer --> Household("Household"):::typeNode
    Customer --> Business("Business"):::typeNode

    Individual --> Ind_CustomerID["CustomerID"]:::attributeNode
    Individual --> Ind_Name["Name"]:::attributeNode
    Individual --> Ind_DOB["DateOfBirth"]:::attributeNode

    Household --> HH_CustomerID["CustomerID"]:::attributeNode
    Household --> HH_Name["Name"]:::attributeNode
    Household --> HH_Address["Address"]:::attributeNode

    Business --> Bus_CustomerID["CustomerID"]:::attributeNode
    Business --> Bus_Name["Name"]:::attributeNode
    Business --> Bus_ContactPerson["ContactPerson"]:::attributeNode

This diagram visualizes a customer model with categories for Individual, Household, and Business customers, each with example attributes.

This ambiguity isn’t limited to business concepts. Let’s look at some other examples:

A “Course”: In a university database, is a “course” the abstract offering (like “Introduction to Database Systems”), a specific section of that course (meeting at a particular time and place), or even an individual student’s enrollment in that section? The registrar, the department, and the student might all have slightly different perspectives.

mindmap
  root((Course))
    Section
      SectionID
      CourseID
      Time
      Location
      Instructor
    Enrollment
      EnrollmentID
      StudentID
      SectionID
      Grade
    CourseDetails
      CourseID
      CourseName
      Description

This mind map visualizes the structure of a “Course”. It breaks down the course into key components like Sections, Enrollment, and Course Details, each with associated attributes.

A “Song”: In a music streaming service, is a “song” the original composition, a specific recording of that song by a particular artist, or even a user’s instance of playing that song (a “play”)? The rights management system, the recommendation engine, and the user’s listening history would all treat “song” differently.

stateDiagram-v2
    state "Song: Different Perspectives" as Song {
        state "Composition" as Composition {
            Title
            Composer
            Lyrics
        }
        state "Recording" as Recording {
            Artist
            Album
            ReleaseDate
        }
        state "Playback Instance" as PlaybackInstance {
            string
            UserID
            Timestamp
            Device
        }
        [*] --> Composition
        [*] --> Recording
        [*] --> PlaybackInstance
    }

This diagram represents the “Song” entity using a state diagram. It defines three key states: “Composition”, “Recording”, and “Playback Instance”, illustrating the attributes related to each perspective of a song.

An “Event”: In a calendar application, is an “event” a single occurrence, a recurring series (like a weekly meeting), or even just a time slot (regardless of whether something is scheduled)? A user might define “event” differently depending on whether they are scheduling a one-time appointment or a recurring class.

flowchart LR
    subgraph "Event: Interpretations"
        A[Single Occurrence]
        B[Recurring Series]
        C[Time Slot]
    end

    A --> D["e.g., Doctor's Appointment"]
    B --> E["e.g., Weekly Team Meeting"]
    C --> F["e.g., 9:00 - 10:00 AM,<br/>regardless of content"]

This graph illustrates different interpretations of the concept “Event”. It categorizes events into “Single Occurrence”, “Recurring Series”, and “Time Slot”, providing examples for each interpretation.

The bottom line is that even seemingly simple concepts can have multiple valid interpretations. Before you can build a data warehouse (or any information system), everyone involved needs to agree on what each “thing” represents for their specific purpose.

Why “Simple” Attributes Aren’t So Simple

We tend to think of attributes as those easy little labels we stick on things: a shirt is “blue”, a price is “19.99 €”, a size is “medium.” Seems pretty straightforward, doesn’t it? But even the simplest attributes can be more complicated than they appear.

The problem boils down to this: the meaning of an attribute isn’t fixed. It changes depending on who’s asking and why they’re asking. Remember how we talked about defining a single “thing”? Well, attributes are just as slippery. It’s all about perspective and context.

Surreal picture of a person standing in front of a mirror surrounded by water and clouds.

Let’s dive into the world of fashion and look at the attribute “color.” Imagine you’re scrolling through an online clothing store and spot an appealing “red” sweater. What information does that “red” label really give you?

flowchart LR
    subgraph Garment
        A["Color: Different Aspects"]
    end
    A -->|"Perceived Color"| B["Subjective Description"]
    A -->|"Color Code"| C["Pantone/Hex"]
    A -->|"Color Name (Marketing)"| D["Descriptive Name"]
    A -->|"Dye"| E["Chemical Composition"]
    A -->|"Main/Accent Colors"| F["Percentage Breakdown"]

This graph visualizes different aspects of “Color” as applied to “Garments”. It illustrates that color can be interpreted and represented in various ways, from subjective descriptions to technical specifications.

So, that simple word “red” is actually packed with potential meanings. The level of detail we need, and the specific situation, totally transform how we should define, understand, and use that attribute.

And this isn’t some abstract, theoretical problem. It has real consequences! Imagine ordering that “red” sweater, expecting a bright, cheerful cherry red, only to receive something closer to a dark burgundy. Disappointment, returns, bad reviews… it all starts with that ambiguous attribute.

Relationships: A Coordinated Team Effort

We usually think of data relationships as simple connections: a customer buys a product, a website has pages. Seems straightforward, like a one-on-one conversation. But in many real-world situations, especially in something as complex as the German energy market, it’s more like a group chat with multiple participants! It’s not just “A links to B”. These complex relationships are everywhere, and we need to understand them to build effective information systems.

Surreal picture of a city in the cloud.

Let’s explore how this plays out in the German energy market from an energy trading company’s perspective.

A Real-World Example

The Players on the Team

An energy trading company is supplying electricity to a factory. Here’s a breakdown of the key players and how they work together:

The Playbook and the Balancing Group Manager’s Role

  1. The Schedule: The energy trader creates a detailed schedule. This is like a game plan, outlining the planned moves: buying 10 MWh from the solar farm and selling 10 MWh to the factory. The trader sends this schedule to the Balancing Group Manager before the electricity actually starts flowing (it’s due the day before, by noon, Day-Ahead Market (D-1) before 12pm).
  2. The Balancing Group Manager’s Balancing Act: The Balancing Group Manager uses the schedules from everyone in their balancing group to keep the overall energy flow in check.
  3. Imbalances and Balancing Energy: If the factory ends up using more electricity than planned (let’s say 12 MWh instead of 10 MWh), that creates an imbalance. It’s the Balancing Group Manager’s job to fix this imbalance using something called balancing energy.

Surreal picture of computer screens floating in the clouds.

Breaking Down a Typical Deal

To illustrate how a typical energy trading deal works, let’s outline the initial steps in our energy trading example. Imagine an energy trading company is facilitating the flow of electricity from a solar farm to a factory. The process begins with these key agreements and actions:

After these initial agreements and the schedule submission, the physical delivery of electricity occurs on the Day of Delivery (D). The Balancing Group Manager then monitors the actual energy flows and manages any imbalances that may arise.

sequenceDiagram
    participant EnergyConsumer
    participant EnergyTrader
    participant EnergyProducer
    participant GridOperator
    participant BalancingGroupManager
    participant DeliveryPoint
    participant MeteredData
    participant Schedule
    participant Transmission System Operator

    Note over EnergyTrader,BalancingGroupManager: Day-Ahead (D-1)
    EnergyTrader ->> EnergyProducer: Purchase Agreement (10 MWh @ 40 EUR/MWh)
    EnergyTrader ->> EnergyConsumer: Sales Agreement (10 MWh @ 50 EUR/MWh)
    EnergyTrader ->> BalancingGroupManager: Submit Schedule - 10 MWh in, 10 MWh out
    Note right of BalancingGroupManager: Schedule Deadline (12:00 PM D-1)

    Note over EnergyTrader,BalancingGroupManager: Day of Delivery (D)
    EnergyProducer ->> GridOperator: Inject 10 MWh
    GridOperator ->> DeliveryPoint: Transport Energy
    DeliveryPoint ->> EnergyConsumer: 10MWh Delivery
    EnergyConsumer ->> EnergyConsumer: Increased Demand! Need 12 MWh
    EnergyConsumer ->> EnergyTrader: Request Additional 2 MWh
    EnergyTrader ->> EnergyTrader: Check short-term market prices
    EnergyProducer ->> GridOperator: Inject Additional 2MWh (If available, and a deal is made.)
    Note over EnergyProducer,GridOperator: OR, source from another supplier on the spot market.
    GridOperator ->> DeliveryPoint: Transport Energy
    DeliveryPoint ->>+ EnergyConsumer: 2MWh Delivery
    EnergyConsumer ->>- MeteredData: Meter Data: 12 MWh consumed

    Note over BalancingGroupManager: Imbalance Calculation
    activate BalancingGroupManager
    BalancingGroupManager ->> MeteredData: Get Metered Data
    BalancingGroupManager ->> Schedule: Compare to Schedule
    BalancingGroupManager ->> BalancingGroupManager: Calculate Imbalance (2 MWh Shortfall)
    BalancingGroupManager ->> Transmission System Operator: Procure Balancing Energy (2 MWh)

    Note over EnergyTrader,BalancingGroupManager: Settlement
    BalancingGroupManager ->>- EnergyTrader: Invoice for Balancing Energy (proportional to 2 MWh deviation)
    activate EnergyTrader
    EnergyTrader ->>- EnergyConsumer: Invoice including:<br/>- 10 MWh @ 50 EUR/MWh<br/>- 2 MWh @ Market Price<br/>- Share of Balancing Energy Costs

This sequence diagram illustrates a typical energy trading and balancing process. It shows the flow of information and energy between key participants like Energy Consumers, Traders, Producers, Grid Operators, and Balancing Group Managers, covering day-ahead scheduling, real-time delivery, imbalance handling, and settlement.

Managing Risk: The Financial Playbook

Besides just buying and selling electricity, energy traders also use financial tools to protect themselves from price swings and unexpected events. These tools don’t involve moving actual electricity, but they’re linked to energy price indexes. Think of them as “insurance policies”:

Traders usually trade these financial instruments on places like the European Energy Exchange (EEX): think of it as a stock market for energy.

Visualizing the data model

To bring these “things” to life, let’s visualize the data model after considering all relevant factors discussed so far. What you see below is a simplified sketch, a kind of starter model for our energy trading example. Think of it as a learning tool, not a blueprint for a real-world system. Don’t expect to use this directly for a live, complex energy trading platform! Instead, this example is designed to shine a spotlight on the essential things you must think about when you’re diving into data modeling, especially in intricate areas like energy trading.

erDiagram
    EnergyTrader {
        string TraderID PK
        string TraderName
    }
    EnergyProducer {
        string ProducerID PK
        string ProducerName
    }
    EnergyConsumer {
        string ConsumerID PK
        string ConsumerName
    }
    GridOperator {
        string OperatorID PK
        string OperatorName
    }
    DeliveryPoint {
        string DeliveryPointID PK
        string Location
    }
    BalancingGroupManager {
        string BalancingGroupManagerID PK
        string BalancingGroupManagerName
    }
    SwapContract {
        string ContractID PK
        string TraderID FK
        string CounterpartyID FK
        decimal FixedPriceEurMWh
        string FloatingPriceIndex
        decimal VolumeMWh
        date StartDate
        date EndDate
    }
    OptionsContract {
        string ContractID PK
        string TraderID FK
        string CounterpartyID FK
        string OptionType "Call or Put"
        decimal StrikePriceEurMWh
        decimal VolumeMWh
        date ExpiryDate
    }
    FuturesContract {
        string ContractID PK
        string TraderID FK
        string CounterpartyID FK
        decimal PriceEurMWh
        date DeliveryDate
    }
    FuturesTransactionLink {
        string LinkID PK
        string TransactionID FK
        string ContractID FK
    }
    EnergyTransaction {
        string TransactionID PK
        string TraderID FK
        string ProducerID FK
        string ConsumerID FK
        string DeliveryPointID FK
        datetime TransactionDateTime
        decimal ActualVolumeMWh
        decimal PriceEurMWh
        decimal GridFeeEurMWh
    }
    Schedule {
        string ScheduleID PK
        string TraderID FK
        string ProducerID FK
        string ConsumerID FK
        string DeliveryPointID FK
        string BalancingGroupManagerID FK
        decimal PlannedVolumeMWh
        datetime DeliveryDateTime
    }
    MeteredData {
        string MeteredDataID PK
        string DeliveryPointID FK
        datetime Timestamp
        decimal ActualVolumeMWh
    }
    Imbalance {
        string ImbalanceID PK
        string ScheduleID FK
        string BalancingGroupManagerID FK
        decimal ImbalanceVolumeMWh
        decimal AusgleichsenergiePriceEurMWh
    }
    %% Relationships
    EnergyTrader ||--o{ SwapContract : "trades"
    EnergyTrader ||--o{ OptionsContract : "trades"
    EnergyTrader ||--o{ FuturesContract : "trades"
    EnergyTrader ||--o{ EnergyTransaction : "conducts"
    EnergyProducer ||--o{ EnergyTransaction : "involved_in"
    EnergyConsumer ||--o{ EnergyTransaction : "involved_in"
    EnergyTrader ||--o{ Schedule : "submits"
    EnergyProducer ||--o{ Schedule : "involved_in"
    EnergyConsumer ||--o{ Schedule : "involved_in"
    GridOperator ||--o{ DeliveryPoint : "operates"
    DeliveryPoint ||--o{ Schedule : "occurs_at"
    DeliveryPoint ||--o{ MeteredData : "has"
    BalancingGroupManager ||--o{ Schedule : "receives"
    BalancingGroupManager ||--o{ Imbalance : "manages"
    FuturesContract ||--o{ FuturesTransactionLink : "linked_by"
    EnergyTransaction ||--o{ FuturesTransactionLink : "linked_by"
    Schedule ||--o{ Imbalance : "results_in"
    MeteredData ||--o{ Imbalance : "compares_with"
    Schedule }|--|| MeteredData : "links_to"

This ER diagram models the data structure for an energy trading system. It outlines key entities like Energy Transactions, Futures Contracts, Schedules, and Imbalances, along with related entities like Energy Traders, Producers, Consumers, and Grid Operators. The diagram illustrates the relationships between these entities within the energy market context.

In reality, building data models for these kinds of complex businesses means carefully considering these key aspects, which we’ll explore next:

Understanding Types and Instances

Now, let’s tackle a fundamental idea that, if misunderstood, can lead to some serious problems in your data: the difference between types and instances. It might seem obvious, but many data disasters stem from mixing these two up.

A Library of Confusion

Imagine a library. You have many different kinds of books (novels, biographies, textbooks) and you have many individual copies of each book. Confusing the kind of book with a specific copy would lead to chaos! You wouldn’t know how many copies of “The Three-Body Problem” you have, or whether a particular copy is checked out or on the shelf.

In data modeling, mixing up types and instances is like that disorganized library.

It leads to:

A library of confusion.

Book Titles and Physical Books

So, what’s the crucial difference?

A single real-world thing can also be an instance of multiple types. A copy of “The Three-Body Problem” could be an instance of “Book”, “Science Fiction Novel”, “Hardcover Book”, and “Loanable Item.”

The book cover of "The Three-Body Problem" by Liu Cixin
The book cover of "The Three-Body Problem" by Liu Cixin

Example: “The Three-Body Problem”

“The Three-Body Problem” by Liu Cixin is a type (the book title). But we also have different editions, each with its own ISBN:

Now, instances (specific copies):

The CopyID is unique to each physical copy. The ISBN is unique to each edition. We now have three “Edition” subtypes: First Edition Paperback, First Edition Hardcover, and a hypothetical Second Edition Paperback (with a made-up ISBN ending in “X” to illustrate the point).

erDiagram
    Book {
        string Title
        string Author
        string Genre
    }
    Edition {
        string ISBN
        string Cover
        date PublicationDate
    }
    BookCopy {
        string CopyID
        string Condition
        string Status
    }
    Book ||--|{ Edition : "is a"
    Edition ||--|{ BookCopy : "is a"

This ER diagram models a book hierarchy, showing the relationship between Books, Editions and Book Copies. It illustrates how a Book can have multiple Editions and each Edition can have multiple Book Copies.

In the world of data, always ask: “Am I talking about the general concept of the book, a specific edition (identified by its ISBN), or a particular physical copy?” Mastering this distinction is fundamental to building effective and reliable information systems.

Summary

We’ve journeyed through the sometimes murky waters of data modeling, and hopefully, one key idea has become crystal clear: data is never a perfect reflection of reality. It’s always a map, a simplified representation, shaped by our choices, our perspectives, and the limitations of our tools.

This isn’t a cause for despair. It’s a call for mindful design. We’ve seen how even seemingly simple concepts like “customer” or “color” can explode into a multitude of meanings depending on who’s asking and why.

We’ve explored the complex inter-dependencies and relationships within systems, like the energy market, where a single transaction involves a whole cast of characters, each with their own role and perspective.

And we’ve dived into types and instances, and why mixing them can cause damage.

The path to effective data modeling, then, isn’t about striving for an impossible, mirror-like perfection. It’s about embracing these principles:

By understanding the inherent ambiguity of information, acknowledging the limits of our models, and embracing a collaborative, iterative approach, we can build information systems that are not just accurate, but truly useful.

We can create a valuable map.