Three Phases of Traditional Database Design

by David Dunning

Traditionally, database designers engage in the creation of a database in three design phases: conceptual, logical and physical. These phases may not follow each other sequentially -- designers may need to revisit earlier phases during later phases, for example. Designers may also omit one or more stages for simple databases. When properly done, database design promotes efficient data storage and retrieval.

Conceptual Design

Conceptual design involves the creation of a conceptual schema, or model, of the database. This model is independent of any physical considerations, including database management systems, programming languages and hardware platforms. Non-technical users must understand the schema, so it should not contain details of how the database should be implemented. It is, however, detailed in terms of the nature, structure and meaning of the data.

Entity-Relationship Model

During the conceptual phase, database designers typically create what is known as an entity-relationship model, or diagram, to help visualize the database. The entity-relationship diagram identifies each entity -- otherwise known as a relation, or table -- in the database -- as well as the relationships between the entities. Essentially, the entity-relationship diagram is simple enough to allow database designers to learn and understand the basic concepts, yet detailed enough to assist in the development of complex applications.

Logical Design

The purpose of logical design is to transform the generic, conceptual schema into a data model specific to a particular database management system. Logical design can be done manually or -- in some cases -- automatically, through the use of computer-aided software engineering (CASE) tools from a conceptual design. In either case, the end result is a set of data definition language commands, which can be used interactively, or as part of a computer program to create the database.

Physical Design

Physical design is the process of physically implementing the logical data model in a database management system. It involves choosing specific file structures in which to store database tables, or relations, and ensuring that the relations can be accessed quickly, efficiently and securely. Database designers may need to think about database access times for frequently used transactions, as well as the average number of transactions processed per minute and the amount of space the database occupies. An index on certain fields, or columns, in a database can improve access times, but it is up to the database designer to decide which fields to index.

About the Author

A full-time writer since 2006, David Dunning is a professional freelancer specializing in creative non-fiction. His work has appeared in "Golf Monthly," "Celtic Heritage," "Best of British" and numerous other magazines, as well as in the book "Defining Moments in History." Dunning has a Master of Science in computer science from the University of Kent.