The entire spreadsheet that we've imported could be described as a table of members' different accreditations. In the previous section we extracted the member information from the alldata table and created a unique id number for each member. The task now is to replace the duplicated member information with a single unique field. In other words, we're going to create a member accreditations table with a foreign key.

The fields in the alldata table that apply solely to a member accreditations table are readily identified:

`certification` VARCHAR(10) NOT NULL
`expirydate` VARCHAR(10) NOT NULL,
`certificationnumber` VARCHAR(10) NOT NULL

So far we've treated the expirydate field as text. While we're creating a member accreditations table we can convert this field to the DATE data type. The new definition for this field is:

`expirydate` DATE DEFAULT NULL  

Again we want to concatenate three columns to create a unique value and also add an integer column for the member id — so we can relate the member certifications to their matching records in the members table. The two additional columns are as follows:

`unique_value` VARCHAR(150) DEFAULT NULL
`memberid` INT(11) NOT NULL DEFAULT '0',  

Right click the association database in the Schemata pane and open the table editor.

You've already added VARCHAR and INTEGER fields so adding a DATE type field should present no problems. Create a table named tempmemberaccreditations and apply your changes. The resulting table should look something like this:

CREATE TABLE `tempmemberaccreditations` (
  `unique_value` VARCHAR(150) DEFAULT NULL,
  `certification` VARCHAR(10) NOT NULL,
  `memberid` INT(11) NOT NULL DEFAULT '0',
  `certificationnumber` VARCHAR(10) NOT NULL,
  `expirydate` DATE DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Don't forget to paste it into the script window before proceeding. If you do forget, retrieving the table structure is a simple matter of executing the SQL statement:

SHOW CREATE TABLE tempmemberaccreditations;

Again we need to populate this table from the alldata table. We are going to select all the records from the alldata table but only selected fields. Click on the Resultset1 tab and enter the following query into the query text box:

INSERT INTO tempmemberaccreditations 
  SELECT CONCAT(firstname,lastname,streetaddress1) AS unique_value, 
  certification, 0 AS memberid, 
  certificationnumber, 
  STR_TO_DATE(expirydate, "%d-%b-%y") 
  FROM alldata;  

Converting a string value to a date is done using the STR_TO_DATE function. This function takes two string arguments; the first is a string expression of the date and the second specifies the date format. In the alldata table dates are in the form '12-Dec-07'. The format specifier tells MySQL exactly how to interpret the string representation of the date. In this case the specifier, "%d-%b-%y", means the day of the month comes first and is expressed as two digits — it will have a leading zero even if the value is less than 10, the month is expressed as an abbreviated name, and the year numerically with two digits. All values are separated by a “-”.

Review the data after executing the INSERT statement. You'll see that dates are now expressed in the default MySQL format, the year has four digits followed by a two digit month, and a two digit day.

At this point, reviewing the data to ensure consistency is a good idea. Any dates that were improperly formatted in the original spreadsheet will not convert to the DATE data type.

When reviewing the data you'll also see that the memberid field is set to '0' for all records. Let's update this field using the values in the tempmembers table:

UPDATE tempmemberaccreditations t2 
  INNER JOIN tempmembers t ON  t.unique_value=t2.unique_value
  SET t2.memberid =  t.id;

That's the last time we'll need the unique_value field for either of our transitional tables. We can now relate these two tables on the numeric id field.