The ORM Foundation

At work we're doing a rewrite of a large database, and I'm finally getting to use NORMA in the field! After reverse-engineering the database, it's obvious to see that there was almost no thought about normalization. The good news is that our updated model will be amazing. The bad news? Getting there is a huge pain.

It would be incredibly helpful to have the ability to merge entity types without having to dance around the model clicking and dragging. Being able to select "end_date", "EndDate-1", "Enddate", "stopdate", etc. and merge them into a single "EndDate" would be a huge time saver. I cannot express how much time this would save. SO MUCH TIME.

Thanks!

It seems to me that what your are encountering is definitely not a "special case". (based on what I have seen in several organisations)

But I think that "the problem" is much deeper than "no thought given to normalisation" (which of course seems to be the case)
Here are some contributory elements:

* developers who don't understand the significance of the relational model.
* managers who "just want results" and either don't care and/or don't understand the importance of data structure.
* university lecturers who teach everything about OO and RAD and very little about the relational model.
* a general lack of understanding of the critical role of language in development projects.

You can't solve these kinds of problems by adding new features to a tool.

Or as I once heard :" A fool with a tool is still a fool"  

Ken 

Ken,

Perceptive as always. Each of those factors contributed significantly to the current mess of a schema that we have now. It's also true that being able to merge entities won't prevent this problem in the future.

It would, however, enable me to refactor the silly thing more quickly! I've spent 3 days now dragging lines around to start getting a handle on things, and goll durn it, it's making my wrist hurt! This fool wants more power tools. ;-)

Hi Tyler,

I don't think you were quite this early on NORMA, but one of the first features (before we even had diagrams) was a Property Window property called 'RolePlayer' for a Role selection. It's still there, and fortunately supports multi-select.

1. Create a temporary diagram in the model.
2. Select the object type you want to merge into oblivion in the Model Browser.
3. Show the context window.
4. Select all of the shapes in the context window into the dummy diagram (you can't edit in the context window).
5. Ctrl-click all of the roles attached to your doomed object type.
6. In the Properties Window, set the RolePlayer property for the selection.

Obviously, you may need to clean up the shape display, but you will have fewer roles to drag around. If you don't want to bother with the context menu stuff, you can use the Verbalization Browser to see fact types that are still attached to the object type.

Another request I've heard in this area is the ability to create equivalence links between object types without merging them. This is essentially a database federation scenario, where the object types may have different representations (including identification schemes) in different databases but represent the same concept.

-Matt

PS Of course, if you want to play at the XML, you can search and replace the object type guids. I'm not sure if the shapes will resize on load, but a quick rename and restore of the new role player (do not use undo to restore) will force the shapes to assume their correct sizes.

 Hi Tyler,

I was wondering why you don't "just" rename the "offending" entity types to begin with so that you get rid of the synonyms? (if indeed that's what they are)

And it was not my intention to brand you as a "fool", rather to make the general point that the folks who created your present mess probably used some kind of tool but without understanding the semantic significance of what they were doing.

Ken

Ken,

I know you weren't aiming that at me! Just a little self-deprecating humor. And yes, the current mess was created with a tool. "Notepad", I think it's called. <shudder />

Unfortunately NORMA won't let me rename the objects to something that already exists in the model. That's a great feature for safety, but in the case of refactoring a large model it's inconvenient.

Matt,

Unfortunately that only helps if there are several roles that the offending Entity Type plays. In this case, we have hundreds of synonym objects that were created when the schema was reverse engineered. I don't blame NORMA for the mess; in a database of several hundred tables there are maybe two dozen foreign keys.

It is a nice tip, though. I've been using the context window a LOT recently, but didn't know about the multi-select RolePlayer change. That'll come in handy after a couple more passes!

Tyler,
Yes, as you say  - simple renaming does not work.

What I meant by "just" renaming was a procedure similar to this:
* select the offending synonym object type on one diagram
* drag the desired object type onto the drawing surface
* change the role-link from offending to desired.
* delete the offending object type

However, I suppose that that is what you may have been doing already - darn it!
Matt's method looks good for multiple object types

One good thing that you might consider contributing is a short "Lessons learned on how not to design databases" case study abstraction of what you have found. What's the application domain?

Ken

Ken,

Alas, your proposed procedure is exactly what I've been doing. It's quick enough for merging down a few hundred Object Types, but when the count gets into the thousands....

Interesting proposal for the "Lessons Learned" document. The domain is a direct sales company that is taking orders and paying commission to its sales reps. There are several areas dealing with government IDs, addresses, warehousing and inventory, commissions, promotions, etc.

What do you mean by "case study abstraction"? I'm pretty sure they wouldn't want me sharing any of the schema, but I can certainly make a narrative describing the considerations faced during design and the consequences of earlier decisions. I could also include some remarks on my experience doing the reverse engineering; the pain points, and maybe some mock-ups for tooling improvements.

I feel like a jerk whenever I make feature requests. It feels like I'm standing on the sidelines saying, "Make this for me NOW! Please?" when I should be buckling down and getting into the code myself. Matt's working on cool features that are critical for a model-driven methodology, and something like this would be a distraction. Maybe I could do the mock-up as a standalone WPF app just for proof-of-concept.

Bah... and now, back to work!

Tyler,

Pity about the procedure but I suspected as much. And yes - thousands gets tedious.But why was this system designed to be like this?

By a "case study abstraction" I meant a narrative of your experiences in the project that does not expose any confidential data such as the data model or the business/user identity.

From what you have said, I see a possible narrative such as:
1: The client had a problem (outline problem e.g. errors caused by data duplication, update anomalies that appeared as - (describe symptoms))
2: When we reverse engineered the database we found (description of findings + semantic observations/business implications etc)
3. It took (amount of effort) to correct the database.
4. If they had used ORM in the initial project the client would have avoided (describe the problems & their business consequences + the wasted effort to design a flawed system)

Ken

Ken Evans:

But why was this system designed to be like this?

If it's anything like the enterprise databases I've encountered, it was never designed at all. It just grew by people throwing one thing after another at it, without anyone ever trying to understand or optimise the big picture. This is exacerbated by success; as soon as a system or especially a product becomes a success, it grows large, which means no-one is willing to understand it all just to add one new feature. In addition, it accrues an entire ecosystem of extensions, add-ons and other kinds of dependencies. No-one can ever know what all the schema dependencies are so nothing can ever be changed - the only path is to add new things. Any mistakes made in the early data modelling are magnified a thousand times as new additions are made that work around the inability to change the basic structures. The result is best described by the acronym BBOM - Big Ball Of Mud.

If, as often happens now-a-days, the original model is a semi-automatic or simplistic mapping of an object-oriented schema, the problems are much worse, even if that O-O schema was carefully designed and really rather good. In fact, perhaps especially if it was rather good, since that reflects the owner's belief in the supremacy of the O-O model, and hang the mere storage concerns. Chuck that object model over the wall and let Hibernate deal with it.

Adding to this woe is the fact that enterprise markets enshrine mediocrity. The product that just installs and works produces no on-going consulting revenue (from junior "consultants" being charged out at five or ten times their market value as employees). My point? It's the same consulting companies who get to write the purchasing recommendations, since no CIO will write a seven- or eight-figure cheque without first getting a consultant's recommendation to protect their career. As a result, enterprises buy the worst product that can, with lots of help and constant ongoing coddling, be coerced into doing most of the job. So the markets are dominated by products like SDM, SAP, and a hundred others equally as horrendous. The purchasing process is simply not rational and informed, as Ken seems to wish it was.

How do I know this stuff? Because I've spent three decades as an architect of three major software products that each mostly maintained their architectural integrity (thanks to my work) over a decade or more, dozens or hundreds of releases, millions of lines of code, many sub-products and spin-offs, being deployed in mission-critical roles on millions of computers. They ran or are running banks, telcos, stock exchanges, aircraft manufacturers, armies, national postal systems... and yet, because the market consistently preferred far inferior products, the products I designed, though successful by many measures, never became major cash cows and dominated to the point of excluding others from the industry. Can you tell I'm proud of my work? Yet I never made anything more than salary from all my hard work and the risks I (and my co-founders) took.

I'm not bitter about that... just realistic... I wouldn't have it another way. In fact, I'm still somewhat hopeful that software purchasing will grow up and start saying no to this rubbish, but I doubt it'll happen in my lifetime. It took four to six hundred years for accounting and banking to grow to the professional calibre we see today, and there's still such skulduggery there. Until software engineers and product managers start showing the markets that a new standard of behaviour is possible, it will not become accepted and expected.

And that is why I believe that fact-based modelling is the key to the software industry entering its adulthood. The problem of complexification can only be solved by learning how to never take a major modelling mis-step, and by implementing software so as to minimise the exposure of modelling mistakes - so they can be fixed without rewriting half the world. This requires reducing dependencies on schemas to the minimum required to support each transaction's semantics; which means no visibility of wide tables or objects, just the individual meanings encoded in those tables.

 Hi Clifford,

Thanks for "View from OZ"  Your assessment reflects much of my own experience.

I deliberately chose the question: "Why was the system designed to be like this?"  because it reflects my philosophical persepective on what is happening "out there".

Specifically, however "awful" a particular implementation actually is, it seems to me that the activities that you have described, (and what Tyler seems to be experiencing) can be usefully considered under the collective name: "The Design Process".

Naming the process in this way enables dispassionate and business based assessments and comparisons of the quality of instances of "The Design Process". Which, in this hype-led world seems to be one of the things that is missing.

I like your BBOM acronym. However, I suggest that we adapt it to the world of "Quality" by using it to mean "Big Ball of Muda".

 Or to quote Dijkstra:  “Don't blame me for the fact that competent programming, as I view it as an intellectual possibility, will be too difficult for 'the average programmer', you must not fall into the trap of rejecting a surgical technique because it is beyond the capabilities of the barber in his shop around the corner.”

 Ken

In my vocabulary, design means a rational choice between relevant alternative compromises. If no such thought process occurs, there is no design - the concept requires intent.

I don't know what relevance "Oz" might have. The experiences I describe are particularly with global companies, not local ones. European ones seem to be more likely to engage in long-term thinking, however, compared to American ones. Large Australian enterprises tend to just follow the trends created elsewhere.

Hah, nice link with muda though! Note that competent (or otherwise) programming is simply not the issue, as I see it. I thought I made that clear what I described the thought processes that go into the building of a BBOM. It's more a case of a continuous unwillingness to anticipate the future, and dealing merely with the immediate problem.

BTW, on the original topic - de-duplication is much easier in text representations like CQL. Or maybe it's that we don't have so many standard tools that can perform bulk manipulations on generalised graphical representations. Either way, if Tyler converts his model to CQL, de-duplicates it using global text edits, then... a piece of the puzzle is missing because he can't convert it back to NORMA to draw new diagrams. Phhht. One day... meantime he could still derive a good relational model and emit SQL from the cleaned-up CQL.

A quick update for everyone that's interested:

I carried on paring down the large model for a couple of days, then everyone else lost patience and wanted faster results. They decided that since we had such good results with building from the ground up on the first section I tackled, that we could use the same process and get quicker traction for the other areas.

It was decided that I'd finish up my section and two other people would feed me fact types that would be added into another model. It's now been nearly 2 weeks. I wouldn't say we're going faster, but there have been some interesting side-effects.

One of the guys working on gathering fact types has been putting them into NORMA directly. It took him a few days to get comfortable with the tool, but he's done a great job. He's had maybe 90 minutes worth of training on NORMA and conceptual modeling practices, but has been able to figure out a lot of things just by playing with the tool and watching the verbalization browser's feedback. One of the most confusing things has been editing multi-column external constraints. Yesterday I explained the gestures and gave some examples that made sense, but that's something he wasn't able to figure out on his own.

One of our business analysts has installed NORMA so that he can see the models as we're making them. I gave him 30 minutes of training yesterday and he was satisfied with the combination of the Context Window, the Verbalization Browser, and the Model Browser.

While giving him instruction, one of the other business analysts overheard our conversation and came over to look at the fancy-looking pictures. This BA isn't associated with our project at all, but got very excited when he saw what the tool could do for reporting. The HTML report generator was what got his blood pumping.

In the meantime, our DBA turned in his two weeks notice. Today is his last day, and our collaboration is being passed on to his successor. I've noticed a trend that when I say "and the tool generates a nice clean 5th-Normal Form database for you", database guys tend to shudder in an almost allergic reaction. From what I can gather, they think it's overly academic, not realistic, and a pain to work with. I've suggested adding views to mitigate some of the joins, but they remain skeptical about working with the database.

And there's my report from the field!

 Hi Tyler,

That's a very interesting report.  Looks like you have overcome one of the main barriers to progress = "I have never heard of that so it can't be any good".

Tyler Young:

"and the tool generates a nice clean 5th-Normal Form database for you", database guys tend to shudder in an almost allergic reaction

Makes me wonder if we ought to find an alternative way talking about the power of 5NF

For example:

"NORMA generates a logical schema that does not have any duplications."

Any other ideas?

Ken