• Choose a category
• All
• Classic-Fiction

Figure 3-2: Now your model has a table in it.

3. Edit the columns.

Select the Columns tab at the bottom of the screen to edit the table’s fields. You can add field names and types here. Create a table that looks like the hero table shown in Figure 3-3. You can use the tab key to add a new field.

4. Make a diagram of the table.

So far, MySQL Workbench seems a lot like phpMyAdmin. The most useful feature of Workbench is the way it lets you view your tables in diagram form. You can view tables in a couple of ways, but the easiest way is to select Create Diagram from Catalog Objects from the Model menu. When you do so, you’ll see a screen, as shown in Figure 3-4.

Figure 3-3: Editing the table definition.

Figure 3-4: Now you have a diagram of your table.

The diagram doesn’t show the contents of the table, just the design. In fact, MySQL workbench doesn’t really care that much about what is in the database. The key idea here is how the data is organized. This matters because you will be creating several tables to manage your superheroes.

5. Extract the code.

If you want, you can see the SQL code used to create the table you just designed. Simply right-click the table and choose Copy SQL to Clipboard. The CREATE statement for this table is copied to the Clipboard, and you can paste it to your script. Here’s the code created by Workbench:

CREATE TABLE IF NOT EXISTS ‘mydb‘‘.‘hero‘ (

‘heroID‘ INT NOT NULL ,

‘name‘ VARCHAR(50) NULL ,

‘birthdate‘ DATE NULL ,

‘missionID‘ INT NULL ,

PRIMARY KEY (‘heroID‘) )

ENGINE = InnoDB

This is great and all...

But how do I work with an actual database? MySQL Workbench is used to help you design and understand complex databases. So far, you’ve been working in a local system that isn’t attached to a particular database. This is actually a pretty good way to work. Eventually, though, you’ll be settled on a design, and you’ll want to build a real database from the model. MySQL Workbench has a number of tools to help you with this. First, use the Database – Manage Connections dialog box to create a connection to your database. Then you can use the Forward Engineering option to commit your design to the database or the Reverse Engineering option to extract a database you’ve already created and build a diagram from it.

While these options can be handy, they aren’t really critical. To be honest, I don’t generally use the code engineering features in MySQL Workbench. In fact, I (like a lot of data developers) do most of my initial data design on a white board and then make cleaner versions of the design with tools like MySQL Workbench. I’m showing you the tool here because it may be helpful to you, and it produces prettier artwork than my white board scribblings.

The hard work is organizing the data. It’s pretty easy to convert a diagram to SQL code. Use a tool like MySQL to see how your data fits together. Then if you want, you can either let it build the code for you or simply use it as a starting place to build the code by hand.

As you’ve seen with other languages, visual tools can help you build code, but they don’t absolve you of responsibility. If the code has your name on it, you need to understand how it works. That’s most easily done when you write it by hand.

Introducing Normalization

Trying to cram all your data into a single table usually causes problems. The process for solving these problems is called data normalization. Normalization is really a set of rules. When your database follows the first rule, it’s said to be in first normal form. For this introductory book, you get to the third normal form, which is suitable for most applications.

First normal form

The official definitions of the normal forms sound like the offspring of a lawyer and a mathematician. Here’s an official definition of the first normal form:

A table is in first normal form if and only if it represents a relation. It does not allow nulls or duplicate rows.

Yeah, whatever.

Here’s what it means in practical terms:

Eliminate listed fields.