Database

Home / Topic / Database / Relational Database Design

Relational Database Design

The design of relational databases is a fundamental step in the development of any application that uses an SQL database. A good design not only facilitates efficient data storage but also ensures data integrity and consistency. In this chapter, we will learn how to design relational databases and use concepts such as normalization, relationships, and constraints.

What is a Database Schema?

The schema of a database defines the structure and organization of the data. In relational databases, the data is organized into tables, which are sets of records with a predefined format. Each table has a series of columns, where each column represents a data type (e.g., name, date, number).

Proper schema design is important because it affects both the performance and ease of use of the database. Next, we'll see an example of how to create a table in MySQL.

sql

"In this example, we are creating a table called products, with four columns: id, name, price, and created_at. The id field is an integer and is the primary key, name is a string, price is a decimal number, and created_at is a field that stores the creation date and time of each product record."

Normalization

Normalization is the process of structuring the database tables to minimize redundancy and improve data integrity. The idea is to divide the data into multiple tables, ensuring that each one stores a unique entity.

Normalization Example

Let's suppose we have a table that stores employee information, where each record includes the department the employee belongs to. Instead of repeating the department name in each record, we can create a separate table for departments and refer to it.

sql

"In this example, we are normalizing the database by creating two tables, employees and departments. In the employees table, instead of storing the department name directly, we use a foreign key called department_id that references the departments table."

Relationships in Relational Databases

Relational databases are called so because the data stored in different tables can be related to each other. Relationships between tables are established using primary keys and foreign keys.

Types of Relationships

One to One (1:1): A row in table A is related to one and only one row in table B.
One to Many (1:N): A row in table A is related to many rows in table B.
Many to Many (N:M): Many rows in table A are related to many rows in table B.

One to Many Relationship Example

sql

"In this example, we are creating a table called orders, which has a one-to-many relationship with the users table. Each user can have many orders, but each order is associated with a single user through the user_id field."

Constraints and Keys

Constraints are rules applied to table columns to ensure data integrity. Some of the most common constraints are:

PRIMARY KEY: Uniquely identifies each record in a table.
FOREIGN KEY: Establishes a relationship between two tables.
UNIQUE: Ensures all values in a column are unique.
NOT NULL: Ensures a column cannot have null values.

sql

"Here we are creating a table called customers, where the email field has two constraints: it is unique and cannot be null. This ensures that no customer will have the same email and that the email field will always have a value."

Summary

In this chapter, we have learned to design relational databases using tables, relationships, and constraints to ensure data integrity. We also saw how normalization helps eliminate redundancy in our tables. In the next chapter, we will explore how to create tables and define their schemas in SQL databases.

Back to previous chapter

Mark as read and continue

Ask me anything