ProductsDatabaseSpyGraphical Database Design

Graphically display structures and relationships of database tables and design new tables without hand-writing SQL statements.

The Altova DatabaseSpy® 2008 Database Design Editor lets you view and edit the structures of all your databases through one graphical user interface. You can examine tables and relationships in an existing database to understand them more easily, you can edit the database to better suit your needs, or you can add entire tables and specify all their column attributes and relationships to other tables from scratch.

The DatabaseSpy® 2008 Database Design Editor graphical view allows you to concentrate on the underlying structure of your data and any required modifications rather than the SQL commands required to implement them. As you work, the DatabaseSpy® 2008 Database Design Editor automatically constructs the SQL statements you will need, but you choose when to execute the database structure change script once all your edits are complete.

You can view the structure of any existing table in your database by dragging it from the database browser into the Design Editor pane or by selecting it and choosing Show in Design Editor from the context menu. The table and column details are immediately displayed in the Design Editor’s graphical view.

The graphical image of each table includes compartments you can expand to view in greater detail or collapse so you can concentrate on table relationships. You can also drag the edges and corners of any table to resize it, or click on the handy autosize button to automatically choose the best fit based on the text contained in the table columns.

You can drag tables from the Online Browser into the Database Design Editor to add them to your diagram. Or, use the convenient toolbar buttons to easily identify related tables by automatically adding them to the editing window, Either way, relationships are clearly indicated, and you can expand the constraint panels for a more detailed display.

Click image to enlarge / reduce database tool screenshot

You can click the connecting line between any two related tables to highlight the columns in each table that define the constraint.

Click image to enlarge / reduce database tool screenshot

Layout options in the Database Design Editor’s toolbar let you zoom or shrink the layout to fit your window, and a special helper button lets you conveniently arrange tables by hierarchy.

You can print the contents of the design window at any time, attach sticky notes to annotate your design, or save your diagram as a .png image file to document it in another application.

The DatabaseSpy® 2008 Database Design Editor offers several options to edit column names and attributes. You can edit directly in the graphical table display, adding a new column, index, key, or constraint by clicking the plus sign icons, or add a column, index, key, or constraint via the right-click context menu. You can edit existing entries directly in the design window, or in the properties helper window.

You can edit the properties of the new column in the properties helper window, or you can click the expand button for any column to open its properties as an extension to the table display within in the design pane. The properties extension shows all the details of the column attributes and offers a pull-down menu of data types.

Click image to enlarge / reduce database tool screenshot

As you work in the Database Design Editor, DatabaseSpy® 2008 automatically creates and updates the SQL statements required to implement your changes. However, because intermediate steps in the revision process could have undesirable or even destructive consequences, DatabaseSpy® 2008 will never automatically execute the database change script.

For instance, when you add a column to an existing table, the initial SQL statement in the database structure change script is created with placeholders for the column name and datatype that you will immediately revise.

You can review the database structure change script any time you like. You can edit the SQL statements directly in the change script window if you wish, paste the SQL statements into a new DatabaseSpy® 2008 SQL Editor window, or save the script to a file. The statements in the database structure change script window will not be executed until you click the execute change script button.

The DatabaseSpy® 2008 Design Editor is a convenient tool for creating completely new tables with multiple columns, keys, and indexes. As you build your new design, plus sign icons indicate changes you’ve made that await execution in the database structure change script.

Click image to enlarge / reduce database tool screenshot

Once you have created the new table with its keys and indexes, you can even define relationships to other tables, based on foreign keys.

When all the modifications to your database structure are complete and you have executed the database change script, the up-to-date view of the table and its relationships provides graphical documentation of your new columns, tables, and relationships.

Click image to enlarge / reduce database tool screenshot

Relationship connectors are color-coded to identify complex relationships when all the tables will not fit within the design pane. When you select any single table:

• Connections from referencing tables are displayed in blue.
• Connections to referenced tables are displayed in orange.
• Self-references (not illustrated in the screenshot below) are displayed in green.

The Overview helper window provides both a reduced view of the selected and related tables and a convenient navigation tool.

Find out for yourself how the Database Design Editor in Altova DatabaseSpy® 2008 can help you analyze, and modify, and design the structures of your own databases. Click here to download your free 30-day evaluation today!