Original URL: http://www.theregister.co.uk/2007/06/14/data_modelling_layers/

Data modelling layers: do you wanna get logical or physical

An introduction to data models

By David Norfolk

Posted in Developer, 14th June 2007 16:06 GMT

Interview In the bad old days we used to progress from "current physical" to "current logical" models. We then used to transform the "current logical" to the "new logical" – and about then the deadline cut in and we scurried about hacking the code for the new system which is about as "new physical" as you can get. No wonder the agile people noticed that it made more sense to devote your time and intelligence to the "new physical", which wasn't going to be thrown away soon after you finished.

And yet, I remember that modelling the current physical and logical models of, in particular, the data we were processing was how I convinced my business users that I understood their needs. And I found many potential errors in the process of modelling the new logical, which could be addressed relatively cheaply just by redrawing the model.

So, was the implementation of process back then wrong, rather than the process itself? Do physical and logical models – and, specifically, physical and logical data models – have a place in development today?

Picture of Donna Burbank.

That seemed like a question to ask Donna Burbank (director, Enterprise Modelling and Architecture Solutions at Embarcadero, the data analysis tools vendor).

Reg Developer: So, Donna, what are physical & logical data models?

Donna: The physical model is designed for a particular database platform, such as Oracle, SQL Server, Sybase, etc. A logical model, on the other hand, sits between the conceptual/business layer and the physical/implementation layer. While it can be considered readable by business users, and contains key business definitions, it is structured in a way that can drive the creation of physical database structures.

The logical model would contain a single entity definition of "customer", for example, and this could be mapped to multiple physical implementations: tables on Oracle, SQL Sever, Sybase, DB2, etc.

Reg Developer: Well, why do we need a logical model? Surely, we're unlikely to move off Oracle, say; and the Oracle database contains all we're going to store about customer, so why not simply document our Oracle implementation?

Donna: A logical model provides key semantic and logical information around data. In using the word "logical", in this context I'm referring to the mathematical sense of the word "logic". There are core principles of set theory introduced in the logical model. For example, what information is contained in this set of data that our Oracle database holds and how does this information logically relate together? For example, can a customer have more than one account with the company? Or just one? That difference is subtle, but it affects the way the tables are created on the physical database. The logical model helps flush out these details.

By semantics, I mean the definitions and business meaning of data. Before we build a table on Oracle called CUST, let's understand what data needs to be contained in that table. The definition of something as simple as customer seems straightforward, but there can be key differences in meaning depending on who's defining and using the data.

For example, a customer to accounting may be a person who has purchased a product from the company and has an active account. A customer to the sales organisation may be a prospect who has not yet purchased a product from the company. Mixing these two together can cause incorrect information to be reported to an end user. When the CEO asks "how many customers do we have?" these seemingly nitpicky differences can be important.

Think of a data architect as playing a similar role to an architect building a house. The customer purchasing the house starts with a basic request like "I want a new house in the countryside". The architect then needs to ask questions and build a model of this new house with enough detail for the construction engineer to build a physical design. For example, would you like a single story or multiple levels in your home? Sliding-glass windows, double-hung, bay? Defining these details can be frustrating and time-consuming to the customer, but they are critical to ensuring that the end product meets the customers' needs, and at the same time is structurally solid. A logical data model serves a similar role.

Reg Developer: OK, so now we have two data models. But I'm hearing people talk about a third level, the conceptual data model. What's that, and why would we need it?

Donna: Well, a conceptual data model is a design layer above a logical data model. It is meant to be read and understood by business users and has fewer objects than a logical model

In a way, you could simply use a logical model with fewer objects and/or constraints to show to your business users and many data architects use the same rules (cardinality, supertyping, etc.) in a conceptual model that they do in the logical - but they may have fewer entities and perhaps not display attributes, for example.

However, by having a conceptual model as a distinct design layer, you are able to see the relationship and lineage map from the business needs (conceptual), to the design layer (logical) to the physical layer (database). You can "forward engineer" from the conceptual to the logical and keep this lineage intact. That's a key difference.

And many other data architects and (importantly) business users consider the conceptual model to be more free-form, without the design constraints of a logical model. This allows the business users, in particular, to use "boxes-and-lines" to express their ideas and concepts around the basics subject areas and data objects of the business, without having to be a data modeller. The data modeller can then add the appropriate constraints to the logical model; leaving the business user free to think creatively. The key here is that this helps to get the requirements from the business - which is often a challenge. Don't make the business user be a data modeller...let them think and design in a format more similar to PowerPoint and Visio.

Reg Developer: All right, so which group wins? How do you handle this in Embarcadero's tools?

Donna: After interviews with many customers, the result was split about 50/50 between the two camps.

Embarcadero's tool allows both methods, and both can be forward engineered to a logical data model. The conceptual model with rules transforms more fully, with less work needed from the data architect, but both can be done.

Reg Developer: Do you have an illustration of this?

Donna: The pyramid below shows the various design layers, the audiences for each, the objects in each, and the fact that the number of objects decreases as you go from physical to conceptual (i.e. there may be many physical tables required to support the concept of "sales").

Illustration of the data modelling pyramid.

Reg Developer: Can't UML be considered a conceptual model? Why do you see a need for a separate conceptual data model?

Donna: Many people do use UML to create abstract, conceptual layer. But while UML does well expressing the interactions between the data and the processes, it does not accommodate some of the core principles around designing data itself and the relationships between data items. Concepts such as identifiers/primary keys, for example, are not expressed in a UML class diagram. A conceptual data model is designed to be easily mapped to a logical data model. UML is more suited to application design [fair enough, but that might just be an omission in MDA, the OMG's Model Driven Architecture - Ed].

We also hear from many of our business users and data modellers that the UML is difficult to understand. Perhaps "difficult" means only that it's not a graphical notation that they are familiar with, i.e. "it doesn't look like a data model to me", or "where's my Visio"? And/or this could be a result of the mistake that many architects make in showing the wrong level of detail to the customer, and that the UML diagrams presented were too low-level to be helpful to business users, and too application-focused to be helpful to the data modellers.

Honestly, another big factor is a societal one. There seems to be separate camps in the UML and ER modelling world and never the two shall speak. This is unfortunate, since I think there are commonalities between the two. I think some of this is changing with efforts like the IMM (Information Management Metamodel) from the OMG, which is attempting to better mesh the UML and ER worlds in a formal metamodel and exchange format.

But, until these two worlds play more nicely together, we'll choose to focus our model on our core audience, the data modeller. But even with that consideration, I do think that the conceptual model as we see it is an easier format for the average business user to understand.

Reg Developer: And XML? How does that fit in? Isn't that data too? I'm sure I remember using data analysis generally and E-R diagramming specifically to design hierarchical IMS databases; and XML is 'just' another data hierarchy.

Donna: XML most certainly fits into these modelling layers. However, similar to the UML discussion, there are currently different camps/factions/silos that don't communicate well. We're seeing our traditional RDBMS data modeller becoming more familiar with XML, but that's a more recent phenomenon. Getting XML developers used to data modelling is a separate challenge - as we mentioned before, the developer crowd can be a harder one to convince of the benefits of a model.

But the benefits are the same - a common conceptual and logical model of the business with different implementation layers - XML being one of those.

We're currently guilty of this disconnect ourselves in our tools. We currently have wizards to export XML from a relational logical design, but we're still treating XML as a translation from the relational world. This actually works well, given the fact that I mentioned earlier that most of our relational database modellers aren't necessarily familiar with XML - allowing them to translate into XML from a format they're familiar with works. But in the longer term, we're working on modelling XML better in its own, native, hierarchical format.

Reg Developer: That's all clear enough, I suppose, and the future directions are interesting, but why do we need a data model at all? Isn't this all just 'stuff' getting between the programmer and her users?

Donna: That's a religious question! There's the basic principal of design, then build. Would you want to live in a house that had been designed without a blueprint?

And then there's reuse - most companies have common data objects such as customer, product, etc. Rather than reinvent the wheel for each new project, better to start from the same core design in the data model.

And what about data quality? In the example above, if every project uses a different definition of 'customer', for example, there are bound to be discrepancies in the data. In fact, there's a whole Master Data Management industry devoted to fixing the problems experienced by companies without a coherent model of their data.

Data governance is yet another issue. The data models help to provide an effective inventory of your data assets. Most companies have a wide variety of database platforms with hundreds, thousands, and even millions of tables. Reverse engineering from these platforms into a physical (and eventually a logical and conceptual model helps companies better understand what they have today – which can be extremely important if these assets form the foundation of regulatory reports, that directors sign off on, on pain of going to jail...

Reg Developer: OK, play the governance card, everybody else does (although you make a good point). But how accepted is data modelling in the community? How often do you need to explain the benefits to customers and potential customers?

I'd say that in the data community, having a data model is a fairly accepted principle, and it is rare that we need to explain the benefits (but it does still happen from time-to-time). Among the developer community, including some DBAs, buy-in is less accepted. I'd say about 50 per cent. I think the attitude and culture is different, more of the "I have a deadline to meet, I don't have time for design", "leave me alone and let me code", etc.

I think we can sum up by saying that understanding data is still important and there is still some point in the old disciplines of data analysis, even though we have moved forward in so many ways (business-centric development, XML and so on). In the end, however, we still have to deal with the old problem of development silos – business analysts, coders, database designers, and so on, all doing their own thing and not talking to each other. ®