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:
- 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.
- Attributes - Attributes are the individual types of information we are going to store about each subject. They're like adjectives, describing the entities.
- 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.
|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.
|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.
|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.
|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.
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.
|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.
|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.
|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.