What is Database Design and How to Plan it

Database design is a process whose objective is to define the appropriate structure for our information system. In this article, we will analyze the main phases of the design of a database. We will see the design principles that we must follow to obtain a well-structured and efficient database that meets our project’s objectives.

What is Database Design?

Database design is a fundamental process for modeling our data sets and defining the operations we want to perform on them. Data is the most critical asset of our organization. A well-designed database directly influences the efficiency that we will obtain when storing, retrieving, and analyzing our data.

Advantages of Database Design

A database design carried out correctly provides us with fundamental advantages:

• It allows us to save space through an optimized database design without duplicate data.
• It helps us preserve the data’s accuracy and integrity, and that information is not lost.
• Significantly streamline access and data processing.

Database Design: Main Phases

Like every process, the database design remains made up of different sequential stages.

Requirements Gathering and Analysis

This first phase consists of a mandatory previous step to ensure that our database will meet our objectives. To do this, we must analyze different factors, among which:

• The information that the data describes.
• The users of the database and their needs when accessing the data.

Conceptual Design

In this phase, a high-level description of the database’s content stands render, regardless of the database management system that will remain used next. Entities, their attributes, and the relationships between them are defined in a drawing.

Choosing a Database Management System

It is in this phase where we will choose the specific database management system (DBMS) that best suits our project, such as, for example, Oracle, MySQL, Microsoft SQL Server, and PostgreSQL.

Logical Layout

In this phase, the previously obtained conceptual model stood translated into a logical schema, which describes the database’s structure. It is the phase in which the tables themselves are designed, with their rows, columns, and relationships. The logic model depends on the DBMS to stay used.

Physical Layout

In this phase, the database’s storage structures remain defined physically when the code (for example, SQL) remains written to specify the design in the database engine that we have chosen.

Implementation

Finally, the database schema is created and compiled; the files and applications that implement the transactions remain generated.

Database Design Principles

For our database to be efficient and respond to requirements, it is necessary to follow a series of principles that should guide the database design process. Next, we will describe the main ones:

• Each table stays made up of rows, also called records, and columns, known as fields. Only one type of information (logical part) should remain stored in the areas. Furthermore, data that can be obtained by calculations on other data have not to be stored.
• Design of primary keys and foreign keys. Direct tickets (or PK) are columns that uniquely identify each row and build relationships between tables. Primary keys can never have a null or duplicate value. On the other hand, foreign keys must correspond to the tables’ primary keys to which they relate.
• The design of the relationships between tables can be one to one, one to many, or many to many. Relationships allow information to be organized between different tables, optimizing the available space.
• Standardization of the database, which allows it to comply with industry standards. Normalization is necessary if we are going to work with an OLTP-type database and the most common forms are:
• Second standard form: attributes must depend on the primary key of the table.
• Third standard form: each column that does not contain a key has to be independent of the other columns.

An exciting list of relational database design principles is Tom Grayson’s “Rules of the Thumb.”

This article has seen how to correctly carry out the database design, from the requirements phase to the final implementation. By following the design principles outlined above, we will ensure that our data storage systems meet our organization’s needs and goals.