Designing a database? Consider MySQL Workbench

MySQL Workbench
MySQL Workbench

My love affair with MySQL has spanned over a decade.  I wondered what the repercussions of Oracle buying MySQL would be, still not sure.  There are conspiracies out there, like this article which helps fuel the fire of skepticism.  It’s a little scary because MySQL was supposed to be the open-source solution to the corporate, Microsoft-style domination of administrating, managing, housing, and operating essential databases to keep little companies and their websites competitive and not be beholden to the corporate behemoths and their licensing and restrictions.  After all, a database is nothing more than a collection of a series of records that in their simplest forms are nothing but lines of data one could type and duplicate in a simple text editor, right?  It’s what the engine and software can do to access, manipulate, and assemble with the data that makes the databases powerful, though.  That’s where databases are separated from simple storage facilities of bits, bytes and files.

Try before you buy — oh, yeah, it’s still completely free, forget the “buy” part! 🙂

 

First, if you have not been familiarized with MySQL Workbench, it is a pretty good GUI tool that allows you to help express in visual terms the visual scope and relationships between database tables.  As George Carlin said, “everyone needs a place for their stuff.”  Data in databases are no exception.  I suggest going to Oracle’s MySQL site and download this tool to try it out.  It’s a great cross-over tool to help set up databases and map them so laymen in your organization, people not verse in database and web systems, to be able to actively participate in the design, creation, implementation, maintenance, and operation of your company’s databases.  It helps document and visually map out the lifeblood of your information systems and website.  That’s a very powerful thing to bridge the gap between the technical and management.  It’s imperative for companies to have people who understand how their data and systems all work together, the better the documentation and the more clear the relationships are visually for humans to interpret, the better off your company will be.

You will be able to quickly navigate around this simple tool by creating an EER diagram (Enhanced Entity Relationship).  Click the icon that looks like a table, then click on the grid and it will place a table container on the grid, expand it with the handles to accommodate the fields, which you can add and edit to by double clicking a table.  A simple list will appear below that will allow you to add any type of data for each field.  Handling relationships, including adding foreign keys (fk) are almost automatic with an eyedropper.  Hover over the relationship lines that bind any two tables and the fields that reference each other will highlight — a quick way to help “follow the money” sort of speak, by following the string of related data.  The visual cues and layout in a flowchart will inevitably help you build a much cleaner, efficient, and effective database for any system of almost any scope.  Observe how clean and easy it is to follow once I added another table as I began to layout the crowd-funding and social media site for entrepreneurs called “Prolific Futility”:

MySQL Workbench and table relationships
MySQL Workbench and table relationships

 MySQL Workbench isn’t just a thin design tool

Oh no, this actually is a very powerful interface to your databases.  You can do so much, we haven’t scratched the surface, but let’s say you design and critique then update your database schema and you’re convinced you’re ready for prime time to deploy to a development server for testing.  All you have to do is go to “Database” choose “Manage Connections” and set up your database credentials and you can “Forward Engineer” what you’ve designed to your database.  Be sure you open up your IP address to be allowed to access the database, this is a common issue.  Once you’ve connected to your database with “Test Connection” you are only seconds away from creating your entire base from your flow chart models!  MySQL Workbench is easy to use, it’s intuitive, and powerful.  I highly recommend exploring this tool and add it into your mix, especially if you are working a on a new project where there is uncertainty as to how all the data, like user accounts and information that is collected that is related to users that needs to be stored and later be effectively and securely retrieved.  It has been quite a help in promoting good database design principles, the foundation for any company’s data infrastructure both on the web and in intranet environments.

About Author:

Senior Cloud Software Engineer and 25+ years experienced video production, video editing and 3D animation services for a variety of global clients including local video production here in Jacksonville, Florida.

Leave a Comment

Your email address will not be published. Required fields are marked *