Database Design

Introduction

Database design is the process of producing a detailed data model of a database. This data model contains all the needed logical and physical design choices and physical storage parameters needed to generate a design in a data definition language, which can then be used to create a database. A fully attributed data model contains detailed attributes for each entity.

Database Design Process

The process of designing a database involves the following basic steps:

Requirement Analysis: This involves gathering the requirements of the database. The requirements are then analyzed for their validity and the possibility of incorporating the requirements in the system to be development is also studied.
Conceptual Design: In this phase, the design of the database is based on the data requirements of the user and the logical data model. The result of this phase is the conceptual data model, which describes the main data entities, attributes, relationships, and constraints of the data.
Logical Design: This is the phase in which the conceptual design is translated into a logical data model. The logical data model describes the structure of the data elements and set the relationships between them. The result of this phase is a set of relations that are in the third normal form, a form of database normalization.
Physical Design: This is the phase in which the conceptual data model is translated into a physical database. A key goal during this stage is data independence, meaning that the physical storage of data should not affect the ability to query or manipulate the data.

Database Models

There are several types of database models that can be used to design a database. Some of the most common include:

Relational model: This is the most common model, where data is structured into tables (also known as relations) consisting of rows and columns. Each row represents a record (or tuple) and each column represents a field (or attribute). A unique key identifies each record.
Hierarchical model: In this model, data is organized into a tree-like structure, with each record having one parent record and many children. This model efficiently describes many real-world relationships like index of a book, recipes etc.
Network model: This is an extension of the hierarchical model. In this model, each child record can have multiple parent records. It was the first system to implement a system defined relationship between records.
Object-oriented model: This model is based on collection of objects. An object contains values stored in instance variables within the object. An object also contains bodies of code that operate on the object. These bodies of code are called methods. Objects that contain same types of values and the same methods are grouped together into classes.

Normalization

Normalization is a method to eliminate the data redundancy and avoid data anomalies in the relational tables. It divides larger tables to smaller tables and links them using relationships. The purpose of Normalization is to eliminate redundant (useless) data and ensure data is stored logically.

Denormalization

Denormalization is a strategy used on a previously-normalized database to increase performance. In computing, denormalization is the process of trying to improve the read performance of a database, at the expense of losing some write performance, by adding redundant copies of data or by grouping data.

Database Design Approaches

There are two approaches to database design: top-down and bottom-up.

Top-Down: In the top-down approach, the higher level view of the system is considered and the system is conceptualized as a whole before considering the individual components. The top-down approach is used when a new database is being designed.
Bottom-Up: In the bottom-up approach, the individual components are considered first and then they are integrated to form the whole system. The bottom-up approach is used when a new system has to be integrated with an existing system.

Conclusion

Database design is a complex, but necessary process. By carefully considering the data requirements of the system, and using a methodical design process, it is possible to create a database that meets these requirements and provides a solid foundation for the system.