Database Design Strategy

Question: What type of strategy should a data modeler have when designing a database?
As a data modeler, designing a database requires a well-planned strategy to ensure the effectiveness, efficiency, and accuracy of the resulting database. A database is an organized collection of data that stores information in a structured format, and its design is crucial to its ability to meet the needs of its users.
To start, a data modeler should identify the purpose and requirements of the database. This includes determining what data will be stored, how it will be accessed, and who will be using it. The data modeler should also consider the scale of the database, the potential growth of data, and the expected performance of the system. Next, the data modeler should choose an appropriate data model for the database. A data model is a representation of the data and its relationships, and it serves as the basis for designing the database schema. The data modeler should consider various factors such as the complexity of the data, the level of normalization, and the need for flexibility.
Once the data model is chosen, the data modeler should design the database schema. This involves creating tables, defining columns and relationships, and establishing rules for data integrity and consistency. The data modeler should also consider the appropriate indexing and partitioning strategies to optimize the performance of the database. During the design process, the data modeler should also consider the security of the database. This includes determining access control policies and procedures, data encryption, and data backup and recovery plans.
Finally, the data modeler should thoroughly test the database to ensure that it meets the requirements and is working as expected. This includes testing for data consistency, performance, and security. Designing a database requires a well-planned strategy that involves identifying the purpose and requirements of the database, choosing an appropriate data model, designing the database schema, considering security measures, and testing the database thoroughly. By following these steps, the resulting database will be effective, efficient, and accurate in meeting the needs of its users.

In designing a relational database, there are a number of objectives to keep in mind. Some the database designer controls and some depend, to varying degrees, on other factors in the database environment.

Three important database-design objectives that the designer controls include:

1. Ensuring that information is delivered in a consistent manner
2. Eliminating data redundancy (duplicate data)
3. Preserving existing data when deleting unwanted data

As you move through this course, it will now become clear how good database design can impact overall robustness.

1. speed and
2. efficiency

of a relational database.

Other important design objectives are either influenced or controlled by the RDBMS, the network operating system (NOS) and network configuration, or the computer hardware. These objectives include:

1. Maximizing the speed of database operations
2. Enforcing data integrity^[1]
3. Achieving database security

Problems with data integrity occur when a value in one table that is supposed to relate to a value in another cannot, because the second value either has been deleted or was never entered. Maximizing the speed of database operations, for example, certainly depends on database design. But speed also depends on the RDBMS, the network configuration, and the computer hardware. While data integrity is imposed early in database design, enforcing data integrity relies on the capabilities of the RDBMS. On the whole, database security is controlled by both the RDBMS and network security. From this point on in the course, the term databases refers to relational databases, unless otherwise indicated.

There are two ways to approach database design.

1. One is to focus on the subject matter with which the database is concerned;
2. the other is to focus on the application in which the database will be created and viewed.

The next lesson takes a look at the first of these approaches: the subject approach.

[1]data integrity: A term used to describe the quality (in terms of accuracy, consistency, and validity) of data in a database, in the sense that values required to enforce data relationships actually exist.