Data Management

SolutionBase: Configuring a simple database in MySQL 5.0

Start by building a logical model of your database

This gallery is also available as a TechRepublic article.

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. Above shows the Co-op's logical model. There are only three entities: Members, WorkShifts, and Jobs.