SQL Database Design » 3. Modeling Levels

3. Modeling Levels

There are three distinct modeling levels on which models can be created. They all have specific characteristics. I took the following definition from here:

The terms "conceptual", "logical", and "physical" are frequently used in data modeling to differentiate levels of abstraction versus detail in the model. Although there is no general agreement, let alone accepted authority, which defines these terms, nevertheless data modelers generally understand the approximate scope of each.

The conceptual, logical, and physical levels expose the following properties:

  • Conceptual level:

    Conceptual models use a comparably high level of abstraction. Their purpose is to provide an overview of the whole or a part of a subject. Conceptual models may be incomplete. They rarely include all critical details and usually describe only the necessary objects. In a way, you could simply take a logical model and strip some objects and attributes. Conceptual models can be distinguished from logical models by the existence of many-to-many relationships and paradigm independence. However, that doesn't mean you can't use a specific paradigm with conceptual models from the beginning or change it in the middle of development. It's just, that you omit paradigm-dependent notations/features from the model.

    Note, that the conceptual level is often called the "business level". I have never been a friend of "business" terms and examples, because modeling does not automatically involve business topics, e.g. in student projects or projects in an otherwise non-commercial environment. Business terms are just too American. ;-)

  • Logical level:

    The following definition was taken from this source:

    A logical entity-relationship model is provable in the mathematics of data science. Given the current predominance of relational databases, logical models generally conform to relational theory. Thus a logical model contains only fully normalized entities. Some of these may represent logical domains rather than potential physical tables.

    For a logical data model to be normalized, it must include the full population of attributes to be implemented and those attributes must be defined in terms of their domains or logical data types (e.g., character, number, date, picture, etc.).

    A logical data model requires a complete scheme of identifiers or candidate keys for unique identification of each occurrence in every entity. Since there are choices of identifiers for many entities, the logical model indicates the current selection of identity. Propagation of identifiers as foreign keys may be explicit or implied.

    Since relational storage cannot support many-to-many concepts, a logical data model resolves all many-to-many relationships into associative entities which may acquire independent identifiers and possibly other attributes as well.

  • Physical level:

    A physical model is a logical model instantiated in a specific implementation, here DBMS (e.g. Oracle, PostgreSQL, MySQL). This is analogous to implementing object-oriented models in a programming language (Java, C++, PHP, ...):

    ... The physical data model specifies implementation details which may be features of a particular product or version, as well as configuration choices for that database instance. These include index construction, alternate key declarations, modes of referential integrity (declarative or procedural), constraints, views, and physical storage objects such as tablespaces.

To sum up the three modeling levels:

The conceptual model is concerned with the real world view and understanding of data; the logical model is a generalized formal structure in the rules of information science; the physical model specifies how this will be executed in a particular DBMS instance.

The following table summarizes the characteristics of each modeling level:

Characteristics of Modeling Levels
TypeParadigmRelationship TypesData TypesCompleteness
Conceptualpure modeling layerbasically paradigm-independentmany-to-many relationshipsabstract data typesmay be incomplete
Logicalpure modeling layerparadigm-dependentno many-to-many relationshipsabstract data typesmust be complete
Physicalmodeling and implementation layerparadigm-dependentno many-to-many relationshipsimplementation-specific data typesmust be complete

The "nitty-gritty" of all (database) modeling is the logical model and also the focus of this work. Conceptual models are basically just a less detailed view onto a subject, whereas physical models are the result of applying a logical model to a specific implementation (DBMS). I strive to create models which are independent of the platform as much as possible, so that the difference between logical and physical models are minimal.

References for Modeling Levels:

Last updated: 2010-10-13