Data modeling 101
Last updated
Was this helpful?
Last updated
Was this helpful?
By organizing and categorizing unstructured information, data modeling allows you to make sense of the data, much like building a Lego castle from a giant collection of Lego bricks. The first step is to group the bricks by their features and properties, such as minifigures, building blocks, and decorations. These groups can then be divided into sub-groups based on additional features like color or size.
By categorizing the bricks in this way, we gain a better understanding of what we have to work with and how we can best use them to achieve our goal (building a cool castle 🏰). Additionally, we can identify relationships between different groups of bricks, such as how a minifigure is made up of various smaller parts like a head, torso, arms, and legs (see figure 1).
This process is similar to data modeling, where we identify entities and their attributes, and define the relationships between them. This helps us to understand the data we have and how to use it effectively to achieve our goals.
Data modeling is a critical process that enables you to make sense of large amounts of information. Without a good data model, it can be challenging to interpret what the data means or how to use it effectively.
To illustrate, imagine a complex Lego build with thousands of pieces. Without instructions it would be nearly impossible to assemble it as it was designed. Similarly, a data model serves as a guide for the data, clarifying how different pieces of information fit together and relate to each other.
A robust data model allows us to:
Organize data logically: By grouping similar pieces of information together and labeling them appropriately, we can easily locate and utilize the data we need.
Analyze data effectively: A well-designed data model simplifies the process of querying data to uncover insights and patterns that might be difficult to detect otherwise.
Make informed decisions: With a clear understanding of the data, we can make better decisions based on the insights we gain.
In short, data modeling is crucial because it transforms raw data into actionable and valuable information.
In the first stage of data modeling, the conceptual data model is developed to identify the core processes that the application needs to support. This is often done in collaboration with business experts and stakeholders. The conceptual data model is an abstract representation of the relevant entities and their relationships, and it's used to communicate with people at any technical level.
The logical data model complements the conceptual data model and describes how the data will be structured. Based on the input and feedback from business experts and stakeholders, the data modeler extends the conceptual model with entity attributes and necessary relationships not defined in the conceptual model.
The physical data model represents how the database stores the data model in a database management system. It specifies data types for the attributes as well as primary and foreign keys, constraints, and indexes, and is modeled by a developer.
There are two common approaches to storing data in a database, relational databases and document databases.
A relational database design for a data model for books and their authors could look like the tables below.
Author
1001
J.R.R.
Tolkien
1002
Stephen
King
1003
Mark
Twain
1004
Virginia
Woolf
Book
2001
The Hobbit
Fantasy
1001
2002
The Adventures of Huckleberry Finn
Fiction
1003
2003
To the Lighthouse
Modernism
1004
2004
The Lord of the Rings
Fantasy
1001
In contrast, document databases store data as documents, where each document represents a specific entity and contains all the information related to that entity in a nested data structure. This type of database is best suited for unstructured or semi-structured data, where the data does not have a clear and defined structure.
Relational data modeling is a crucial process that involves a number of essential concepts for creating an efficient database design.
An entity is a “thing” or object that we want to keep track of in our database. It can be a person, a place, a thing, or an event.
An attribute is a characteristic of an entity. It describes some aspect of the entity, such as its name, age, or address.
A data type defines the type of data that can be stored in a property. Common data types include text, number, date, and boolean.
A relationship describes how entities are related to each other. For example, a customer can have many orders, and each order belongs to only one customer. Closely connected to this concept is cardinality, which describes the number of instances of one entity that can be related to another entity. This is usually expressed with the terms one or many.
A one-to-one relationship is probably the least common relationship. It defines that there can only be one record in table A associated with a specific record in table B. This can be interpreted as a bijective function from a mathematics perspective.
A real-world example for this scenario is the connection between a human and its brain. A human can only have one brain and a brain can only be connected to one human, and this relationship is constant. There’s (at least for now) no possibility for someone to have their brain replaced or duplicated.
Another, more relevant, example is the connection between a user and their user settings. One user has their set of settings, and a set of settings belongs to exactly one user, as seen in figure 2.
In data modeling this relationship is not that commonly used, and is often modeled as an ordinary one-to-many relationship instead. This provides greater flexibility should the requirements for either brains or user settings change in the future.
A one-to-many relationship is very common. It defines that a record in table A can only be associated with a specific record in table B, while a record in table B can have multiple records in table A associated with it.
As an example, when a customer places an order in an online store, they might want to purchase different items at the same time. To avoid requiring one order for each item, you can create an order object holding the details of that particular order (including the customer, the state of the order, the order date, etc.), while each item is represented as a single order line with a reference to the order object.
A one-to-many relationship is illustrated in figure 3 with a data model consisting of Order and Order Line. An order line can be connected to only one order through its property Order. An order can have any number of order lines referencing it.
The last type of relationship is the many-to-many-relationship. It defines that multiple records in table A can be connected to multiple records in table B.
To illustrate this visually, imagine drawing lines between books and authors. Each book may have lines connecting it to multiple authors, and each author may have lines connecting them to multiple books.
This is illustrated in figure 4 with a data model consisting of Book, Author and Written By. A Book can be written by multiple authors and an Author can write multiple books. This is modeled as Written By which combines these two object classes and represents the relationship between books and authors. This object class can also be extended to contain more metadata about the relationship (e.g. number of pages that a particular author has written of a particular book).
A primary key is a unique identifier for an entity in a table. It is used to ensure that each record in the table can be uniquely identified.
A foreign key is a field in a table that refers to the primary key of another table. It is used to establish a relationship between the two tables.
Book
6376344c01ed7ce7fb4f1967
Data Modeling 101
1337
Author
6376347f4a2bd917bc5128f2
App
Farmer
Written By
637f9382462b2a1d0e6b823a
6376344c01ed7ce7fb4f1967
6376347f4a2bd917bc5128f2
Normalization is the process of organizing data in a database to minimize redundancy and ensure data integrity. It involves breaking down tables into smaller, more specific tables and establishing relationships between them.
In summary, data modeling is the process of organizing and creating a visual representation of data to help make sense of it. A good data model is essential to the success of a project as it helps understand what the data means and how it can be used effectively.
There are different types of data models, such as conceptual, logical, and physical models, used at different stages of the data modeling process. Relational databases store structured data while document databases store unstructured data. Appfarm Create provides a hybrid of the conceptual and logical data model and adds a layer on top of the non-relational document database, providing it with relational capabilities.
Appfarm Create uses a hybrid of the conceptual and logical data model in the . The physical data model is handled automatically by the platform.
A relational database and a document database differ in their ability to store structured and unstructured data, respectively. In a relational database, data is organized in tables with pre-defined columns and rows that enforce relationships between entities. On the other hand, a document database stores unstructured data as -like documents within collections, making it self-describing and free from a pre-defined structure. Therefore, there is no need for a defined table reference, as the data itself provides context and can vary in structure.
Appfarm Create uses a document database, but adds a layer on top through the to provide relational capabilities (referential integrity, foreign keys), delivering the best of both worlds.
In Appfarm Create, an entity is described with an .
In Appfarm Create, an attribute is an .
In Appfarm Create, the is a part of the definition of an . Simple data types such as boolean, string, integer, and datetime are supported, as well as and references to other object classes.
You may find useful if you want practical examples of how to use many-to-many relationships in Appfarm Create.
In Appfarm Create, the primary key is stored in the ID.
In Appfarm Create, the foreign key is represented as an referencing another object class. In the diagram view of the it is visualized with an arrow pointing from the property to the referenced object class (Book and Author in the Written By object class are foreign keys in figure 4). The object that has the property of a referenced object class will store the ID of the referenced object as seen in the Written By table below, which references both the Book object class and the Author object class.
Please read if you want to learn more about the concepts of database normalization.
