1

{AutolinkerID – 101}
Database Management System Notes

Thematic Outline: Database Design 101

I. Introduction
• Importance of database design for efficient, accurate, and maintainable data storage.
• Focus on relational database design principles.
II. Understanding Your Data
• Defining the database scope: Identifying the entities to be stored and their relationships.
• Determining hierarchies and dependencies.
• Understanding user needs: Identifying different user types and their data access
requirements.
• Analyzing primary and edge use cases.
• Planning for future growth: Considering potential application evolution and database
adaptability.
III. Designing the Database
• Defining Entities: Identifying main entities based on business requirements (e.g., Users,
Posts, Comments).
• Defining attributes for each entity, including primary keys (e.g., User ID, Post ID).
• Visualizing the Database: Utilizing Entity-Relationship Diagrams (ERDs) to represent the
database structure.
• Using online tools for ERD creation (e.g., [Link]).
IV. Establishing Relationships
• Identifying relationships between entities: Determining related tables and the nature of
their relationships (one-to-one, one-to-many, many-to-many).
• Implementing Relationships: Using primary and foreign keys to establish connections
between entities.
• Creating intermediary tables for many-to-many relationships.
V. Relationship Types
• One-to-One (1:1): Each record in one entity is associated with exactly one record in another
entity.
• One-to-Many (1: N): One record in an entity is associated with multiple records in another
entity.
• Many-to-Many (N: N): Multiple records in one entity can be associated with multiple records
in another entity.
2

VI. Rules and Constraints

• Importance of constraints for data consistency, integrity, and accuracy.
Types of constraints:
• Primary Key constraint: Enforces uniqueness and non-nullability of primary keys.
• Foreign Key constraint: Ensures referential integrity between related tables.
• Unique constraint: Guarantees unique values within a column.
• Enum data type: Defines a specific set of allowed values for a column.
• Default rules: Specify values assigned to columns if no explicit value is provided.
VII. Database Normalization
• Purpose of Normalization: Efficient data separation, redundancy elimination, and
consistency improvement.
• Normal Forms:
• First Normal Form (1NF): Atomic values in each table cell.
• Second Normal Form (2NF): Non-key attributes fully dependent on the primary key.
• Third Normal Form (3NF): Elimination of transitive dependencies.
VIII. Conclusion
• Recap of fundamental database design principles.
• Importance of practice for skill development.
• Encouragement to explore further database design concepts and tools.
• Recommendation for utilizing resources like Hyper skill for continued learning.

Hyper Skill – Database Design 101 Blog

Long before choosing the database type, driver, and where to deploy it, you should decide how
to organize the data to store it in an efficient, accurate, and maintainable way. This involves the
concept of database design; in this article, we will talk about basic things you should know to
correctly model your data organization. The principles described in this article are mainly applicable
to relational database design.

Understanding Your Data

To start designing a database, you should clearly understand your data from the business
requirements side. These are some valuable questions to ask yourself or the business at the start:

• What is the main objective of the application that will use the database? What entities do we
store?

The first thing you need to understand is the database scope. What exactly are you storing? How are
these objects related to each other? Are there any hierarchies or dependencies? It’s imperative to be
as specific as possible here because even a tiny change in the structure may lead to a severe redesign
later.

• Who is going to use the data? What are some use cases?
3

An application might have more than one type of users, and they might need access to different data
or manipulate it differently. Write down some primary use cases and think through possible edge
cases.

• What are future growth plans for the database

Another vital piece you should consider in the beginning is how the application might evolve over
time and ensure that the database can adapt to future needs. Having an idea of what changes may
be required in the future can give you an understanding of the data structure.

When you have answers to those questions, you’re good to go. And it’s time to start designing.

Designing Stage

After you’ve gathered the initial info on your data and decided about the entities you will store, it’s
time to divide information into tables. A table is a database term describing a unit storing a separate
entity.

Each table consists of rows and columns for each data record and each entity property,
respectively.

Let’s go with a popular application example that many beginner courses suggest implementing — a
simple social media platform. This platform will have the functionality to share posts, comment on
those posts, and add users to your network. Let’s design a database for this application.

Defining Entities

When you’ve gone through the questions from the previous paragraph, you might have come up with
the following main entities: Users, Posts, and Comments. If we were to design a real-world
application, we would also think about the possible user roles — like administrators. But to keep the
example simple, we won’t stop here this time and consider our application only to have one type of
regular users.

Once we’ve defined the entities, we should continue with the attribute definition. Another important
database term is the primary key. This is the unique property (or a column) every table must have —
it identifies the data record. So don’t forget to add this attribute to each model. Keeping that in mind,
we might determine those fields:

• For the Users entity: User ID, Username, Password, Email, and Registration Date.

• For the Posts entity: Post ID, Content, Creation Date, Likes Count, and User ID (to associate
the post with its creator).

• For the Comments entity: Comment ID, Content, Creation Date, Post ID (to associate the
comment with the post), and User ID (to identify the comment author).

Note that the primary key must always be present in a model, meaning the value chosen as the
primary key cannot be nullable or empty. This is why we normally create a separate explicit ID field
for this purpose. However, in some cases, for example, for the Users table, a `username` could also
make a good primary key.

It is useful to have a visual representation of your database at this point. And database design has an
approach for it, too.

It’s called an Entity-Relationship Diagram (often referred to as ERD), and it’s widely used to
visualize the database structure. An ERD is not only helpful in the planning and designing stage, but
4

it will also serve as documentation. Especially as your database grows bigger, it will be much more
convenient to have a complete picture right in front of you.

At this point, we have the following layout:

There are multiple online tools for drawing ERDs. I used [Link] for this article. You
can read more about creating ER diagrams here.

Establishing Relationships

A relational database is called relational for a reason. The entities we define are usually related, and
you need to analyze and implement those relationships accurately. Things you would need to
recognize at this stage:

• What tables are related?

• What kind of relationships are these? For example, can users have multiple posts? Can a post
have multiple users?

• What are the keys that will connect the entities?

In our case, we can tell that posts are related to users, and comments are related to both posts and
users. Every one of them has a primary key. And when a primary key appears in another table to
define a connection, it is called a foreign key.

This is how these relationships would appear on our ER diagram:

The relationships between entities are classified into three types:

• One-to-One Relationship (1:1)

In a one-to-one relationship, each record in one entity is associated with exactly one record in
another, and vice versa. For example, if later you would like to store the user’s profile picture as a
separate table entity, the relationship between Users and Profile Pictures would be one-to-one
because a user cannot have more than one profile picture, and the same profile picture (as an
instance) cannot be used by multiple users.

• One-to-Many Relationship (1: N)

In a one-to-many relationship, a record in one entity is associated with multiple records in another
entity. Still, each record in the second entity is associated with only one record in the first entity. In
our project, an example of a one-to-many relationship would be Users and Posts or Posts and
Comments.

• Many-to-Many Relationship (N: N)

In a many-to-many relationship, multiple records in one entity can be associated with multiple
records in another. A good example of this kind of relationship in the social media platform would be
friendships — i.e., Users to Users.

Wait, but how do we connect entities for many-to-many relationships? In the project description, we
mentioned that we would want our users to have relationships — like friending, following or blocking.
But if you look closely, the diagram doesn’t have it defined. Yet.

To implement a many-to-many relationship, you need to create an additional table containing the
primary keys of both entities you want to connect and, optionally, some additional data. To relate the
Users in our example, that’s what this table could look like:
5

The new User Relations table has two one-to-many relationships with the Users table, containing
two Foreign Keys for users we want to establish a connection between.

We also added two fields — created at to keep track of a specific user’s relations and relation to save
the relation type. This will allow us to store friendships, follows, and blocks within the same table
and add more types of user relations later. Note that the relation field type is varchar on the diagram.
However, in some cases, you would probably want to have it as an Enum, limiting the variety of
values that can be put in that column.

Rules and Constraints

And that leads us to defining constraints in designing a database. It’s not enough to just determine
the fields and their types. Establishing guidelines and restrictions on the data stored in the database
is also crucial, as it helps maintain data consistency, integrity, and accuracy.

Constraints are rules defined at the table level to ensure that data entered into the database meets
specific criteria, such as uniqueness or data domain. You’ve already met some of the constraints in
this article — the Foreign/Primary Key constraints. Once you mark a table field as a Primary Key,
the database will control and prevent invalid data from being inserted into this field. Another widely
used constraint is the Unique constraint. You can tell from the name that when it’s added to a field,
it ensures that the values inserted into the property are unique over the table. For example, we could
put a Unique constraint on the `email` field in the Users table to ensure a user can register a specific
email only once.

In the previous paragraph, we discussed the `Enum` data type. Although it’s not a constraint, it’s
commonly used to define a specific set of values that are authorized on the database level. This
makes it an effective tool for maintaining data integrity.

Remember about the Default rules, too, as they are often defined in the designing stage. Defaults are
values assigned to a column or attribute in a database table if no explicit value is provided during
data insertion. For instance, a “created at” column in the Posts or Comments table can have a
default value of the current timestamp, so if no date is provided during data insertion, the current
timestamp will be automatically assigned as the default value.

These rules are not always defined on the database level. Sometimes moving them to the application
code is beneficial for flexibility and customization. For example, the Enum values can be defined on
the application level if you expect a set of values to change in the runtime. Even though defining
constraints is mostly preferable to do in the database, the final decision depends on the specific
requirements, environment, and trade-offs in your particular case.

Database Normalization

Once your initial design is ready, you can apply data normalization rules to your database to ensure
your tables are structured correctly. Applying the rules to your database design is called normalizing
the database or just normalization.

These rules are aimed at efficient data separation, without unnecessary duplication, and to minimize
the risk of data inconsistencies or anomalies. There are six normalization rules or Normal
forms, each building upon the previous one. Describing them all in detail and with examples is going
to be a whole new article, but let’s briefly mention the basics to get the main idea. The most
commonly used normal forms are the first three:

• First Normal Form (1NF): Ensures atomicity by stating that every table field (or a cell) should
contain only one value and never a list of values.
6

• Second Normal Form (2NF): Builds upon 1NF and ensures that each non-key attribute fully
depends on the primary key.

• Third Normal Form (3NF): Extends 2NF by removing transitive dependencies, requiring non-
key attributes to depend only on the primary key, not other non-key attributes.

Data normalization is a valuable tool as it eliminates redundancy, improves data integrity, and makes
it easier to manage data efficiently in relational databases. Let me know in the comment section
below if you want to see an article on data normalization from me, and we will dive into the topic next
time.

Conclusion

In this article, we’ve reviewed the fundamental principles of database design in the very early stages.
Effective database design requires understanding data requirements, analyzing relationships and
dependencies, enforcing data integrity with constraints, and normalizing data structures. Remember,
these skills are only polished with practice, so I highly recommend going through this post’s example
or repeating the actions with some other application idea.

If you want to go further with the social media platform example, consider expanding your database.
At this moment, likes on posts are anonymous. If you decide to have a list of people who liked the
post, you should consider introducing a new “Likes” entity, a table to contain information about a
user and the post they liked. Or will it be a “Reaction”? What would change in the table structure,
then?

If you want to learn more and get some practice with databases, Hyper skill is a great place to
continue your adventure. Here are some topics you might want to check out:

• Relational data model

• Introduction to SQL

• Database management systems

Hyper skill also has various topics on handling database interactions from different programming
languages. If you are learning or want to learn Python, check out Flask track and, specifically
the Movie Database API project that focuses on practicing database design.

Hyper skill is a project-based learning platform that offers a personalized curriculum and a variety of
tracks to help people from different backgrounds gain market-relevant skills through online
education. It’s giving you solid pieces of theory and allows you to practice the skills right away — and
practice makes learning perfect .