Now that the data has been copied into a MySQL database, we need to split it up into different tables — we want a proper relational database and not another flat database. The most obvious entity is a member, having the attributes name and address. The following fields from the alldata table belong to this entity exclusively:

lastname VARCHAR(50) NOT NULL
firstname VARCHAR(50) NOT NULL
streetaddress1 VARCHAR(50) NOT NULL
streetaddress2 VARCHAR(50) NOT NULL
city VARCHAR(50) NOT NULL
state VARCHAR(2) NOT NULL
zipcode VARCHAR(10) NOT NULL

Removing any fields that relate to certification gives us the basis for a members table.

We need to transfer data from the alldata table into a members table but, since members can have more than one certification and so appear more than once in the alldata table, we can't just copy all records over to a members table. To make sure that we have unique records we need a way of uniquely identifying each member. We can do this by combining a number of fields together to create a unique value — a combination of the firstname, lastname, and streetaddress1 columns fits the bill. The combination of these fields could form a primary key, but it would be a very cumbersome one. For this reason, we're also going to add a numeric key value — an integer AUTO_INCREMENT field. The two new fields are:

unique_value VARCHAR(150)
id INT(11)       

Create this table using the table editor in the same way that you created the alldata table. The only new element is an integer, auto increment field. To create this field select INTEGER as the data type and ensure that all three check boxes in the column options frame, Primary Key, Not NULL, and Auto Increment, are checked. Make sure the size of the unique_value column is adequate and add the other columns exactly as you did before.

When you're ready apply your changes and copy the SQL from the dialog box. It should look something like the following:

CREATE TABLE `tempmembers` (
  `unique_value` VARCHAR(150) DEFAULT NULL,
  `id` INT(11)  NOT NULL AUTO_INCREMENT,
  `firstname` VARCHAR(30) NOT NULL DEFAULT '',
  `lastname` VARCHAR(40) NOT NULL DEFAULT '',
  `streetaddress1` VARCHAR(60) NOT NULL DEFAULT '',
  `streetaddress2` VARCHAR(60) NOT NULL DEFAULT '',
  `city` VARCHAR(60) NOT NULL DEFAULT '',
  `state` VARCHAR(10) NOT NULL DEFAULT '',
  `zipcode` VARCHAR(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Paste this table definition into the script window and switch to the Resultset1 tab to create a query to populate this table. As described above, we want to concatenate three columns to create a unique value and also add an auto increment column. The remaining columns come directly from the alldata table.

To populate the tempmembers table enter the following SQL into the query text box and execute it:

INSERT INTO tempmembers 
  SELECT DISTINCT CONCAT(firstname, lastname, streetaddress1) AS unique_value, 
  NULL AS id, firstname, lastname, 
  streetaddress1, streetaddress2, city, state, zipcode 
  FROM alldata;  

Using DISTINCT with the unique_value field should guarantee that we don't have duplicate members and selecting NULL as the id field generates a unique auto increment value for each record. Look at the records in the tempmembers table to confirm that unique id numbers have been generated.

This is fairly close to what a final version of a members table would look like — removing the unique_value field would be the next step to take but as you'll see shortly, we still need this field.