With both tables populated with data it's time to get rid of the unique_value field and while doing so we should also change the name of our tables since they are no longer temporary or transitional tables.

Select the tempmembers table in the Schemata pane and open the table editor. Rename the table to members, by changing the table name in the text box in the top left of the table editor. Select the unique_value field and press the Delete key to remove it. Choose Apply Changes to view a dialog box with the following content:

ALTER TABLE `tempmembers` 
  RENAME TO `members`,
  DROP COLUMN `unique_value`;     

Making similar changes to the tempmemberaccreditations table will result in the following DDL statement:

ALTER TABLE `tempmemberaccreditations` 
  RENAME TO `memberaccreditations`
  DROP COLUMN `unique_value`;  

Copy the SQL version of these table alterations to the script window.

Adding indexes to tables is also easily accomplished using the Query Browser. Since we expect searches on the lastname and the city fields these two columns are ideal candidates for indexing. Again this can be done using the table editor. Open the table editor and click the Indices tab. Click the + button on the bottom left and a new index called new_index appears in the list of indexes. Change the name to lastname_idx and drag and drop the lastname column to the Columns text area on the right.

Create an index on the city column in the same way. When you apply your changes you should see something similar to the following:

ALTER TABLE `members` 
  ADD KEY `lastname_idx` (`lastname`),
  ADD KEY `city_idx` (`city`); 

The memberaccreditations table still lacks a primary key. To remedy this, open the table editor again so that we can add a primary key. To do this click the + button on the lower left and ensure that PRIMARY is selected in the Kind drop-down list box. Create a primary key composed of two columns by dragging the memberid column and the certification column to the Columns list. When applying your changes you should see:

ALTER TABLE `memberaccreditations` 
  ADD PRIMARY KEY (`certification`, `memberid`);  

After altering database objects, it's always an idea to refresh the view in the Schemata pane. Do this by right clicking the association database and choosing the Refresh option (Under Unix this option is called Refresh Schemata.)

Looking at the data there is yet one more change we could apply. The certification field may indicate another database entity. Let's create a table of accreditation acronyms with their corresponding descriptions.

One of the simplest ways to create a table and populate it using MySQL is to issue a CREATE TABLE statement in conjunction with a SELECT statement. For instance we could create our final version of the members table in the following way:

CREATE TABLE  accreditations
  SELECT DISTINCT certification AS acronym, '' AS description
  FROM alldata;

At this point we don't have the information necessary to add a description so we populate this field with an empty string.

Creating and populating a table in this way is a quick and easy way to create a populated table. The downside to creating a table in this way is that the resulting table has no primary key or indexes. I'll leave it to you to add an index to this table.

At this point we've created all the necessary tables and migrated the data to those tables. We just need to check the integrity of the data before copying it to a production server.