The SQLyog Schema Designer

The Schema Designer (introduced in SQLyog version 6.1) is a visual interface where tables and table structures can be defined, displayed and manipulated. The Schema Designer provides a convenient graphical way to perform common operations as an alternative to the menu-based way of operation that was always supported. Also, the Schema Designer provides a graphical overview of (complete or only parts of) your databases.

To manage the databases using Schema Designer, goto the ‘File’ menu and select the click option “New Schema Designer”,

This option can also be selected by clicking on the "+" icon in the query editor line to the right of all open tabs and select the option “New Schema Designer” or just use the keyboard shortcut ‘Ctrl+Alt+D’,

The Schema Designer implementation has two parts:

1) A Tab type displayed in the Query Editor area of the program window. This Tab contains a single element only: a 'canvas' (drawing area). You can configure SQLyog to display one such TAB when the program starts in program's 'Preferences'. If you need more such TABs you can  open more from the 'file' menu any time.

2) An XML-based data file format for storing information generated from the Schema Designer.

The Schema Designer Tab

The canvas of the Schema Designer is very similar to the canvas of the QueryBuilder and their operation is almost identical. You can move existing tables from the Object Browser into the canvas by double-clicking it or by drag'n'drop and you can move the tables around and resize the table symbols on the canvas.

For the rest of this paragraph we will refer to a Schema Designer Tab with table(s) (and what else information is supported) on it as a 'LAYOUT'.

Table operations

When a table is added to the canvas, the table symbol will include a toolbar displaying small icons that let you perform the operations:

  •  Alter Table
  •  Manage Indexes
  •  Manage Foreign Keys
  •  Change the table view to display only the column name or both the column name and data type

The same operations are available from the context menu for each table. Actually this context menu does the same as the 'table' entry in the program main menu and the context menu for a table in the Object Browser. The graphical and the menu-based way of doing things are functionally identical.

Also a context menu is available on the canvas itself for where you can add existing tables to the canvas or create a new table for the selected database. To create a table, select 'CREATE TABLE' from the canvas context menu. You will see that the CREATE TABLE dialogue will open. Just enter the parameters describing the table definition and close the CREATE TABLE dialogue as you normally would. The table will be created in the database, the Object Browser updated and a table symbol will be added on the canvas.

Also you can any time 'Refresh' the canvas from the context menu.

Concepts

You have already now seen two concepts in work that are basic design concepts of the SQLyog Schema Designer:

1) Whatever you add or change from the Schema Designer will be stored to the database exactly the same way as when you work with the menu's of the program. There is no need to 'upload', 'apply', 'synchronize' the LAYOUT or anything like that. SQLyog Schema Designer is a graphical implementation of direct client functionalities. We work with the database that we are connected to - not with any kind of 'model' internal for the program

(but note that if you want a 'model' before applying the changes to the 'real' or 'live' database you can simply use another database as a 'model' - and use Schema Synchronisation to synchronise from the 'model database' to the 'real database'. The two databases need not be on the same server).

2) Whenever an operation will need to open another dialog to get additional information from the user, you will see that the Schema Designer uses the same dialog as the rest of the program.

Zooming the canvas can be done using the dropdown or CTRL+mouse scroll button. Canvas can be saved as .bmp graphics and printed. There is an option to see how the print will split into more pages (using the settings of the current default printer). These options are in the canvas context menu.

Defining Foreign Keys ('relationships')

The image above shows a canvas with tables added. You will also see that some lines are drawn between some of the tables. In the Schema Designer such line defines a FOREIGN KEY between two TABLES in the database (whereas in the Query Builder it defines a JOIN-condition for a query RESULT). When drawing a line from one table to another the common 'MANAGE FOREIGN KEY' tab will open and 'propose' you to create the FK where the 'child' column is the column from where you draw. The 'parent' column will be proposed according to the rules:

    * If there is a Primary Key on the parent table it will be proposed.

    * If there is no Primary Key on the parent table it will propose the first Unique Key found (if any) in table definition.

That procedure would find the 'parent' column of the Foreign Key that would make sense in most situations. If you need something more special then just change in the 'MANAGE FOREIGN KEY' tab.

When a line is drawn in the canvas "1" and "infinity" symbols will display at either end of the line. This symbol indicates what kind of index exists on the columns: "1" indicates a PRIMARY KEY or a UNIQUE index - "infinity" symbol indicates a non-UNIQUE index. Also the line has a context menu from where you can drop and edit the Foreign Key.

Note: This feature is available in SQLyog Enterprise and Ultimate.