Why do we do logical data modelling?

In the domain of my work, there has been a long-going debate on the purpose of logical data modelling. There are people who care more about the end-result of a database design and choose to focus on the database part and call it "physical data modelling." There are also people who care about both logical and physical data models but are in doubt whether these two are the same or different.

Well, by opening the few books that I left behind my shelf and check the Wikipedia site, I think I do have some ways to answer these questions.

First, what is logical data model? I guess the Wikipedia definition below is fair enough for people to understand.

"A logical data model (LDM) in systems engineering is a representation of an organization's data, organized in terms entities and relationships and is independent of any particular data management technology."

So, the principle is that no specific database technology should be involved in the logical data modelling part. If we look at the typical database theories, such as the one you can get from these books , here are the key areas that we look at during the logical modelling phase.

• Entities
• Attributes
• Relationships: Binary/tertiary/n-ary
• Roles
• Participation
• Keys, super keys, candiate keys, primary key
• Weak Entity Types
• ternary relationship
• Multi-valued Attributes
• Lossless-Join Decomposition
• Functional Dependency
• Normal Forms
• Boyce-Codd Normal Form (BCNF)
• 3rd Normal Form (3NF)
• Update anomalies

It is important to note that part of the E-R modelling is initiated in the conceptual modelling phase. The logical data modelling activity starts by inspecting the E-R model and decides on how the entities and relationships are further arranged into tables.

The term 'Logical Data Model' is sometimes used as a synonym of 'Domain Model' or as an alternative to the domain model. While the two concepts are closely related, and have overlapping goals, a domain model is more focused on capturing the concepts in the problem domain rather than the structure of the data associated with that domain.

Second, why do we need logical data modelling? I think the Wikipedia has a few good points. For example, "Helps common understanding of business data elements and requirements" and " Facilitates avoidance of data redundancy and thus prevent data & business transaction inconsistency."

It is quite apparent that the logical data modelling provides a foundation for designing the database schema. However, many people choose to ignore this fact by creating the database design direct. In fact, the "logical data model" is already inside these people's mind when they are creating the database tables. Otherwise, how can one say that attribute A and attribute B should be in the same table? How can one determine the unique key of a table?

Another vital step in logical data modelling, is the decisions that help to reuse and share data. Most people only understand this point after the database has been used for several years (and when new requirements arrive to the database design). It is hard to say that this is a shame. But a lot of enterprises do need to understand the importance of data by losing billions to re-create solutions every time the database cannot afford the changes.

Third, there has always been debates and discussions about the boundary of logical data modelling and the physical database design. When one comes to generic data modelling where an individual or an organization is generally considered as "Involved Party," the decisions about roll-up/down in the class hierarchy can be either a logical data model decision or a physical database design decision. It is hard to distinguish who should make the final decision in most enterprises. However, a better way to solve this situation is to involved both logical data modellers and the DBAs in the discussion and reach a design where DBAs agree with the logical data modelers. To make this point clear, this is a logical data model decision as long as no specific database technology is involved. The DBAs of a specific database technology can be consulted (so that she/he feels involved and engaged) to find out if a re-work has to be done when the logical design is applied to physical database design.