Conceptual, Logical, and Physical views of data

From: David Cressey <david.cressey_at_earthlink.net>
Date: Tue, 30 Aug 2005 10:07:32 GMT
Message-ID: <EdWQe.3994$_84.75_at_newsread1.news.atl.earthlink.net>



My introduction to databases, back in the 1980s, was, in part, by way of Martin. I got this description of multiple views of data: the conceptual, logical, and physical views.

The conceptual view of data: the data as seen by the subject matter expert. The meaning of each of the values, in context, is part of the subject matter expert's domain. Database structure is not the domain of the SME, in that role.

The logical view of data: the data as seen by the programmers. The people who store and retrieve data from a database (or who write software that does the same thing) view the data in terms of its appearance at the interface. Structural aspects of the data that are visible at the programming interface are part of the logical view. Structural aspects that are transparent at the interface are not part of the logical view. Structural alterations at the logical level may require program maintenance, but structural changes at the physical level will not.

The physical view of data: the data as seen internally in the DBMS. This view of the data might be of concern to the DBA, the Data Base Designer, or to the DBMS engineer.

Missing from the above is the data as seen by the user. Users are generally represented by the SME, though not always.

My understanding of the conceptual data model, the logical data model, and the physical data model are influenced by the above.

In particular, I don't buy the concept that the logical model is unrelated to inplementation. In the design process, the logical model is going to, for all practical purposes, determine the portion of the physical model that will be visible at the programming interface once construction is complete.

Once construction is complete, the logical model is just as "real" as the physical model. The terminology changes with the context, but the design carries over. Thus "relations" will be implemented as "tables", but they are equally "logical".

If you use SQL to construct the database, the relations in the logical model will appear as tables and indexes. Tables and indexes have logical features and physical features as well. In general, the logical features of tables and indexes appear inside the parentheses in the CREATE TABLE or CREATE INDEX statement, while the physical features appear somewhere else.

The fact that indexes are visible at all in the logical view is simply a reflection of the fact that, in the SQL world, the programmer needs to know something about indexes for performance reasons.

Having said that, one goal of logical data modelling is that the model be protable between two different DBMS products without changes to the model. Thus a good logical model for a relational database should be invariant on whether the implementation will be in DB2 or Oracle.In practice, this is not always possible, because choices of datatypes will be influenced by the choice of a target DBMS, and those choices will become part of the logical model. Received on Tue Aug 30 2005 - 12:07:32 CEST

Original text of this message