Tuesday, 9 September 2008

Is JDev 11g ready to replace Designer's table modeller?

There's been a few announcements recently that the upcoming version of SQL Developer will include database table modelling features. It seems to be a little know fact outside of JDeveloper circles that JDeveloper has had "offline" table modelling for sometime. The 11g release has expanded upon this feature set to include more options, that in turn make it (more of) a viable option to replace Oracle Designer physical database modelling.

(I suspect the upcoming SQL Dev modelling features just come from JDev anyhow, but I can't confirm this).

In particular in the 11g release we now see that offline database objects have support for normal tables, external tables, index organised tables, temp tables and so on through the radio group at the top of the screen:

In addition for normal tables when selecting the Storage Options button under the Table Properties subnode, we now have the full gamut of storage clause options for the table we're creating:

I know of a few Oracle sites that are holding onto Oracle Designer purely for its physical database modelling features only. Through the latest release of JDeveloper we can see there is the potential to drop Designer all together and go for the free JDeveloper. Now only if those sites could drop Oracle Forms too ;-)


Borkur Steingrimsson said...


Could you tell us how the "offline" model is stored in jdev? Is it just a SQL file on your local drive? Also, is there any modelling capability other than setting some physical attributes for a CREATE statement? Designer is/was obviously a bit more than just that :)

Is there an "online" modelling option that lets DB developers work on a whole system, with multi-user support and versioning and branching etc? How about design diagrams and ER models?

OWB has a lot of those features, but the name alone suggests that it shouldn't be used for other things than warehousing. Back in the day I was using Designer to model my DW and OWB to build the ETL, but as more and more features go added to the modelling capabilities of OWB I have totally stopped using Designer...

Borkur Steingrimsson

Chris Muir said...

Hi Borkur

The offline model is stored within a JDev project as a series of xml property files behind the scenes. These act as the "repository" similar to Designer's database approach. The actual diagram file is a binary file format which I don't know much about.

When you say capabilities beyond physical create statement attributes, what are you referring to? I'll elaborate if you can give me specifics.

When I say "offline" it actually means what you mean by "online". The property files that comprise the repository behind the scene can be checked into a version control system to be shared among developers, much like objects in the Designer repository are checked in to be shared too. The key difference for the repository is JDev is file based, while Designer is database based. Obviously some Designer users will be unhappy with the non-database based repository, yet that's just a preference.

In JDeveloper when we say "online" database modelling, we mean we're changing the database objects on the fly directly. Thus "offline" is modelling the database without changing the objects, and later generating the changes to the database using create/alter/replace scripts similar to Designer's approach.

As I mentioned this JDev features only replaces the physical database modelling side of Designer, not the whole other side of designer such as ER models, design diagrams etc. JDeveloper is not a replacement for the complete Designer feature set (sadly). However do note that JDevelopers supports UML diagramming notations in many forms, and other diagramming notations that potentially may compliment or move beyond what Designer does.



JCFlack said...

There are two main reasons for putting metadata in a database a la Designer, rather than in XML files a la JDev. First of all, a well designed repository in a database is normalized. For instance, suppose I have table EMP with a foreign key to table DEPT. In JDev, the XML that describes EMP has a reference to the foreign key. The XML for DEPT has no notation that there is a foreign key from EMP that references DEPT. Now suppose I decide to rename DEPT to DEPARTMENTS. There is no way for the XML for EMP to get the FK changed. In Designer, you can easily see that DEPT has a reference from EMP, and if DEPT's name is changed, the change is immediately reflected in EMP's FK.

Secondly, if I want to do reporting, it is very easy to do it from a database. It is hard to do it from XML files. This is especially true when I want to cross reference, like when I need all the dependencies.

Fortunately, the word we heard from Sue Harper at ODTUG is that the plans for SQL Developer's modeling module include a repository that can optionally be placed in a database.

Chris Muir said...

Certainly John, there are pros and cons of each approach. The biggest con, and it's a big one, for a database based repository, is you need a database.

Do you see how it goes back to my point earlier? It's just a matter of preference.

I was never that comfortable with the Designer repository, because it was normalized to the nth degree.

As for the FK issues you talk about in XML, that's the job of JDeveloper. It was never implied to change the XML files directly. You've extrapolated the XML concept too far.

As for reporting, XSLT provides the transformation on the XML files to any reporting requirement. If you're comfortable with XML & XSLT this is easy enough. It is a comparable skill set to learning SQL.

But with these points it just shows my preference for a non database based repository which is a subjective call. So again, it's just a preference. If you like SQL and databases and database repositories, you're not going to like JDev's approach. So be it. The original post was aimed at people who don't want the full gamut of Designer features. Again JDev is unlikely ever to replace that full functionality set. As far as my Designer friends have told me, pretty much no tool currently does that, but Designer itself.


satonaoki said...

> (I suspect the upcoming SQL Dev modelling features just come from JDev anyhow, but I can't confirm this).

Oracle SQL Developer Data Modeling is based on CWD4ALL:


naoki - http://blogs.oracle.com/satonaoki/

Chris Muir said...

Thanks for the update Naoki, that's an interesting development. Though it's not immediately clear that the SQL Dev modelling tool will be CWD4ALL, it's a pretty close bet. Why aquire a product and not use it.