Introduction to Database modeling
Why is it essential to know the basics of database modelling?
Most of the applications we use are driven by data. The performance and usefulness of these applications mostly depend on the following factors:
What kind of data does it store?
How does it store the data?
How soon can it retrieve the data on demand?
During my initial days in development, I was ignorant of the importance of data management in an application. I always thought “we store the data in the database and retrieve it when the application requires it”. It took some time for me to realize the facts about data management.
The way we store and process data is crucial and it affects the overall working of our application.
In this week’s post, I’ll give an overview of data management and why database modeling is an important step in the development lifecycle of an application.
What is Database Modelling?
A database model is the logical structure of a database that includes the relationship and constraints determining how we store and access the data. There are many types of database models depending on the type of data and the relationship between them. Here are some of the commonly used models.
Entity-relationship model
Document model
Hierarchical database model
Relational model
Network model
Object-oriented database model
I’ll discuss two of the most commonly used database models.
Entity-relationship model and Document model.
I’ll link some resources at the end of the post that will give more information on the other models.
Entity-relationship model (ER model)
An entity-relationship model is used to design a database conceptually before the actual implementation of the physical infrastructure.
The representation of entities in ER model is similar to a relational model.
The data is sorted into tables which consist of columns and rows.
Each column represents an attribute of an entity. Each row contains information about a particular entity.
Each row of data can be uniquely identified using a primary key, which will be one or a combination of the columns in the table.
The primary key can be used to refer to a particular entity from one table to another which is called a foreign key.
Let me explain each term with an example. Take a look at this simple ER diagram below.
The above ER diagram shows a database that stores information about students, subjects, and the marks scored by each student in those subjects. Let us look at each component of the ER diagram.
The DB model has 3 entities → Student, Subject, and Marks.
The Student entity has student_id, name, and date_of_birth as attributes.
The Subject entity has subject_id, name, and total_marks as attributes.
The Marks entity has student_marks_id, student_id, subject_id, and marks as attributes.
In each of these entities, the primary keys are as follows:
Student → student_id
Subject → subject_id
Marks → student_marks_id
This key will uniquely identify each row in the tables.In the “Marks” table, the student_id and the subject_id attributes are foreign keys. Each value represents the corresponding row of data in the linked table.
The relationship between tables can be one of the following:
One-to-One
One-to-Many
Many-to-Many
The entity-relationship diagram is used to represent relational databases. Relational databases are written using Structured Query Language (SQL).
Document database model
Document DB model stores information in documents.
A document is a record in a document database. A document typically stores information about one object and any of its related metadata.
Documents store data in field-value pairs. The values can be a variety of types and structures, including strings, numbers, dates, arrays, or objects.
Documents can be stored in formats like JSON and XML. Each document has a unique id that can be used as a reference.
Document database models are non-relational DB models or NoSQL DB models.
A sample document is shown below.
Collections
A collection is a group of documents. Collections typically store documents that have similar contents.
Not all documents in a collection are required to have the same fields, because document databases have a flexible schema.
References
Types of database models → https://www.lucidchart.com/pages/database-diagram/database-models
Document database model → https://www.mongodb.com/document-databases
Designing the database models will give clear solutions to the three factors we saw earlier. An optimized database model that avoids redundancy will improve our application’s performance tremendously and helps with a smoother development process.
I’ve just given an overview of the most commonly used database models.
You might wonder when we should use a relational model (SQL) and when we should use a document model (NoSQL).
Next week, I’ll cover the difference between SQL and NoSQL databases, their benefits, and the scenarios where these can be used.
Until next time!