Logical Versus Physical Database Modeling

After all business requirements have been gathered for a proposed database, they must be modelled. Models are created to visually represent the proposed database so that business requirements can easily be associated with database objects to ensure that all requirements have been completely and accurately gathered. Different types of diagrams are typically produced to illustrate the business processes, rules, entities, and organizational units that have been identified. These diagrams often include entity relationship diagrams, process flow diagrams, and server model diagrams. An entity relationship diagram (ERD) represents the entities, or groups of information, and their relationships maintained for a business. Process flow diagrams represent business processes and the flow of data between different processes and entities that have been defined. Server model diagrams represent a detailed picture of the database as being transformed from the business model into a relational database with tables, columns, and constraints. Basically, data modelling serves as a link between business needs and system requirements.

Two types of data modelling are as follows:

• Logical modelling
• Physical modelling

If you are going to be working with databases, then it is important to understand the difference between logical and physical modelling, and how they relate to one another. Logical and physical modeling are described in more detail in the following subsections.

Logical Modelling

Logical modelling deals with gathering business requirements and converting those requirements into a model. The logical model revolves around the needs of the business, not the database, although the needs of the business are used to establish the needs of the database. Logical modelling involves gathering information about business processes, business entities (categories of data), and organizational units. After this information is gathered, diagrams and reports are produced including entity relationship diagrams, business process diagrams, and eventually process flow diagrams. The diagrams produced should show the processes and data that exists, as well as the relationships between business processes and data. Logical modelling should accurately render a visual representation of the activities and data relevant to a particular business.

Note

Logical modelling affects not only the direction of database design, but also indirectly affects the performance and administration of an implemented database. When time is invested performing logical modelling, more options become available for planning the design of the physical database

The diagrams and documentation generated during logical modeling is used to determine whether the requirements of the business have been completely gathered. Management, developers, and end users alike review these diagrams and documentation to determine if more work is required before physical modeling commences.

Typical deliverables of logical modeling include

• Entity relationship diagrams
  An Entity Relationship Diagram is also referred to as an analysis ERD. The point of the initial ERD is to provide the development team with a picture of the different categories of data for the business, as well as how these categories of data are related to one another.
• Business process diagrams
  The process model illustrates all the parent and child processes that are performed by individuals within a company. The process model gives the development team an idea of how data moves within the organization. Because process models illustrate the activities of individuals in the company, the process model can be used to determine how a database application interface is design.
• User feedback documentation

Physical Modelling

Physical modelling involves the actual design of a database according to the requirements that were established during logical modelling. Logical modelling mainly involves gathering the requirements of the business, with the latter part of logical modelling directed toward the goals and requirements of the database. Physical modelling deals with the conversion of the logical, or business model, into a relational database model. When physical modelling occurs, objects are being defined at the schema level. A schema is a group of related objects in a database. A database design effort is normally associated with one schema.

During physical modelling, objects such as tables and columns are created based on entities and attributes that were defined during logical modelling. Constraints are also defined, including primary keys, foreign keys, other unique keys, and check constraints. Views can be created from database tables to summarize data or to simply provide the user with another perspective of certain data. Other objects such as indexes and snapshots can also be defined during physical modelling. Physical modelling is when all the pieces come together to complete the process of defining a database for a business.

Physical modelling is database software specific, meaning that the objects defined during physical modeling can vary depending on the relational database software being used. For example, most relational database systems have variations with the way data types are represented and the way data is stored, although basic data types are conceptually the same among different implementations. Additionally, some database systems have objects that are not available in other database systems.

Implementation of the Physical Model

The implementation of the physical model is dependent on the hardware and software being used by the company. The hardware can determine what type of software can be used because software is normally developed according to common hardware and operating system platforms. Some database software might only be available for Windows NT systems, whereas other software products such as Oracle are available on a wider range of operating system platforms, such as UNIX. The available hardware is also important during the implementation of the physical model because data is physically distributed onto one or more physical disk drives. Normally, the more physical drives available, the better the performance of the database after the implementation. Some software products now are Java-based and can run on virtually any platform. Typically, the decisions to use particular hardware, operating system platforms, and database software are made in conjunction with one another.

Typical deliverables of physical modeling include the following:

• Server model diagrams
  The server model diagram shows tables, columns, and relationships within a database.
• User feedback documentation
Database design documentation

Conclusion

Understanding the difference between logical and physical modelling will help you build better organized and more effective database systems. This article described both of these models.

Source: http://www.developer.com/tech/article.php/641521/Logical-Versus-Physical-Database-Modeling.htm