(No Ratings Yet)
Loading ...The history
In December I’ve got a project, that required sophisticated and quite complex DB and that DB was subject to change greatly during first development phase. Before that project I was using Sparx Enterprise Architect both for application design and DB design. But, frankly speaking, it’s not good at databases, especially MySQL. So I decided to pick up a new DB modelling tool.
Requirements
My requirements to it were:
- Convenient entering of the attributes and their types. When I have an idea, I want it to be recorded fast, I don’t want to spend time clicking different “add” and “save” buttons in order to enter table attributes
- Full MySQL features (including all types of indexes) and main storage engines support (MyISAM, InnoDB, MEMORY)
- Visual tables layout like in MS Access
- Maintaining table joins and involved indexes, visual creation of table connections
- SQL export
- Free!
On the way to perfection…
I went through this list and installed and tried around 10 different tools and I didn’t like any of them. One has no visual modelling, other has inconvenient input, third has both, but doesn’t support a number of MySQL features. So finally I ended up with MySQL Workbench. It gives me all I need and even more.
Yes! It’s really perfect 
Here you may find a list of features together with versions comparison. You can read it yourself and I want to describe why I liked it:
Nice design
Yes, it matters. When you use Windows95-like program, you can’t feel as good as if you’re using excellent-designed one. Here is a screen:
Model Overview
Creating DB
I liked their “schema approach”. When you create DB, you set the schema default encoding and all the tables in DB are automatically created in this encoding. Sure, you can change it for each table, but this is very helpful. I’ve also set up mydefault storage engine – InnoDB (MyISAM is default). This setting can be found in a seconds; everything is in it’s place (Tools -> Options -> MySQL).
So now I can create my table without remembering that I should change some settings each time I create new table. That’s my first requirement.
Creating tables
I liked their template approach to the generic field names. Near every table has ID field. In my name conventions (since I’m using Yii framework, I follow their), ID field name is [tableName]ID. So I go to the Tools -> Options -> Model and set there:
- Primary key pattern – %table%ID
- Primary key default type – INT(11)
- Foreign key defaults (since I’m using InnoDB, which takes care of data integrity, I set this. I’ll tell more about this in my future posts about MySQL&InnoDB)
Three little settings, but it makes my life much more simple! Actually, you can set up the naming of the auto-generated index fields, but I don’t use it, I create all tables I need myself. However, Workbench can create cross table for Many-to-Many relation for you.
Now I create the table (collation and engine are set automatically), go to the “fields” tab (tabs are in the bottom – I had problems finding them first time) and have my ID field created. Unfortunately, I can’t say that I want first letter in lower-case, so I correct it manually. Then start adding fields. It is very simple. First you double click on the empty row where should one name of the field, enter your name, press TAB, enter the field type (I usually enter 1-3 letters and press “Down” key, it selects correct type), press TAB again and you’re on the next field! So you just keep populating your attributes really fast! Then just tick NN (not null) and AI (auto increment) checkboxes and your table scheme is done. All indexes are set up on the next tab – Indexes except PRIMARY KEY. ID field is PRIMARY KEY by default. To add more fields there, select field and tick PRIMARY KEY checkbox in the right part of the tab.
Visual layout
Consider we created tables we need, now we advance to the scheme and connections. Double click “Add Diagram” in the top of the screen and you’re there. In the centre of the right pane you have tables you created. If you drag table from that list to your diagram, you’ll see big dot near it’s name. That’s also very convenient when you have a number of tables. Sure, here you can snap to grid, show/hide it, but that’s not so impressive.
Visual grouping
And this feature is mega-great! When you have huge DB, you can group tables using coloured layers:
ER Diagram with coloured layers for grouping
Visual connections
Yes, it allows you to connect tables like you want. 1-1,1-N and N-M relations. If you prefer to create foreign keys automatically, yo can use these types of joins. I prefer to create all tables and fields and then connect it. So I click on the last icon in the left toolbar, select foreign key, then click “Pick referenced columns” and finally select the primary key I link to. I’m done. All InnoDB connections are automatically created.
Creating relationship using existing columns
SQL export
Sure, SQL export is not new or impressive feature, but MySQL Workbench allows you not only to create SQL for the scheme, but also to create ALTER DB script for the CREATE DB script already created. For example, you designed a great DB, exported to SQL query, installed on server and then your client wants changes. You open MySQL Workbench project, make necessary changes and then create ALTER script. Very convenient thing
Integration with Yii
One clever guy (unikly) created the Yii shell command, that allows to use mwb file instead of DB connection when creating Yii DB models. This command is available on the Yii extensions page. As for me, it’s a very big plus.
A fly in the ointment
I’ve noticed several bad things in this software:
- When DB becomes huge, you need a good machine to work with it comfortably. It takes too much resources
- Some interface elements are not evident (In the DB overview screen lowest part has tabs both on the top and on the bottom. Top tabs – switch between tables, bottom tabs – switch between table’s sections (properties, fields, indexes)
- When creating joins, it doesn’t maintain the uniqueness of foreign indexes names. Take a close look at the export script (and watch for constraints and auto-created foreign keys) when it fails in phpMyAdmin (or other tool)
Disclaimer: Screenshots are taken from MySQL Workbench site, I was lazy to take my own ones.
No related posts.
Related posts brought to you by Yet Another Related Posts Plugin.
Share this post with a friend
Will says:
Great post, totally agree!!!
March 23, 2009, 19:58One more reason to choose Yii!
Konstantin Mirin says:
Yeah, I like that program. Very handy to use and it’s import-export features are really excellent.
March 24, 2009, 10:40I’m going to try that in the nearest time and will publish my results
Ing.espitaleta says:
I have a question, is this dabase designer better than dbdesigner 4.0 or fork from this one???, I mean I would like to try this new one, but I want to be sure by should I use this one!!
May 12, 2009, 05:09Best regards!
Konstantin Mirin says:
Here, on the download page: http://fabforce.net/downloads.php you can see the latest release is mysql workbench. And description states: “DBDesigner4 successor, recommended for all DBD4 users”. So I think it should be better. However, I think, that MySQL development team made some features available only in commercial version. If you try it, could you write your feedback on this?
May 12, 2009, 08:45pestaa says:
I also use Workbench daily, however, it is terribly slow.
August 21, 2009, 11:23Konstantin Mirin says:
Yes, I wish it worked faster. However it is very handy and it suits me. By the way, not only this app is slow on my pc, so maybe we should update hardware?
August 21, 2009, 13:48Database Designer says:
Very nice Article – Is it OK if i place a Link on my Domain http://www.dbdesigner4.de/ to this?
October 6, 2009, 20:21Konstantin Mirin says:
Sure! Feel free to link to my posts, just don’t copy them without permission
October 8, 2009, 14:49pintu says:
Nice 1!……..
Keep Rocking…
November 5, 2009, 12:46Elif says:
Well.. My two cents: The features listed on page http://www.mysql.com/products/workbench/features.html under the Community Edition are misleading. I can’t find any explanation why they can not update their website, if they shifted some of that functionality to the commercial version. In my opinion, it is mis-advertising..
December 21, 2009, 22:50Osvaldo Nevin says:
To start earning money with your blog, initially use Google Adsense but gradually as your traffic increases, keep adding more and more money making programs to your site.
February 24, 2010, 08:06DawnBlake20 says:
Do not enough cash to buy a house? Worry not, just because that’s achievable to take the http://www.lowest-rate-loans.com to resolve all the problems. Thence get a car loan to buy all you need.
May 26, 2010, 01:53Dominating Google says:
The Article Directory Guide…
to sign up for and permit you to supply a full about me profile which includes picture along with description…….
May 28, 2010, 23:45Epic Traffic Systems says:
What Is The Point Of Article Directories…
sizeable archives of written content covering just about every area …….
May 30, 2010, 16:12Yahoo News says:
Yahoo News…
I saw this really great post today….
June 1, 2010, 15:42The Advantages of USB Floppy says:
…
Your point is valueble for me. Thanks. Could you please take a look at the suitable Usb floppy driver info！…
June 2, 2010, 15:29Yahoo News says:
Yahoo News…
This is really great news today….
June 2, 2010, 22:46Yahoo News says:
Yahoo News…
This is really good news today….
June 3, 2010, 04:00How Welding Hoods Creat a safe working environment | Best and Free ... says:
As the boat made of plastic weld on the grass | Best and Free tips ……
HOW PLASTIC WELD Boat on the grass ? With MINI-WELD MODEL 6 plastic WELDER I was not able to find the price of this article. It costs about $ 15 to fix….
June 17, 2010, 13:57yardworks lawn mower manual says:
…
Extremely inspiring post, Thanks !?!…
June 17, 2010, 18:44Free Movie Online says:
Yahoo Movie…
This is really great news today….
June 21, 2010, 20:45No credit auto free personal loan agreement says:
No credit auto free personal loan agreement…
This is my blog about auto loan agreement…
June 24, 2010, 15:19mens wedding bands with diamonds says:
…
I saw this truly excellent post these days!…
June 24, 2010, 20:28i love vhs shopping meter film says:
mobile and telecom software telecommunications network…
the telecommunications network can find telecom software,mobile telecom,solutions telecommunications,Get your toll free numbers,800 numbers,virtual business phone system…
June 27, 2010, 16:44ecommerce says:
Really nice read thanks, I have added this to my Mixx bookmarks.
June 30, 2010, 00:18Yahoo Healthy News says:
Great {article|post}…
This the best article I have never seen before….
July 9, 2010, 17:14Quick and best ways to lose weight safely and naturally online says:
Quick and best ways to lose weight safely and naturally online…
Find the best ways to lose weight,Like laxatives,dieting,Make you quick and easy ways to lose weight safely and naturally….
July 17, 2010, 01:30锘縜utorepair says:
Excellent stuff….
I think you know how to write a genuinely nice post. Thanks!…
July 19, 2010, 16:43homeloan says:
Its Definitely good bookmarking for future reference….
Thanks for sharing this pretty post….
July 21, 2010, 03:56headache says:
your blog’s design is simple and clean….
Mind if I use some of the information from this post if I provide a link back to your site?…
July 25, 2010, 10:44mobile and telecom software telecommunications network says:
mobile and telecom software telecommunications network…
the telecommunications network can find telecom software,mobile telecom,solutions telecommunications,Get your toll free numbers,800 numbers,virtual business phone system…
July 25, 2010, 19:28Coach bag outlet says:
…
This is a great blog submit and I defer to you what you might have mentioned the following. I have previously subscribed to your RSS feed in Firefox and are going to be your typical reader. Thanks for the time in writing the submit….
July 27, 2010, 18:06Discount coach handbags says:
…
How’s it heading? I enjoyed reading this write-up. My husband and I’ve been researching for this sort of write-up with the longest time and We know that your information around the matter at hand is spot on. I’ll be positive to introduce this write-u…
July 28, 2010, 11:45forbiddencity says:
your blog’s design is simple and clean….
Mind if I use some of the information from this post if I provide a link back to your site?…
July 29, 2010, 05:0911 griddle says:
…
Quite inspiring publish, Thanks !?!…
August 14, 2010, 10:16Perego stroller says:
…
Hi, thanks so significantly for these ideas!…
August 14, 2010, 10:26Mailbox cover reviews says:
…
One from the top sites for relevant facts on this niche !?!…
August 21, 2010, 12:42Stiletto heel says:
…
Wonderful publish! I’ll subscribe perfect now wth my feedreader software package!…
August 25, 2010, 19:37Christine Wilsey says:
I have regretably gotten myself into a large amount of debt and need to make some forward progress. I would like to get a personal loan but I am not sure of the best way to go about doing that. Does anyone have any advice?
August 30, 2010, 00:15music boxes says:
Purchase mp3 music online…
This is really a Super knowledge gaining news and all thanks to google search engine get me on here. I liked reading your writing and added to the bookmarks. The points you tried to put up was clearly understandable. My hubby also appreciated after rea…
August 30, 2010, 17:24Kansas deer hunting says:
…
Really cool! I assistance your view!…
September 1, 2010, 17:55Black sisal rug says:
…
Cheers for this write-up, guys, retain up the good function….
September 3, 2010, 18:19Mens White Gold Wedding Bands says:
…
Below is definitely an area that could possibly be of support to you!…
September 5, 2010, 09:55Front wicker basket says:
…
Many thanks with the well-thought posting. I’m really at operate proper now! So I must go off without having examining all I’d like. But, I set your blog site on my RSS feed in order that I can understand additional….
September 5, 2010, 10:07