ER Diagram – Conceptual, Logical, or Physical Schema?

database-designerdschema

I have been reading databases, and I clearly understand the difference between a conceptual, logical and physical schema. An ER diagram is an example of which of these schema's?

Best Answer

When ER Modeling was introduced in the 1970s, the intent was to provide a model that was not biased towards one particular implementation. People were constructing relational models of a given project, even if the intent were to implement on a hierarchical or network database.

This evolved into what I learned as conceptual modeling, and that's probably what you learned as conceptual modeling as well. The way I learned it, the conceptual model is useful for analysis of the subject matter from a data centric point of view. That model captures what the information requirements on the database are going to be. It does NOT capture features of the proposed design.

ER diagrams initially reflected only ER models, and thus were suitable for depicting conceptual models.

When I learned logical modeling, this reflected the first stage of design, as distinct from analysis. If the final target is a relational database, it makes sense for this model to be relational. When I first learned this stuff, ER diagrams were not used to depict relational models. Instead there was a kind of diagram that I'll call a relational schematic. A relational schematic had somewhat different appearance from an ER diagram, and also somewhat different content. For example, a relational schematic usually used arrowhead notation while an ER diagram usually used crow's foot notation.

Differences in content were mainly these: a relational schematic included foreign keys while an ER diagram did not. And many-to-many relationships were depicted differently. In the relational schematic, a junction table is needed to hold the two (or more) foreign keys, whenever there's a many-to-many relationship. In an ER diagram, a many-to-many relationship was depicted with a line with a crow's foot at both ends.

Physical models added details to the logical model, and generally reflected features available in the specific DBMS intended for implementation, as well as certain other considerations.

Over the last 20 years, there have been two broad trends. The first trend has been to use ER diagrams to depict either relational models or ER models. The second trend has been to skip over analysis completely and proceed directly to design. Thus, a single model depicts both the understanding of the requirements and the features of the proposed solution.

This blending of analysis and design works pretty well on small scale projects, because the analysis is pretty trivial anyway. For large scale projects, it can be a disaster. You end up with the right solution to the wrong problem. By the time the mismatch is discovered, the budget has been spent, and the deadline is looming.

There's more detail in a post-relational world, but I'll stop here.