Creating a database schema with MySQL Workbench

MySQL Workbench is a free, open-source, cross-platform database tools.

From Wikipedia:

MySQL Workbench is a visual database design tool that integrates SQLdevelopmentadministrationdatabase design, creation and maintenance into a single integrated development environment for the MySQL database system.

One of the features of MySQL Workbench, is a tool for visual database design. Once you have designed your database, you can create a SQL script which can be run to create the database. MySQL calls this functionality 'forward engineering'. This tutorial will guide you through that process.

1. After starting MySQL Workbench, click 'New Model' from the  File menu.

 

MySQL New Model

This will bring up a page that looks like this:

Workbench Main Page

 You can dive straight into creating your tables by clicking the 'Add Table' link:

add table link

We'll go ahead and add a diagram:

add a diagram

Here you'll see a windows like so:

visual design screen

The icons separating between the left and right panes is what we will use.

Click the small icon of the table to add a table:

create table

Then click anywhere on the grid for the table to be placed.

table

 

 

 

Double-click on the table's bar to bring up a display with table information.

table info

The table tabs lets you alter table properties including the name of the table, which I changed to Users.

The Columns tab lets you add columns to the table.

add columns

The first two columns are easy enough to understand. The next columns contain just the initials, this is what they mean:

PK - Primary key

NN - Not null

UQ - Unique

BIN - Binary

UN - Unsigned (non-negative numbers only)

ZF - Zero filled (if there are fewer characters than space, the space is filled with zeros)

AI - Auto increment.

G - Generated (this column is filled with values from a formula)

You can add as many tables as you like. Here, I've added a second table for pets:

pets table

Adding Relationships

In our example, we'll suppose that a pet has one owner, and an owner can have many pets. This is know as a one-to-many relationship.

MySQL Workbench allows you to easily create this relationship using graphical tools.

First, select the one-to-many icon which looks like this:

one-to-many icon

Then select the many side, which in this case is the Pets table. Then select the one side which in this case is the users table. The program will add a line connecting the two tables, using Crow's feet notation to indicate the relationship. It also adds a column for the foreign key for the users table.

adding relationship

There are also icons for creating other sorts of relationships too.

relationships

Using the above information, you can create as many tables and relationships as you like.