7.2 Database design practices

---

What is a good database design? Some of us may state that if the database follows all the four rules of normalization it is a good design. Others may state that if the database meets the business requirements to store and retrieve data it is a good design. Yet others could also state that the database should be easy to understand, should meet the business rules of the enterprise, and should store and retrieve data efficiently, providing good performance. Meeting the business rules includes scalability and performance.

In fact, all the above capabilities of a database would be desirable and should be the goal of any database design. Yet they are seldom all achieved. Normally, this is due to the fact that most businesses start small and the databases that they designed in the earlier stages do not fit the business model of the enterprise at a later date when the business grows. The database is not able to accommodate the increased growth in users, or the new business rules and new functionality that the application is required to provide while maintaining the current functionality.

7.2.1 Representation of the business requirements

This includes a good logical model that describes the enterprise business in its entirety, providing opportunities for growth with the addition of new features and functions and the implementation of the business rules via relationships and constraints.

Figure 7.2 is an illustration of a logical model. This schema will be used while analyzing and discussing various database features through examples in this chapter.

Figure 7.2: Logical model.

This stage of the database design covers defining the problem or objective, gathering detailed business requirements, creating a functional, logical or entity model, and implementing business rules and constraints.

7.2.2 Representation for the future growth of business

This is the implementation phase of the previous step. That is, once the entity model and the business rules and constraints have been defined, the entity model has to be implemented into a physical form that is usable and which provides throughput, recoverability, securability, maintainability, and manageability of the database system. During this transformation process some design changes to the database model may be implemented to bring efficiency, and may also involve some denormalization. Another method to meet these requirements would be to use some key features of the database product. For example, Oracle provides features such as data partitioning that help maintainability and manageability. Selecting a suitable indexing strategy based on the business model in the previous step would help easy retrieval of data.

Once all the right features of the database have been selected to implement the physical database, another important phase of this step remains, which is the actual implementation itself. During this phase it is important to consider if the database needs to be distributed, if the database needs to be copied over to a different location to meet avail ability, what level of availability is required, if it is immediate or just to support disaster, and how it will meet scalability. Then there are more details such as the required features of the database (e.g., LMT or dictionary managed), and the various indexing and partitioning strategies that should be considered during the design phase.

---