How to build a circuit in workbench. From model to physical database in MySQL WorkBench. Creating and editing a data model

All webmasters have to delve into programming and database creation technologies after developing their resources.

Gradually you begin to delve into every detail, but training material is not always easy to find. Plus, not everyone knows about the existence useful programs.

How to create a MySQL database? You can create tables manually and establish relationships between them, but this is not so convenient.

It was created especially for this free program Workbench. With its help you can visually create MySQL databases. Modeling them using software is easier, more convenient and much faster.

Workbench will help you create a MySQL database

With one simple tool, you no longer have to describe the structure. The program generates the code automatically. Download the utility from this site, it is suitable for any operating system.

After normal installation of the program, to create a MySQL database you need to do the following:

1. First you need to open new model, this is done through the menu or the key combination Ctrl+N:




2. The first step when creating a database is to add a table, so we select the appropriate function:




3. Next, the table is filled in. Specify the name and attributes, keep in mind that one of the attributes is the main key, marked with a checkbox. Think in advance about how the tables will then be connected to each other:




4. After filling in the required data, create a diagram to identify relationships between subjects:



5. You will see a table in the work area. For convenience, you can expand the table structures:




6. Now you need to set connections between elements; this is done with a special tool on the work panel:
7. As a result, you should have tables presented in the workspace and relationships established between them:




8. Double-clicking on a connection opens a window in which additional parameters are set:

A web developer grows with the projects he creates and develops. As projects grow, the complexity of the software increases, the amount of data it processes inevitably increases, as well as data schema complexity. Communication with other web developers shows that MySQL databases are very popular among us, and to manage them - the well-known PHPMyAdmin. Moving from small projects to large ones, from cms to frameworks, many, like me, remain faithful to MySQL. However, to design a complex database with a large number of tables and relationships, the capabilities of PHPMyAdmin are sorely lacking. So I decided to write a review MySQL Workbench is a great free desktop program for working with MySQL.

In the first part of the review, I will talk about the very basics of working with the program, so you can use this article as beginner's guide. The second part will be devoted to using Workbench in combat when working with a remote server. In it I will give the basic instructions and recommendations for setting up a server connection and synchronization with it.

MySQL Workbench- a tool for visual database design that integrates design, modeling, creation and operation of a database into a single seamless environment for the MySQL database system.

I must say that the program is really great. It allows you to throw quickly and with pleasure project data schemas, design entities and connections between them, painlessly implement changes into the scheme and just as quickly and painlessly synchronize it with a remote server. A graphics editor EER diagrams, reminiscent of funny cockroaches, allows you to see the overall picture of the data model and enjoy its lightness and elegance :) After the first try, this tool becomes an indispensable assistant in the combat arsenal of a web programmer.

Download MySQL Workbench

The MySQL Workbench distribution is available on this page. The latest version of the program at the time of writing is Version 6.1. Before downloading, you must select one of the following platforms:

• Microsoft Windows (available MSI Installer and ZIP archive)
• Ubuntu Linux
• Fedora
• Red Hat Enterprise Linux / Oracle Linux
• Mac OS X

After choosing a platform, you are prompted to register or log in to Oracle. If you don't want to, there's a link below. "No thanks, just start my download"- click on it ;)

Beginning of work

The program's start screen reflects the main areas of its functionality - designing database models and their administration:

At the top of the screen there is a list of connections to MySQL servers of your projects, and a list of the latest open data models is at the bottom of the screen. Work usually starts with creating a data schema or loading an existing structure into MySQL Workbench. Let's get to work!

Creating and editing a data model

To add a model, click the plus sign next to the "Models" heading or select "File → New Model" (Ctrl + N):

On this screen, enter the database name, select the default encoding and, if necessary, fill in the comment field. You can start creating tables.

Adding and editing a table

The list of project databases and the list of tables within the database will be located in the tab "Physical Schemes". To create a table, double click on "+Add Table":

A convenient interface will open for editing the list of fields and their properties. Here we can set the field name, data type, and also set various attributes for the fields: assign field primary key (PK), mark it Not Null (NN), binary (BIN), unique (UQ) and others, set for field auto-increment (AI) And default value.

Index management

You can add, delete and edit table indexes in the tab "Indexes" table management interface:

Enter the name of the index, select its type, then check the list of fields participating in this index in the required order. The order of the fields will correspond to the order in which the checkboxes were checked. In this example I added unique index to the field username.

Relationships between tables

Setting foreign keys and linking tables is only possible for tables InnoDB(this storage system is selected by default). To manage relationships, each table has a tab "Foreign Keys":

To add a connection, open the tab "Foreign Keys" child table, enter the name of the foreign key and select parent table. Further in the middle part of the tab in the column Column select the key field from the child table, and in the column Referenced Column- the corresponding field from the parent table (field types must match). When creating foreign keys corresponding indexes are automatically created in the child table.

In chapter "Foreign Key Options" configure the behavior of the foreign key when the corresponding field changes (ON UPDATE) and removal (ON DELETE) parent record:

• RESTRICT- throw an error when changing/deleting a parent record
• CASCADE- update foreign key when parent record changes, delete child record when parent is deleted
• SET NULL- set the foreign key value NULL when changing/deleting parent (not acceptable for fields that have the flag set NOT NULL!)
• NO ACTION- do nothing, but in fact the effect is similar to RESTRICT

In the example above, I added to the child table UserProfile foreign key to link to parent table User. When editing a field userId and deleting positions from the table User similar changes will be made automatically also occur with related records from the table UserProfile.

When creating a project, you often need to add startup data to the database. These could be root categories, administrative users, etc. In MySQL Workbench table management there is a tab for this "Inserts":

As can be seen from the example, if some MySQL function needs to be applied to the data before writing to the database, this is done using the syntax \func functionName("data"), For example, \func md5("password").

Creating an EER diagram (entity-relationship diagram)

To present the data schema, entities and their relationships in graphical form, MySQL Workbench has an EER diagram editor. To create a diagram at the top of the database management screen, double-click on the icon "+Add Diagram":

In its interface you can create and edit tables, add connections between them various types. To add a table that already exists in the diagram to the diagram, simply drag it from the panel "Catalog Tree".

To export a data schema to graphic file select "File → Export" and then one of the options (PNG, SVG, PDF, PostScript File).

Importing an existing data schema (from SQL dump)

If we already have a data schema, it can be easily imported into MySQL Workbench for further work. To import a model from an SQL file, select "File → Import → Reverse Engineer MySQL Create Script...", then select the required SQL file and click "Execute >"

MySQL Workbench also provides import and synchronization of the data model directly with a remote server. To do this you will need to create connection remote access to MySQL, which I will talk about in the continuation of this review.

The demo project from the article is available for download at this link. I wish you success and beautiful cockroach schemes!

Whatever the database developer is: a beginner (especially) or a bearded professional, it is always easier and more visual for him to present what he is working on and developing. Personally, I consider myself to be in the first category and to understand the material I would like to see visually what I am designing/developing.

Today there are various programs and tools that cope with a similar task: some are better, some are worse. But today I would like to talk a little about MySQL WorkBench - a visual database design tool that integrates database design, modeling, creation and operation into a single seamless environment for the MySQL database system, which is the successor to DBDesigner 4 from FabForce.(c) Wikipedia. MySQL WorkBench is distributed in two flavors: OSS - Community Edition(distributed under LGPL license) and S.E. - Standard Edition- the version for which the developers ask for money. But I think that for many it will be enough O.S.S. version (especially for beginners and those who do not want or consider it inappropriate to pay for software, as well as supporters of open source programs), Moreover, the OSS version has rich functionality.

So, as the name suggests, this tool is designed to work with MySQL databases, and supports a large number of different types of MySQL models (see screenshot below) and will become an indispensable tool for better understanding and learning relational databases (in particular MySQL) for beginners:

Thus, any MySQL developer will find what he needs. Besides MySQL WorkBench allows you to connect an existing database, perform SQL queries and SQL scripts, edit and manage database objects. But for those who are just starting to master relational databases, the most interesting, in my opinion, is the ability to create EER models Database. In other words, this is a visual representation of all the relationships between the tables of your database, which, if necessary, can easily be presented in the form of an SQL script, edited or created a new view. But more on that a little later. First, let's see what the main eye looks like MySQL WorkBench(5.2.33 rev 7508):
In order to create an EER model of your database, select " Create New EER Model" As a result, we will have a tab in which we can add/create charts, tables, views, procedures; set various access rights for users; create a model using SQL scripts. This tab looks like this:
We will not consider the process of creating tables and databases, because everything is simple here. I will give only the final version of the finished model (see the screenshots below). Moreover, if you hover the cursor over the connection line (dashed line) of the tables, then the “relationship”, the primary key, as well as the foreign key will be highlighted in a different color. If you hover the cursor over a table, the table itself will be highlighted, as well as all the relationships belonging to the selected table.

In order to edit a table, just right-click on the table we need and select " Edit Table... ". As a result, an additional table editing area will appear at the bottom of the window, in which you can change the table name, columns, foreign keys and much more. In order to export a table to a SQL script, just right-click on the table we need and choose " Copy SQL to Clipboard", and then paste from the clipboard into the desired location/program/file.

And now directly about installation MySQL WorkBench. Naturally, first you need to download MySQL WorkBench. To do this, go to the MySQL WorkBench download page, at the bottom of the page in the drop-down list, select the one we need operating system. As a result, we will be offered several download options:

• for OS Windows you can download the MSI installer, zip archive of the program, as well as an archive with source code. For this OS MySQL WorkBench can only be downloaded for the 32-bit version of Windows;
• for users Ubuntu the choice is a little richer than for Windows OS users - we are offered to download MySQL WorkBench for Ubuntu versions 10.04, 10.10 (at the time of writing) and 32- or 64-bit versions of deb packages;
• For rpm-based distributions, and in this case these are Fedora, Suse Linux and RedHat/Oracle Linux, MySQL WorkBench assemblies for 32- and 64-bit OS are presented;
• Macintosh users have not forgotten either - for them there is an assembly only for the 32-bit OS;
• Well, of course you can download source programs;

So, select the required download option and click DownLoad. Then we will be kindly asked to introduce ourselves: for registered users - enter your login and password, for newcomers - register. If you do not want to introduce yourself, then select the option just below " "No thanks, just take me to the downloads!" and select the nearest mirror for downloading. In addition, before installation, make sure that you have installed MySQL Client,.otherwise MySQL WorkBench will refuse to install.

What Linux users need to remember and know:

Naturally, as in the case of Windows OS, we do not forget about MySQL Client. For Ubuntu users, you need to download the version of the program in accordance with the version of your Ubuntu. During installation, carefully look at the error messages, if any, which will probably tell you which packages are missing in your OS. Read about this below.

Unfortunately, I don’t know how things are with rmp-base distributions, because... I’ve never used such distributions, but I think it’s about the same as with debian-based ones.

You may have noticed that the assembly is missing MySQL WorkBench for OS Debian GNU/Linux. But, as practice has shown, it’s okay. For installation MySQL WorkBench in Debian 6.0 (Squeeze) we will use deb- package for Ubuntu 10.04(don’t forget about the bit depth of your OS: x86 or x64). Let me remind you that in order to install the downloaded deb package you can use the utility gdebi or enter the command in the console as root:

# dpkg -i mysql-workbench-gpl-5.2.33b-1ubu1004-amd64.deb For example, I got the following error while installing MySQL WorkBench:
dpkg: package dependencies prevent the mysql-workbench-gpl package from being configured:
mysql-workbench-gpl depends on libcairomm-1.0-1 (>= 1.6.4), however:
The libcairomm-1.0-1 package is not installed.
mysql-workbench-gpl depends on libctemplate0, however:
The libctemplate0 package is not installed.
mysql-workbench-gpl depends on libgtkmm-2.4-1c2a (>= 1:2.20.0), however:
The libgtkmm-2.4-1c2a package is not installed.
mysql-workbench-gpl depends on libpangomm-1.4-1 (>= 2.26.0), however:
The libpangomm-1.4-1 package is not installed.
mysql-workbench-gpl depends on libzip1 (>= 0.9), however:
The libzip1 package is not installed.
mysql-workbench-gpl depends on python-paramiko, however:
The python-paramiko package is not installed.
mysql-workbench-gpl depends on python-pysqlite2, however:
The python-pysqlite2 package is not installed.
dpkg: failed to process option mysql-workbench-gpl (--install):
dependency problems - leave unconfigured
Errors occurred while processing the following packages:
mysql-workbench-gpl

To resolve this error, all I had to do was type the command in the console to install some packages:

# aptitude install libzip1 libcairomm-1.0-dev libctemplate0 libgtkmm-2.4-1c2a

To install the above packages, you will need additional packages, which the manager apt will kindly offer to download. After installing all the necessary packages, MySQL WorkBench installs without problems.

That's it: MySQL WorkBench is safely installed and ready to learn.

upd:
If I'm not mistaken, then starting from Ubuntu 12.04 MySQL WorkBench can be found in the distribution repositories. Consequently, the installation process is much easier and without any crutches.
To install MySQL WorkBench, just enter the command in the terminal:
sudo aptitude install mysql-workbench

Attention, since WorkBench was updated, I wrote, which consists of the theory and practice of building a database from WorkBench.

In chapter “Foreign Key Options” configure the behavior of the foreign key when the corresponding field changes (ON UPDATE) and removal (ON DELETE) parent record:

• RESTRICT– throw an error when changing/deleting a parent record
• CASCADE– update foreign key when parent record changes, delete child record when parent is deleted
• SET NULL– set the foreign key value NULL when changing/deleting parent (not acceptable for fields that have the flag set NOT NULL!)
• NO ACTION– do nothing, but in fact the effect is similar to RESTRICT

Saving from model to real/physical database

“File → Export→ Forward Engineer MySQL Create Script...”

We tick the necessary boxes, I only needed one Generate INSERT Statements for Tables. If you need to save the script to a file, write the directory in the field above.

In the next window you can configure which objects we will export. If you look closely, we have created only 2 tables.

Executing the script - creating a database and tables

Click on “house” in the upper left corner of the program...

Then double-click on MyConnection….

This tab opens before us...

This is our connection to the server, this is where we will execute our script. Please note on the left are the databases that were created in the WorkBench program….

Now, you need to give the command to execute this script, to do this, click in the top menu, Query Execute (All or Selection)

So, if everything is fine, then in the lower output window you will see all the “green checkmarks”. And when you click Refresh in context menu in the list of databases, you will see the newly created database mydatabase1.

Finally, let's build an ER diagram. ER stands for Entity Relation - a successful “Entity-Relationship” model, which, in particular, was developed by Peter Chen. So, go back to the model tab and click on Add Diagramm...

We have created a one-to-many relationship. Several students can study at one faculty. Please note that the relationship near the Students table is split - this means “to many”.

So, we created a model, from which, through script execution, a real database with tables. And also created an ER diagram.