Data modelling layers: do you wanna get logical or physical

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 …

[Anonymous Coward]

An Unprofessional Approach

I have always regarded Oracle and the like as Business Prime Data systems where modelling and its maintenance have been essential to an ongoing successful business.

The problem for a lot of smaller Businesses is that they simply cannot afford a professional approach to database design and certainly will not pay a license for DB/Artisan or ER/Studio or see the need for such tools.

Compounding this is Microsoft's decision to push SQL Server to the desktop level whilst neglecting appropriate products like Microsoft Access. These SQL Server designs only have a physical model with limited metadata, if any, but they work at least in the short term.

However, the design degrades markedly when the DBA moves on and maintenance or new features need implementing. This is going to be a costly mistake for such Businesses in the long run.

Good DBA's with modelling skills are worth their weight in gold but rarely found.

[Anonymous Coward]

This isn't new

Any good practitioner of database design has always used three models and mapped them to specific areas of the problem space.

The conceptual model has traditionally mapped to 'business rules' and understanding the roles of each element, or entity in the problem space.

The logical model, i.e.; "E/R diagrams" reflect the attributes of each entity in the problem space and their relations and relationships to each other.

The physical model, i.e; "Tables and views" reflects the actual implementation of the models in the target DBMS.

What am I missing?

[Mark Studden]

Data Modelling is not an "old discipline", it's an essential professional skill.

Sigh.

After many years of Data Modelling consultancy, experience has taught me that the problem for a lot of businesses is that they really cannot afford NOT to take a professional approach to database design.

In the long run, a well designed database system will repay its design costs many times over. Poorly designed databases (especially "hacked physical models" lashed up by developers at the last moment) cost more than they should do to code against, to maintain, to extend, to report against and eventually to replace.

Far too many businesses still ignore these facts, paying the price in countless little increments of unnecessary expenditure.

The licence (it's a noun) costs for DB/Artisan or ER/Studio or any other top quality ER tool are piffling in comparison. Even my daily rate pales into insignificance.

[Matt]

I agree

I've always used data models too. We went through a bit of a hard time the last few years because IT was listening to developers too much and going straight to a poorly thought out physical model.

Companies seem to have realised again how much it costs to miss the logical model stage, both in loss of flexibility and re-work.

Suddenly I'm being asked if I know Powerdesigner or ERwin again!

Personally, I think a lot of businesses started looking at SOA because of fragmented systems caused by a lack of analysis and logical modeling. Again, some now seem to have realised their mistake having been hit by the overheads of maintaining different copies of the same logical entity and having failed to really solve that with SOA.

[Richard Gowan]

Not this old stuff....

Logical vs physical... what a title. This is a debate out of 70s.

Obviously - we should have the models. In truth, we probably don't have the models and no longer live in a simple OLTP/ER world. Instead there's XML, ROLAP, and ORDBMSs.

So much for that. We'll do models if we have time.

Now about that first post. What is this about DBAs doing the models. That's crazy. The developers should do the models. All of them. Perhaps with some DBA input but that's it.

[Matt]

Design

Richard's comment says it all, that's why these projects go so badly. Developers don't understand data modeling and are generally only interested in their latest project and trying out their latest buzz word. He's right, in that a lot of them think that data modeling is out-of-date.

Adding new buzz words doesn't make it out-of-date, in fact it makes it more important so fit any new ideas into the right place.

In terms of who does the data model: The advantage of the DBA is that he sees beyond the one project, or one update and it's him who'll have to live with the consequences because he can't just wash his hands and move on to the next project. On the other hand, any pan-project team would do, as long as they've got the right training and are looking out for the business as a whole.

[Richard Gowan]

DBA and the model

One thing that may be forgotten here is that many DBAs in smaller businesses support off-the-shelf (albeit customized) software for much of the time. SAP, Siebel and the likes.

They probably haven't done many production enhancements in terms of actual code additions or reprogramming. For sure they patch systems. But more the database side than the application code. And ultimately, it's the app maintenance that a model (or lack of) affects.

I guess... at the end of the day... most businesses don't care. And to some extent they have good reason. For performance to matter tomorrow, we have to first meet todays goals. It's fair to expect that tidying up is left to stage two or three. It's up to us as developers to ensure that we isolate the app from the structure enough that a tidy up will actually be feasible later.

[Jason Alcock]

data modelling does *not* imply RDBMS or E/R or...

I'm uncomfortable with the implication that 'data modeling' implies a RDBMS and E/R tool.

Let's suppose we have perfect object persistence (a stretch but humor me) then would we would still need data modeling?

OOA\OOD includes a means of analysing and structuring data. Conceptual and implementation.

However the modeling process here is not predictable and robust. So what would a "normal form analysis" of OOD look like? Or looking in reverse; if we had Normal Form analysis of the OOD why would that not be as high a quality of data model as any other?

I believe that the OO/Applications teams don't do enough data analysis and modeling. But the "data modeling activity" should be independent of the whole relational dB tool industry.

Now SOA is an interesting case. Here IMHO the modelling needs to be done on the surface of each service. Far from the database. How a data modelers going to engage here?.

[David Norfolk]

Title

I know what Jason means when he says "I'm uncomfortable with the implication that 'data modeling' implies a RDBMS and E/R tool". He makes some interesting comments and I'm pretty much in agreement.

But in the context of the article, only the first part worries me a bit. The second part doesn't so much. I've used E/R modelling to help me build (hierarchical) IMS databases. I see no reason to believe that it wouldn't be useful when designing (hierarchical) XML databases or even understanding the data in an object. I think the reader must distinguish between the logical properties of the data (including, but not limited to, E-R properties) and the physical implementation in a so-called RDBMS.

The first part is trickier. Most commercial databases claim to be RDBMS (often with not very much justifification - see Chris Date) and I imagine that most Embarcadero customers use ER tools for RDBMS. So perhaps the article does rather assume RDBMS. But this is realistic, and it does mention XML alternatives.

But I think that E-R modelling is an important part of data modelling (or, rather, data analysis) and that data analysis is an important precursor to any kind of application design. The point of RDBMS is that it should help you implement your data analysis more directly. I don't think Donna's views, as reported, are inconsistent with any of this.

[Chris Rimmer]

E-R vs UML

I'm no expert on E-R modelling, but from the examples that I've seen it doesn't seem well suited to expressing the idea of hierarchies of types / entities, which can be useful in some situations. In those cases, I'd have thought that UML would be more suitable than E-R. If the business people have a problem understanding UML, they should go and learn a bit about it, say as much as they expect (entirely reasonably) IT people to learn about the business. It's at least as much their responsibility as the IT people's to ensure that the requirements are communicated clearly, and surely communication is the main point of modelling?

I don't understand the point about UML not expressing concepts such as identifiers/primary keys. That seems to me to be a low-level implementation detail that should be kept well clear of logical modelling.

By the way, it's "principles", not "principals" in 3 places in the article. Yes, I was able to correct it in my head, but I find that having to do so is quite distracting.