Thursday, November 12, 2009

Data Modeling

Most readers of the blog are probably DBA's, or do DBA work along with development or other duties.

Though my title is DBA, Data Modeling is something I really like to do.

When first learning Oracle, I cut my teeth on data modeling, and used CASE 5.1 on unix to model a database system. True, CASE 5.0 used an Oracle Forms 3.x based interface, and the GUI modeling was unix only.

That was alright with me, as the Form interface allowed manual changes to be made quite quickly.

And the graphic modeling tool was fairly decent, even on a PC running Hummingbird X Server.

When Designer 2000 came out, it was clearly a more capable tool. Not only did it do everything that CASE 5.1 could do, it could do more. I won't make any silly claim that I was ever able to fully exploit D2K, as it was an end-to-end tool that could do much more than model data and databases.

What it could do with just the databases however was quite good.  Data models could be created, and then a physical database could be generated from the model.

Changes in the database model could be reverse engineered back to the model, and changes in the model could be forward engineered in to the physical model. D2K could truly separate logical and physical models, and allow changes to be migrated back and forth between the two.

There are other high end tools such as Erwin which can no doubt accomplish the same thing, but I have not used them.  

One important differentiation for me between D2K and other tools was that D2K worked with Barker Notation, which is the notation I first learned, and the one I still prefer.  

I should not speak of Designer 2000 in past tense I guess, as it is still available from Oracle as part of the Oracle Development Suite, but is now called Oracle Designer.  It just hasn't received much attention in the past few years, as I think many people have come to think of data modeling as too much overhead.  

I've tried several low end tools in the past few years, and while some claim to separate logical and physical models, those that I have tried actually do a rather poor job of it.

All this leads to some new (at least, new to me) developments from of all places, Microsoft.

Maybe you have heard of Oslo, Microsoft's Data Modeling toolset that has been in development for the past couple years.

If you're just now hearing about it, you will likely be hearing much more. The bit I have read has made me think this will be a very impressive tool.

If you have done data modeling, you have likely used traditional tools that allow you to define entities, drop them on a graphical model, and define relationships.

The tool you used may even have allowed you to create domains that could be used to provide data consistency among the entities.

Oslo is different.  

Oslo incorporates a data definition language M. The definitions can be translated to T-SQL, which in turn can be used to create the physical aspects of the model.  M also allows easy creation of strongly typed data types which are carried over into the model.

Whether Oslo will allow round trip engineering ala D2K, I don't yet know.

I do however think this is a very innovative approach to modeling data. 

Here are a few Oslo related links to peruse :

You may be thinking that I have given SQL Developer Data Modeler short shrift.

Along with a lot of other folks, I eagerly anticipated the arrival of SQL Developer Data Modeler.

And along with many others, was disappointed to learn that this add on to SQL Developer would set us back a cool $3000 US per seat.  That seems a pretty steep price for tool that is nowhere near as capable as Oracle Designer, which is included as part of the Oracle Internet Developer Suite. True the price is nearly double that of SQL Modeler at $5800, but you get quite a bit more than just Designer with the Suite.

As for the cost of Oslo, it's probably too early to tell.

Some reading suggests that it will be included as part of SQL Server 2008, but it's probably too soon to tell.

Why all the talk about a SQL Server specific tool?

Because data modeling has been in a rut for quite some time, and Microsoft seems to have broken out of that rut.  It's time for Oracle to take notice and provide better tools for modeling, rather than upholding the status quo.


Robert Freeman said...

Nice post Jared! I really think that Data Modeling has gotten a back seat in this day of "Agile" development. I think that this is less of a DBA problem but more of point-of-view that is coming from the development ranks. Part of this, I think, comes from the different perspectives of the two camps.

DBA's look at the model and the data from a long term perspective. We understand that data tends to outlive applications.

Developers seem to look at things from a delivery point-of-view. Let's get it done and distributed. Add to that things like object-relational impedance which leads to middle layers like Hibernate and you have a nasty data store more often than not.

So, there is a impedance that has to be addressed. In my experience, if the DBA is a good advocate for data modeling, and if the developers are willing to discuss the benefits of data modeling then common ground can be found and data modeling can be folded into any "Agile" process.

As time goes on, the negative implications of bad data modeling on the Enterprise will become more clear. When this happens (DBA's) will have to make sure that we surface these issues and use these problems as educational opportunities.

Gints Plivna said...

Interesting that I'm writing similar article in my blog just now. In Latvian though ;)
Ok speaking about Designer - I have some nostalgia for it. It was my first and best tool, until Oracle added versioning to it along with bunch of bugs. I think this was the last straw that broke the camel's back :)
It had this wonderful feature behind - database repository. It was really cool and I fully understood the power of it only after started to use tools with everything in xml. No SQL, no nice programming interface, no ad hoc reports, no possibility quickly update many objects ...
And another aspect - I don't think any organization can earn billions with software engineering tools :)

3K for SQL developer modeler - that's simply funny. Feature list necessary for me or my organization definitely doesn't impress for 3K :)
All other tools (Rational, Enterprise Architect) my company bought and I've used for requirements specification and analysis until now lack the possibilty to create DB model from conceptual data model (class model). However I look into the future and hope I have another possibility to work with such a wonderful tool :)
And thanks for info about Oslo. Should look at it.

Anonymous said...

Hi Jared,

Do you remember Richard Barker's books on the various CASE components and Oracle's modeling methodology?

I think I have autographed copies somewhere about the house and can recall hoisting a few beers with him (which doesn't really comment on your DM post, since I'm not a modeler (by trade :)).


Gigante Legendario said...

More often than not I've found customers asking for advice once the project is near completion, like: Hey, Mr. DBA, my app sucks at stress testing, Can you tune the database?. And then you find a crappy data model, crappy access paths, etc. I usually end up having to create a LOT of indexes (somehow developers seem to forget them). I concur with Robert about the main difference on how DBAs and Developers view data.

I think the point would be for the project managers (who SHOULD have an overview of the project) to take into account the need for an apps DBA from the scratch (design stage).

In another note: I had to create a ERD for a class and had an Oracle 10g and MySQL installed on my notebook. The schema was already created in the Oracle DB so I took TOAD and asked for an ERD. Plainly, it sucked. I tried SQL Developer but couldn't find anything to create an ERD (without paying). So, I ended up generating the DDL of the schema, passing it thorugh a little conversion script I have that makes standard Oracle SQL into standard MySQL SQL, put it in the MySQL instance and use MySQL Workbench to get a good ERD.

The error on my side was having the DB done BEFORE the ER, and not the other way around... but I never use modeling tools... I use the plain old pen and paper. Keep in mind that I'm talking bout 30 tables at most, since they are all for college and rather simple at that.

Jared said...

Take a look at the link pointed to by 'Barker Notation' in the blog text.

That is a link to the modeling book in the CASE series by Barker.

Also, here is a link to a list of a number of drawing and modeling tools, some free.

This includes MySQL modeling.

Drawing Tools

khair said...

I have used several modeling tools ..
My favorite is is Power Designer (by Sybase) with several notations (UML, C++, VB. T-SQL, SQL) and a lot of reverse engineering features. A true multi-platform tool and not free of course.

sap upgrades said...

I agree with you and also thinks the same that Oracle needs to make some better modelling tools as data model is the base structure on which the whole database depends. I am using Power designer, it is really good but I need some change.