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 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.

Let’s say you have a basic survey database table where it records a response in the “response” field that will contain different values depending on the survey questions.

Now, it’s pretty easy to gather the value of each response and tabulate the quantity of each response in a list like so:

“Yes”   50 responses
“Maybe” 5  responses
“No”    12 responses

Pitfalls and misunderstanding of “SUM” and “COUNT” in MySQL

However, I have encountered some confusion between the use of “SUM” and “COUNT” in MySQL queries.  “COUNT” just counts the number of instances that fit the query abstraction, it’s a way of selecting some data from the entire pool of data out there.  Think of a database as a container filled with…. a bunch of different marbles and coins.  Database queries are supposed to find just what you’re looking for in that container.  What kind of coins?  How many black marbles?  That’s a way of being presented a container full of things and only taking out what you fits what you’re looking for.

So, if you are trying to sum up a tabulation of counts, a “sum” of “counts” just won’t work, they will throw a “#1111 – Invalid use of group function” error message from MySQL.  Why?  Because the values in “response” aren’t numeric and the result from “COUNT” you are trying to accumulatively sum aren’t the result of multiple records (invalid use of group function.)

These are too examples of improper MySQL queries using “sum” and “count”.  After encountering the “group” error, some may think all they would need is to group the field they are trying to count:

SELECT response,SUM(COUNT(response)) FROM homepage_surveys WHERE survey_id=2

SELECT response,SUM(COUNT(response)) FROM homepage_surveys WHERE id=2 GROUP BY response 

These queries all return the tabulations correctly:

SELECT response,COUNT(response) FROM homepage_surveys WHERE id=2 GROUP BY response

SELECT response,COUNT(*) FROM homepage_surveys WHERE id=2 GROUP BY response

By aliasing the result of “COUNT(*)” as “count”, this also orders the results and lists them in descending order:

 SELECT response,COUNT(*) as count FROM homepage_surveys GROUP BY response ORDER BY count DESC