I find that physical models have some major drawbacks. Often the table names aren't truly indicative of what the business entity is, and I never need ALL the attributes.
I really need the business entities, their relationships, business keys, and a few key attributes for context. I guess you would call that a logical model?
If you are going to fully model the DB before building it, a physical model is useful. But if you are documenting something that is already built, I'd rather have the business orientation than the gory details.
Vladimir Sosiurko, CDMP
We have 1 logical layer for the enterprise central data model (actualy it is the model of the "real world" as we see it) and 2 layers for IT applications: logical and physical data models. Logical IT-application model could be different from logical enterprise model. Conceptual model is used sometimes just as a "helicopter view" or a list of headings of the enterprise model. We use it when we can't show the logical data model at one printed page.
Conceptual models show business concepts (insurance company
deals with customers, sales, insurance policies, etc.)
Logical models show relationships between entities derived from the business concepts in the conceptual model. (i.e. insurance policy has specific attributes - name, term, etc., and logically relates to customers in a given way, regardless of physical implementation)
Physical models are derived from logical models and are specific to a given way of physical storage (i.e. for a specific physical implementation, policy name will be stored in table or file A, policy term will be in table B, etc.)
If you skip the logical model, you will have to clone and modify physical model for each new physical implementation. If you have a logical model, you can derive new physical models from it.
I like Yakov's response, which echoes how I would describe them:
(1) Physical data model is what is actually implemented - the direct database specification, cryptic table names and all.
(2) The logical data model adheres to all the data modelling best practices of a particular modelling paradigm. For example, a fully normalized ER model with human-readable names. As Yakov points would, multiple physical data models can be derived from a single logical data model, and the physical models would/could take into account denormalization or other shortcuts for implementation/performance reasons. Note that you can different logical models of the same information-space using different data modelling paradigms: ER, XML, JSON, RDF.
(3) A concept model (I don't like conceptual data model because at this level we're not talking about data, per se) is all about semantics. The goal is to identify and define significant concepts in your information-space, their attributes, and interrelationships. At this level, you don't need to (and should not) worry about keys, identifiers, key migration, data types or any "data-y" things because you're not modelling data: you're trying to model information. You can then derive multiple logical data models (ER, XML, JSON, RDF) from a single semantic concept model.
william burkett[All DATAVERSITY Members] @ Apr 11, 2019 - 01:20 PM (America/Mountain)
william burkett[All DATAVERSITY Members] @ Apr 11, 2019 - 01:21 PM (America/Mountain)
Here's a simple example to consider. Say I have a Party table. Logically, the Party table has two subtypes, Person and Organization. In my physical model, I rolled up these subtypes into one table. In my logical model for Person, I have attributes, First Name, Middle Name, Last Name. In my Organization subtype, I have Company Name. My business rules are as follows:
- A Person must have a first and last name
- An Organization must have a Company Name
This is easy to model and represent in my logical model. In my physical model I have to make all of these columns nullable since I have both Person(s) and Organization(s) rolled up into one table.
The question you have to ask yourself is do I care? If you don't care to know the hidden dependencies in your physical model, then don't bother with logical models. If you do care, then logical models are a necessity.
To me, the conceptual data model is a representation of the subjects under consideration in order to confirm/agree/communicate the shape/meaning of concepts that exist. It's the fundamental model that establishes a common understanding. Whether this a high level/outline or fully specified matters not.
The logical data model takes into account a class of implementation platform (relational, OO, document, etc).
The physical data model is directly implementable on a specific technology platform and considers access paths/storage/security/etc.
Go back another level - abstract your data objects and attributes : person and organisation = Party Type, Forename, Surname, CompanyName = Party Names - thats the "logical" bit ?
What you are describing is what I would term as conceptual modeling. Abstraction is a great way to build conceptual models that document important entities and their relationships without overloading the model consumers with too much detail.