Database Design

Database Design

Database design is the process of creating a structured and efficient database schema that meets the requirements. It involves identifying entities, relationships, and attributes, and defining the organization of data within tables.

There are two main approaches to database design. One is a Top-Down design and the other is a Bottom-Up design.

Top-Down Database Design

When we going to work with a new database, we need to follow the Top-Down database design (we are going to define the entities from scratch). It has a few steps to follow.

1. Identifying Entities: This step involves determining the entities based on the requirements. (Example: book, and author)
2. Defining Attributes: Once the entities are identified, the next step is to determine the attributes associated with each entity.
3. Relationships: In this step, the relationships between the entities are determined, defining how they are related to each other.
4. Many-to-Many Relationships: If there are many-to-many relationships between entities, a solution is designed to handle and resolve such relationships effectively.
5. Defining Subject Areas: Subject areas are utilized to organize entities logically, providing a structured approach to database organization.

Botton-Up Database Desing

• When we look at the Bottom-Up database design, it is opposite to the Top-Down database design.
• When we look at the bottom-up design, to create a data model from specific detail of the existing system, legacy detail, and there is some type of database that is already in use or some kind of process that is already being followed.
• When you go up with Bottom-Up, something that exists, we are going to migrate it to newer. that’s why we are going with a bottom-up database design.
• In bottom-up design first, we have to identify the data (Attributes) and group them as entities. In bottom-up design, we are not going to define the entities from scratch.
• In the bottom-up design, our goal is, to create the perfect data model without redundancy or duplicates and anomalies (Get the existing specific data and create the data model for that).
• In the bottom-up design, we have to follow normalization.

What are database anomalies?

• In Database Management System (DBMS), anomaly means the inconsistency occurred in the relational table during the operations performed on the relational table.
• There can be various reasons for anomalies to occur in the database. For example, if there is a lot of redundant data (Unnecessary data) present in our database then DBMS anomalies can occur. If a table is constructed in a very poor manner then there is a chance of database anomaly.
• We can categorize anomalies into three types.

Types of anomalies

To learn about anomalies follow the below document

Anomalies in DBMS - javatpoint

Note: To do normalization we must understand 2 things: Function dependencies, and Normal Forms.

What is Functional Dependency?

• A functional dependency shows a relationship between attributes. Here the relationship is not between tables, here the relationship is between the data stored in the table.
• A functional dependency occurs when the value of one attribute is determined by the value of another attribute.
• Functional dependencies play a crucial role in the normalization process, helping to eliminate data redundancy and improve data integrity.
• let’s consider a “Customer” table with attributes such as “customer_id” “customer_name,” and “customer_address.” If we observe that each “customer_id” uniquely determines both the “customer_name” and “customer_address,” we can say that there is a functional dependency between “customer_id” and the other two attributes. Here if the two customers have the same name that is not a problem because each customer's name is dependent on “customer_id” which is unique because each “customer_id” uniquely identifies each unique customer.
• Basically, functional dependency creates a unique relationship between the data.
• Functional dependencies are used to define the keys, primary keys, and foreign keys in a relational database. They are utilized in the process of normalizing a database schema to ensure efficient storage and retrieval of data.

What is Normalization?

• Normalization is the key way to avoid database anomalies and redundancy. It is just a design technique.

To learn about Normalization follow the below documentation.

Database Normalization

• It is a structured way to verify the attributes. To reduce the anomalies and redundancy we have to follow the normalization process. It has the step normal forms that we have to follow. And here the end state will be minimal anomalies and minimal redundancy.

When to use which database design

Even though the ERD is for a Top-Down design pattern and Normalization is for a Bottom-Up design pattern, we can use the ERD in the Bottom-up design, and we can use Normalization in the top-down design pattern.

Thanks for reading this Article!