Database Design Phases
- Computer Science Department
Analysis: This is the first step in the database design process. It involves gathering the requirements and understanding what data needs to be stored and how that data will be used. This includes identifying the entities (i.e., the major objects or subjects the data will be about), their attributes (i.e., the data points we need to store about each entity), and the relationships between entities. The result of this process is typically an entity-relationship (ER) model, which provides a high-level, implementation-agnostic view of the data. At this stage, we're not concerned with how the database will actually be implemented; we're only trying to capture what data is required and how different data points relate to each other.
Logical Design: After the analysis stage, we move on to the logical design or data modeling stage. Here, the ER model is converted into a logical model of the database - this involves deciding on the tables, columns, and relationships that will be implemented in the actual database. This is where the concepts of primary keys (unique identifiers for each record in a table) and foreign keys (fields that reference the primary key in another table) come into play. The result is a logical data model that describes the structure of the data, but still ignores the details of how the data will be physically stored in the system. At this stage, the focus is on ensuring that the database structure will be able to hold all necessary data and support the required operations (queries) efficiently.
Physical Design: This is the final step in database design, where we take the logical data model and turn it into a physical database. This involves specifying how the logical structures (tables, columns, keys) will be implemented using the specific features and data types of the target DBMS (Database Management System). This is also where we consider performance optimizations, such as creating indexes to speed up certain queries, partitioning large tables, and deciding how data will be organized on the physical storage media. The goal is to ensure that the database will not only store all required data and support all required operations, but also do so efficiently, even as the size of the data grows.
Each of these steps is crucial for creating a database that meets the application's requirements, performs well, and can be efficiently maintained and evolved over time.
The following are two classical approaches to database design:
Top-down design starts by identifying the data sets and then defines the data elements for each of those sets
Bottom-up design first identifies the data elements (items) and then groups them together in data sets
