SQL Developer Data Modeler

Good morning everyone

I decided to start playing with Oracle's SQL Developer Data Modeler 4.2 as part of a technology refresh process.  Having been in the data administration, data architecture, database administration business for over 30 years, I have used a multitude of both Oracle modeling products and third-party enterprise modeling tools.  SQLDDM was Oracle's solution for a very full marketplace of offerings that cost thousands of dollars.  Oracle Designer was in that same field until deprecated years ago.  With the tools market competitiveness heating up, Oracle delivered SQLDDM, a free tool to the marketplace, for Oracle developers to use for creating both simple and complex models.  As part of this tools review/comparison, I used ER Studio Enterprise, ER-Win, and Visual Paradigm.  This review will focus on some nuances found within SQLDDM but also some issues that should be bugs as compared to other tools on the market.

I am hoping for an interesting discussion thread and perhaps some clarification from other users that have either experienced similar issues with the product or have found resolutions that aren't readily known.

To start, I am creating a data model, and eventually an application, to support a "need" I have.  This application will assist in managing Golf Tournaments, Golf Outings and individual golfer scoring activities.  Yes there are many of these on the market, however, I am using this as a learning tool to help build some additional skills.  I would be happy to publish the .dmd file if anyone would like it.

Review:

1. Lots of features, many are not explained properly within the documentation or online.  The documentation does not explain the tool in depth and assumes experience prior to experimenting with the tool.
   1. I find this to be a problem with many of Oracle's tools and associated documentation.  I was looking for a comprehensive "SQLDDM - 101" document or training video to start with and either passed thru it on Oracle's web site or just could not find it.  I will continue to look for this material, however, if anyone has some links to this type of material, it would be very helpful.  As with all Oracle documentation, an Intro guide would be helpful, allowing a user to start from scratch, creating a model from a process driven approach.  Instead, it appears the tool throws you into logical modeling without building out some of the requirements first.  With ER Studio and Visual Paradigm, you need to spell out some of the requirements before you can do anything in terms of modeling.
2. Logical to Relational model transition is very poor.  Tool completely redraws the model, even if the relational model already exists and has been properly created with bent lines and placed entities.
   1. So I found this to be VERY annoying.  After spending countless hours redrawing the relational model after engineering from the logical model, I closed the file (saved first) and reopened it later that afternoon.  I was upset to see that SQLDDM had completely redrawn several portions of the model, moving lines around to areas I had purposely avoided.  This probably has to do with the anchoring options built into the tool.  However, the tool should not make adjustments to the model on its own.
   2. For example, I was trying to avoid relationship lines from traversing under entities.  Instead, I created elbows to move around entities or move the lines up to the top of entities instead of in the middle.  After reopening the dmd file, I found that all of my changes were ignored.
3. Automatic generation of indexes for both Primary, Unique and Foreign Keys does not occur when transitioning from Logical to Relational.  Would have expected the tool to properly populate these options in the model.
   1. Within the logical model, I specified both Primary and Unique constraints, as well as several, foreign key constraints.  When engineering the Relational Model, I expected that the tool would "automatically" generate all corresponding indexes for both Unique and PK constraints as well as non-unique indexes for FK constraints.  What I got was I had to manually create all of these indexes within the relational model, manually checking boxes to ensure uniqueness, etc.  All other tools, when generating a logical model to physical, properly create indexes.
4. Subtype/Supertype modeling a bit clumsy.
   1. I found creating both Subtypes and Supertypes within the Logical Model to be a bit clumsy.  Creating Exclusive Relationships with an arc was a challenge initially as well.  To be honest, many of the tools on the market had issues with this feature, however, Oracle SQLDDM didn't make the process easy.  Some additional documentation within the manual is needed to clarify (1) how to create these types of relationships, (2) how to properly engineer and resolve them.
5. Trying to establish naming standards is a bit clumsy within the tool.  Would have preferred a visible option to establish all naming standards versus having to hunt for the feature.
   1. Using naming standards features was very clumsy.  It was not apparent when using the tool that the modeler needed to click on the model "first" and fully establish all naming and architectural standards.  The features within the tool implied that naming standards could evolve as the tool is being used and would retrofit existing material to conform to the standard.  This did not occur.  My experience with other tools has been that you need to establish standards up front.  The tool will retrofit existing models to the standard.
   2. I did notice that once a naming standard was established, all other changes to that model would follow the standard.  I guess that's ok but would prefer a more top-down approach forced upon the user.
6. For M:M relationships, attributes for the intersection entity can be created as part of the relationship.  However, if you have fields that can be copied in such as audit fields, the tool does not allow for this option.  Must create the fields manually.
   1. In most of the entities, I have inserted several auditing attributes.  When creating M:M relationships, I did notice that you can add fields for the associative entity, however, you could not copy them from another entity via the tree.  Yet, you can copy attributes between entities (and tables).
   2. Not sure if this is a bug or a desired feature, but this should be available to save modeling time.
   3. Most tools that have a tree-design structure allow for this feature.
7. Specification of security features should be more spreadsheet-like, whereby values can be copied/pasted versus individual identification.
   1. Adding security features within the relational model was very painful.  No copying allowed.  This should be driven like a spreadsheet with copy/paste functionality.
8. For the Logical Model, it would be helpful to see all Primary and Foreign Keys within the diagram versus having to open the entity properties.  All other tools work this way.
   1. It was quite challenging to look at the logical model without ALL of the attributes present.  I believe this is a bug.
9. When opening up the file for the data model, these errors were immediately received.  Here’s the problem.  The tool, on its own, deleted all of these indexes from the model WITHOUT giving any reason for the error and WITHOUT prior notification.  If the tool cannot set the name as defined, then the tool should have provided better information, including errors with a comprehensive explanation.

   1. Index HOLE_YARDAGE_FK1v1 in table HOLE_YARDAGE cannot be set to name HOLE_YARDAGE_FK1

   2. Index HOLE_YARDAGE_FK2v1 in table HOLE_YARDAGE cannot be set to name HOLE_YARDAGE_FK2

10.  In generating the DDL for this data model, it is readily apparent that Oracle does not generate DDL that follows either ANSI Syntax or Oracle’s version of SQL DDL Syntax.  For example, CREATE TABLEs do not generate the primary key using the PRIMARY KEY Constraint.  Also, the USING INDEX feature is not even used.  TABLESPACE specification is completely missing.

I have some more but Oracle.com keeps freezing up.  If anyone has commentary on this, it would be helpful.  Email is Stephen.Karniotis@Lochbridge.com