Three Phases of Traditional Database Designby 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 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.
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.
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 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.