Some thoughts about EA 13 (Professional) data modeling features

[Richard Freggi]

I am thinking about submitting a feature request, but first I would like to sound it off here, since I'm still learning about EA (previous ERWin user)

1. What do you think about adding a true crow's feet notation, where keys are underlined instead of having the key icon as stereotype
1.1 Also if it's not patented, how about adding automatic highlighting of the keys when you hover mouse over a relationship... this is something that Mysqlworkbench does and I find it useful to plan queries and joins
2. You can drag attributes (non-keys) from a table (or Class) to another in Project Explorer, but not in the diagram... it would be useful to be able to move attributes around in diagram as well
2.1 Editing attributes in the diagram by double clicking is buggy too... changes are not saved
3. EA (at least Professional) cannot reverse engineer SQL DDL code... I find it useful to move models around and update schemas.  MySQLWorkbench and ERWin can do this
4. No CSV export for attributes is a major feature gap... I have read up on EA limitation to CSV export classifiers only and not their attributes, and I understand the difficulty in implementing this across all UML classifiers, but a table-attribute specific script should be made available by Sparx.  It's very valuable for quick editing-fine tuning-reporting when under time pressure (ERWin is excellent in this respect)

Any feedback or ideas?  I'll submit feature request in a few days

[Sunshine]

Always good to get some fresh thoughts.

1. What do you think about adding a true crow's feet notation, where keys are underlined instead of having the key icon as stereotype
1.1 Also if it's not patented, how about adding automatic highlighting of the keys when you hover mouse over a relationship... this is something that Mysqlworkbench does and I find it useful to plan queries and joins

1 - I like the key icon can't say the underlining would get my vote.
1.1 - Yeah may be.

2. You can drag attributes (non-keys) from a table (or Class) to another in Project Explorer, but not in the diagram... it would be useful to be able to move attributes around in diagram as well
2.1 Editing attributes in the diagram by double clicking is buggy too... changes are not saved

2. - There is a feature where you can drag from project browser to element on diagram and it copies it. Not sure if I'd like that moving attributes on the diagram. Kind of feel the UI might become a little tricky to use when trying to select element vs attribute.
2.1 - I've not had this problem but may have just gotten used to the UI over the years.

3. EA (at least Professional) cannot reverse engineer SQL DDL code... I find it useful to move models around and update schemas.  MySQLWorkbench and ERWin can do this

3- Can reverse engineer DB already in corporate edition and above. I usually reverse engineer DB, edit in Sparx EA and generated DDL to create DB.
 

4. No CSV export for attributes is a major feature gap... I have read up on EA limitation to CSV export classifiers only and not their attributes, and I understand the difficulty in implementing this across all UML classifiers, but a table-attribute specific script should be made available by Sparx.  It's very valuable for quick editing-fine tuning-reporting when under time pressure (ERWin is excellent in this respect)

4 - MMh? I struggle to understanding this one. Do you want to make global changes to attributes or run a report? Here is a little trick on global changes; the  eap files are really ms access 97 and you can open it with MS Access and do global changes that way. Using DB skills to save time in changing the DB design - kind of poetic in some way isn't it . BTW you can use the MS Access Jet 4 too for later versions of MS Access that don't support 97 anymore.  To run reports then look at doc generation. very comprehensive reporting built in already.

If you can't wait for that feature you can write some vbscript/jscript to create your cvs file.

[Richard Freggi]

Great feedback Sunshine thanks!
Yes reverse engineering DB works fine for me too - but I'm referring to reverse engineering a DDL text file (CREATE statements...).  Open the SQL file and the tables+attributes+associations+diagram are generated for you
I use CSV export of tables and attributes to go over action items for each table or attribute (check semantics with someone, validate domain, etc.) , sometimes several times a day.  I don't really do this for relationships.  I edit the schema on Project Explorer.  Html or rtf document generation is an OK workaround but not as clean and fast as having an Excel file.

Anyone else?

[Geert Bellekens]

Exporting and importing table and columns (elements and attributes) is fairly easy to implement yourself.

I made an excel with some VBA to import classes and attributes. This excel also exports tagged values, so it should be fairly simple to add a feature to export table and columns.

You can find my excel importer here:

https://bellekens.com/2015/10/24/simple-vba-excel-to-ea-importer-v4/

The import DDL issue has been discussed a few times here. The response from Sparx was every time that they are not interested in investing to develop such a feature as it would need to be different for each target SQL dialect.
If you really really want to you can create your own reverse engineering templates using some kind of BNF notation, but from what I understood that is far from easy.

Geert

[MaXyM]

As db programmer I can suggest to stay away from UML modeling tools for database modelling as long as you can.
Why? Because those tools are not dedicated this task, therefore are very limited regarding database-wise features. Instead, they implement db support deriving most features from class diagrams and adding some minimal coverage needed by db.

Specialized tools do their best to cover features and requirements for various databases including specific differences between dbms' (like index required for FK in case of mysql, which is not mandatory in other dbms), making modelling just easier and faster.

Simple example: In each db modelling tool I know, foreign key is created just by dragging and dropping primary key onto another table. Yet few years ago, in EA you had to manually: create column for FK, setup the same datatype, create relationship between 2 columns, name it, create index if needed by particular DBMS etc.
Ussually UML tools are also late regarding features released with recent database versions.

It's true, that some features finally met UML tools. But are still light years behind soft like CaseStudio (almost 20 years ago), ToadDataModeler, PowerDesigner and many more. yeah... and limits db reverse engineering to more expensive licenses... LOL.
but even with all those improvements you are going to struggle with tools like EA. Below simple example from EA13.5. Reported already, receiving nothing but note they will look at this later but it's not considered as bug.
Try to reverse engineer tables from more schemas. You are end up with all tables put in single package called tables. Unfortunately schema names will be not shown (actually are stored in tag value). So basically you lost visual information about schema - you can see all tables from various schamas mixed together.  OK... I bet you will attempt move tables into subpackages named by schemas and try to import schema by schema. But then EA is not able to reverse engineer FKs between tables which are located in other packages than mentioned Tables package. Moreover, RE tool shows the missing FKs and offers synchronize action. But cannot do that. And it is not A BUG by Sparx definition.

But of course there are cases when going for UML tool is justified. One of them is a need of mapping other part of architecture (components or even business needs/requirements) onto database objects. If you don't need that, if you want just to model database without taking care about the rest of system look for dedicated software. Otherwise you will end up struggling with operations/features which you expecting to be available but in fact there are not over here.

[Richard Freggi]

Thank you Geert and Maxym.  Yes I agree with your posts - although to be fair Sparx EA is not marketed as a data modeling tool, the ERD is a useful add-on but it will never be at the same level as dedicated data modeling tools.  Same for other EA add-ons (BPMN comes to mind), I think it's great that Sparx provides some of them for free and they are very valuable in a pinch but they were clearly not meant to replace the dedicated tools.  Another thing is that I think it's perfectly appropriate to model data and design databases in 100% UML and in this case Sparx EA would do a fine job.

I'll submit the feature recommendations (sans-DDL reverse engineering), we'll see what happens.