Logical vs. Physical data model

Jaunine Conradie (former student) asks:

. . Following the differences between logical and physical data models (e.g., include many‑to‑many relationships thus excluding association entities except if attributes are associated with the association entity)...

Here's the question: If the task is to draw a relational model, do I draw a logical or a physical data model?

If the answer is a logical data model, then I expect to not see association entities and thus also not the foreign keys that would have been included in the association entity.

If the answer is a physical data model, then I expect to see every attribute, as it would be implemented in a specific RDBMS (thus, including data types), based on a full analysis of all available attributes and their values.

Everest response:

    First, I think we need to drop the term "logical" model.  All data models are logical, and some have a physical representation by being implemented in some DBMS. The distinction between logical and physical models arose several decades ago in an effort to rise above the current data modeling schemes which were just trying to draw pictures of a database as implemented and stored physically.  Today we have all sorts of "logical" data modeling schemes.

. . What is important is that every data model is done with and conforms to some modeling scheme, whether logical or physical.  So we can have logical models which are relational, network, hierarchical, single flat file, extended network (allowing M:N relationships), ER, Extended ER (which included ternary relationships and sub/supertypes), multidimensional,... and Fact Oriented Models like ORM.

. . Terry Halpin refers to Relational models as "logical" and ORM models as "conceptual."  OK, but don't get hung up on the terminology; it is the semantics that are important, not the syntax, i.e., the choice of words to describe semantic concepts.

. . If the answer is a logical model, then it will be drawn according to some modeling scheme, which may be relational, in which case in includes foreign keys and intersection entities for M:N relationships.  This logical model could include attribute data types, although that information is getting closer to the physical.  Even though we call ORM conceptual, we do specify data types for the objects!

. . If the answer is a physical model we will generally include more information relating specifically to some DBMS, for example, conforming to the naming conventions and the available data types in the DBMS.  I also suggest dropping the term "physical" model, preferring instead "implementation" model.

. . The truth of the matter is that any data model can be represented over a range of stages, from conceptual to physical, and at each stage it conforms to a particular modeling scheme.