MySQL Workbench is a great tool when someone asks you to look at their schemas. It is hard to get the over-all view of data that you are not familiar with and this is a great aid for this situations. You an reverse engineer the database, make changes, and then roll them out but this blog is about creating the model only. The examples you see in this blog were made with MySQL Workbench 6.12, which is in beta. Beta means we want to you test the heck out of it and let us know how you broke it so we can make it better.
Click on the circled greater than sign next to model to begin.
Click on the circled greater than sign next to Models
to begin. We want the Create EER model from database
We want to create a model using an existing database
Next we set up the connection to the server
Next we set up the connection to the server. Workbench will make the connection to the server and fetch a list of schemas. The schemas you will see depend on your privileges and I am willing to bet 99% of t hose reading this will be root
Pick the schema to use for the model
Here we will use the good ol’ World
database that has been used in MySQL documentation, classes, and examples ad nauseum
. The World schema bundled with the Windows install is the MyISAM version and I recommend loading the InnoDB version (Download at the link above, save to your disk, unzip data, and use Workbench to read the SQL file in a Query window) or you will not get the to see in person the results seen in the next picture (there are DROP TABLE IF EXISTS lines for the table creation so you do not have to drop the tables by hand).
And here are the three tables in the World Database, note the foreign keys
And there is the model! You can drag and drop the various tables to arrange them to your liking. Note that mousing over the foreign key links shows the relationship between tables. Clicking on a column name will tell you the definition of the column. There is even an index arrow to click to show the carious indexes. Pretty slick for a free piece of software! Click om the table name and you see the columns and the foreign keys.
So if you have never seen a schema before, you now have a wonderful (printable) diagram of the tables and their interrelations. My learning style is osmosis (or banging it through my thick skull) and having a graphical representation to reference helps me greatly.
The Sakila Schema
Things can get visually busy as you can see with the nearby representation of the Sakila database but Workbench lets you zoom-in, zoom-out and see sections as you desire.
Don’t like the ‘chicken foot’ or need UML, you can change the model relationship model from a pull down. There are enough options here of value that you really do need an afternoon to explore them all.
So please download MySQL Workbench 6.1.2 (and MySQL 5.7 while you are at it) and give it a try. Our developers await you feedback!