The new graphical user interface in MySQL 5.0 makes creating
simple databases extremely easy. All of the elements in the database, such as
tables, indexes, constraints, and stored program objects like views and stored
procedures, can be created using these tools.

In this article, we’ll take a scaled-down scenario and walk
it through the process of building a MySQL 5.0 database. After introducing the
scenario, we’ll build a logical model and refine it into a physical model
suitable for implementation. Then we’ll create the database, build its tables
and indexes, and add a few other things like constraints and views.



A look at the problem

Seattle Co-Tech is a fictional co-operative that provides products
and services to self-employed technical consultants. Members of Co-Tech pay an
annual fee and pledge to work eight hours at the co-op each month.
Unfortunately, techies are busy people, and not enough of them are putting in
their hours. Also, it’s difficult to find a member to cover a specific job on
short notice.

The co-op board decides on a two-pronged approach: create a
member directory to make it easier to find substitutes, and reward those who go
above the minimum eight hours by giving them recognition for their efforts.
They therefore need to track hours in a more formal way, and turn to MySQL to
do it.

Build a logical model

Although this is a trivial problem that could designed
informally, building a formal logical model of the solution forces you to think
through design decisions which, if poorly done, will come back to haunt you
later. Like the blueprint for a house, it guides you in creating the necessary
database objects and setting their properties.

Logical models are theoretical descriptions of what in the
real world things about which we need the database to store information. They
consist of three things:

  1. Entities – Entities are the persons,
    places, things, or events we are going to keep track of in our database. They
    are the nouns of the database.
  2. Attributes – Attributes are the
    individual types of information we are going to store about each subject. They’re
    like adjectives, describing the entities.
  3. Relationships – Relationships are the
    ways the entities interact with one another. They’re the verbs, saying who does
    what.

When you put these together in a diagram, you get a logical
model of the planned database. It’s sometimes called an “entity-relationship
diagram,” or ERD. Figure 1 shows the Co-op’s logical model. There are only
three entities: Members, WorkShifts, and Jobs.

Figure A

Start by building a logical model of your database.

Each of the entities has attributes listed. The Members
entity has names, addresses, telephone numbers, and other contact information.
The WorkShift entity records when the member donates time to the co-op by
working some number of hours on Co-op business. The Job entity describes the
type of work performed.

The relationships among entities are represented by lines on
the model, connecting entities and providing text descriptions of the
relationship. To read a relationship, start with one entity and travel to the
next, following the line and text descriptions. For example, starting with
Member, we see that each member may (dashed line) work one or more
(multiple line) WorkShifts. You can also read this backwards: each WorkShift must
(solid line) be the record of time worked by one and only one (single line)
Member.

Derive a physical model

Before we can implement the Co-op’s logical model, however,
we have to refine it into a physical one. Logical models are useful for
understanding the structure of the proposed database, but don’t contain enough
information to actually build it. A physical model targets a specific database
product: the same logical model might be implemented differently on Microsoft
SQL Server, MySQL, or Oracle due to differences in the data types and other
features available. It’s important to do this kind of targeting instead of
staying generic, because if you don’t take advantage of the specific features
in the database, the result will not perform as well as it could.

The first task in refining the model is to decide which
MySQL datatypes will be used to represent each attribute. We’ll also need to
identify which attributes are required versus optional, and which uniquely
identify each entity. We’ll need all of this detail to create actual tables in
the new database.

MySQL 5.0 follows the ANSI SQL 2003 standard very closely,
so the datatypes it supports are generally available in MySQL. It has five
different sizes of integers, for example, from TINYINT (1 byte, maximum value
255) to BIGINT (8 bytes, maximum value 18446744073709551615). Fixed length
character fields, CHAR, are still limited to 255 characters, but variable
length character fields, VARCHAR, can now go up to 65,532 characters. There are
a variety of date and time data types as well, such as DATE and TIME that store
their data separately, or DATETIME and TIMESTAMP that store them together in a
single column.

One common problem in translating a logical model to a
physical one is the presence of Many-to-Many relationships, which are not
supported directly by relational databases. Our diagram shows one such
relationship between Member and Job: a given Member may be able to perform
several different jobs, and conversely, a given Job may be able to be done by
multiple members.

Such Many-to-Many relationships must be decomposed in the
physical model by creating a new entity as a “junction” between them.
This changes the Many-to-Many relationship into a pair of One-to-Many
relationships, which is easy for relational databases like MySQL (or SQL
Server, or Oracle) to deal with. We’ve added an entity called Training to
accomplish this.

Also, the physical model is the place to show any changes you make purely for performance reasons. One such “denormalization”
introduces redundant summary data to avoid having to read all the detail. For
example, to determine the member’s status for the recognition program, we could
read through all their work shift information and apply a formula. But by
putting a redundant Karma attribute in the Member entity, we only have to look
one place to find their current status.

Of course, we’ll have to keep that information up to date. A
stored procedure could be run on a regular basis to assign member Karma, or a
trigger could be used to keep it up to date in real time as WorkShift records
are entered.

Figure B shows the physical model with specific data types,
the junction table Training, and the redundant Karma attribute in Member.

Figure B

Next, building a physical model of your database.

Create a schema

Now we can begin to build our solution. We can call upon a
new MySQL 5.0 tool, MySQL Administrator, to create the database, its tables and
indexes. MySQL Administrator is a separate download and install from the
database itself.

After signing in using the credentials set up when
installing MySQL, the main MySQL Administrator screen appears. The last category in the left-hand navigation
menu is Catalogs. These are also called schemas, and are similar to schemas in
SQL Server 2005: logical groupings of tables within the database. The third
party tool phpMyAdmin calls these databases.

To create a new schema, right-click in the lower left-hand
section of the screen that lists the standard schemas: information_schema,
mysql, and test. Select Create New Schema from the pop up menu, and name the
new schema “coop”. Now an empty objects list appears, showing that no
objects have yet been created in the schema, as you can see in Figure C.

Figure C

Design the schema for your database

Do not begin the name of your schema with the word “test”.
All such schemas are assumed to be test databases, and are accessible to all
users by default!

To create a database using the command line client program,
connect to the MySQL server, and type the command:

CREATE DATABASE coop;

The command CREATE SCHEMA can also be used, and is. The
semicolon at the end of the command is required in the command line client.

Create database tables and indexes

Once the schema is created, tables can be defined within it.
In MySQL Administrator, click the Create Table button to bring up the Table Editor as seen in Figure D.

Figure D

Here you define tables for your database

Enter the table name and an optional comment in the heading
of the form. Then, on the Columns and Indices tab, begin defining the table.

This is where the up-front design work of defining logical
and physical models pays off. For each entity in the model, create a table; for
each attribute, create a column. The datatypes from the physical model can be
entered directly on the form next to the column names.

Two checkoff fields come next. Not Null indicates that the
column is required in all rows. Checking this box applies a constraint to the
data so that an attempted INSERT that is missing data for that column will be
rejected. The Auto Inc field creates an auto-incrementing field. Flags may
appear as additional checkboxes depending on datatype; for example, an INTEGER
column will have an UNSIGNED flag appear to limit the column to positive and
zero values.

The last two items for each column definition are an
optional DEFAULT value, used when an INSERT statement doesn’t mention the
column at all, and a COMMENT field which stores a column comment in the
metadata for the table.

When all columns have been defined for a table, click Apply Changes. You’ll see the actual CREATE
TABLE statement, which you can copy and paste to an editor if you wish. Click
Execute to run the statement and create the table. Then click close to close
the Table Editor. Repeat for each entity
in the physical model.

Establish constraints

After defining the tables, the table list should appear as
in Figure E. You can double-click a table name to return to the table editor,
either to make changes in the column definition or to add constraints.

Figure E

After defining tables, you’ll see the table list.

Constraints place limits on the data entered into the
database. They are called “declarative constraints” because you
declare them in the table definition. The first column entered for a table is
marked with a primary key constraint by default. This prevents duplicate values
from being entered for that column, and is indicated by a gold key icon to the
left of the table name. This icon is a toggle: click to remove or re-add it. If
a table has a composite primary key (more than one column), click next to each
one.

As in SQL Server, an index is automatically created for the
primary key column(s).

Now we come to the lines on the physical model. Each
represents a foreign key constraint that prevents data values from being
entered in a column if they don’t also exist in another (foreign) table.
Several of the tables include MemberID as one of their columns; a foreign key
constraint guarantees that any MemberID entered matches an existing member in
the Member table.

To enter one of these relationships, bring up the Table
Editor for a table, then click the Foreign Keys tab. See Figure F. Click the +
sign to add a new foreign key, or the – sign to delete one. Enter a name for
the foreign key constraint, and from the dropdown list choose which table will
be used for validation. The proposed columns in both tables will be displayed.
Click Apply Changes to create the foreign keys.
The text of the ALTER TABLE statement will appear, and the Execute
button executes the query.

Figure F

Here you’ll define foreign keys.

Create other database objects

MySQL Administrator also contains a SQL editor for creating
indexes, views, and stored procedures. In the coop database, we may want to
create a view that finds all the members who have not yet worked eight hours in
the month of April.

From the table list screen, click the Views tab, then the
Create View button. Enter a name for the view, which must not duplicate the
name of any table or other object in the schema. The MySQL SQL Editor window
opens with a code template already copied into it. Add the text of the SELECT statement that
defines the view, then click Execute SQL button as seen in Figure G.

Figure G

You can create views using the MySQL SQL Editor.

In our case, the query is:

SELECT m.LastName, m.FirstName, SUM(w.HoursWorked)

FROM Member AS m

JOIN WorkShift AS w ON w.MemberID = m.MemberID

WHERE w.DateWorked BETWEEN '2006-04-01' AND '2006-04-30'

GROUP BY m.LastName, m.FirstName

HAVING SUM(w.HoursWorked) < 8.0

We add that to the template already entered into the editor
window, and click Execute SQL. The view is created. It can be edited later by
clicking the Edit View button, or dropped from the database with the Drop View
button.

The Bottom Line

The graphical administration tools added in MySQL 5.0 make
the task of creating simple databases much easier. They are also an excellent
training aid for learning the SQL language, as the SQL that results from
settings on the screen is displayed before being executed.