Monday, July 8, 2013

ER Diagrams in SQL Anywhere 16

Tip: The "ER Diagram" feature of Sybase Central is all growed up and well behaved in SQL Anywhere 16.

I'm guilty of not trying it out for a long time, but today it seems to be actually useful:

  • The right-mouse Choose ER Diagram Tables... context menu item makes it easy to pick the tables you want to display.

  • It's easy to click and drag the tables to move them around,

  • and the lines too, but with the lines you first have to click on a line to display the teeny tiny endpoint boxes, then click and drag those endpoints to move the line:
  • You can resize the table images to show more columns by first clicking on the title bar to display the resizing icons.

  • The View - Refresh Folder menu item lets you clear highlighting after clicking on columns.

  • Best of all... your careful work moving tables and lines around isn't wiped out when you refresh the display.

  • Better yet (better than best?) Sybase Central remembers the layout when you disconnect, connect, and redisplay the ER Diagram tab.

  • Better still: right-mouse Export Layout... (new with SQL Anywhere 16) lets you save the layout in a funky XML text file (just the layout, not the columns and and what-not),

  • and then you can revert to that layout with right-mouse Import Layout... after, for example, you completely [cough] muck it up :)

  • The right-mouse Go To Table and Go To Foreign Key menu items take you to the relevant tabs in Sybase Central, and from there the Back button brings you back to the diagram.
Here's a before-and-after example of an ER diagram
  • as originally displayed for the SQL Anywhere 16 Demo database (the first image) and

  • after some fiddling about (the second image):

Is it an alternative to PowerDesigner?

Yes, it is, for 99% of what people actually need: Pretty pictures for management (90%), and diagrams for documentation (9%).

The remaining 1% of what folks need, which accounts for 99.999% of PowerDesigner's feature set (and complexity, and cost) is beyond the scope of the Sybase Central ER Diagram feature. 2000-12-16


Evan said...

Well I got a question for you. You have well explained this tutorial, but you have not mentioned any er diagram tool to draw er tools. I know creately, do you use any platform independent diagramming tools?

Breck Carter said...

I have used many bloated CASE tools over the years (DesignAid, IEW, IEF, ERwin, PowerDesigner) and finally decided that diagrams do not scale well when dealing with large databases... it is too hard for developers to find the information they need. Text is better, where you can see at a glance what the parent and child tables are, and jump up and down in the relationship hierarchy with a single click. So, I now use these tools: Wordpad to enter CREATE TABLE statements, ISQL to compile the CREATE TABLE statements, and Foxhound's Schema Display to see what I've got.

Stuff said...

It's still awful. Even in Sybase Central 16 I can't do the most basic things like multi-select tables in the ER diagram, let alone auto-arrange in some way which is aesthetic.

I can't zoom in or out to see the whole diagram, I cannot select a table and drag it further up the diagram, but can only move it around the visible screen area.

It's an abysmal, and largely useless, ER diagram tool.

Breck Carter said...

You are correct. My article was too kind. The ER Diagram tool in SQL Anywhere does not do any of the things you mention, nor does is provide better gas mileage, world peace or a solution to global warming.

Seriously, you ARE correct. The real problem is, I should not be offering ANY opinion on ANY diagramming tool. Diagramming tools do not scale well which is why I do not use any of them. I use the Foxhound text-oriented schema display for existing client databases (which often have thousands of tables), and I use wordpad (!) and dbisql to develop database objects.

I also don't use ER modelling, or OOP, or Facebook, and I don't watch "Girls", so you have permission to ignore everything I say :)