Author: plavin
Date: 2007-03-23 18:57:36 +0100 (Fri, 23 Mar 2007)
New Revision: 5534
Log:
Add section
Modified:
trunk/userguide/excel-mysql.xml
Modified: trunk/userguide/excel-mysql.xml
===================================================================
--- trunk/userguide/excel-mysql.xml 2007-03-23 17:54:21 UTC (rev 5533)
+++ trunk/userguide/excel-mysql.xml 2007-03-23 17:57:36 UTC (rev 5534)
Changed blocks: 4, Lines Added: 140, Lines Deleted: 7; 6444 bytes
@@ -794,8 +794,131 @@
<title>The Final Tables</title>
- <para></para>
+ <para>
+ With both tables populated with data it's time to get rid of the
+ <literal>unique_value</literal> field and while doing so we should
+ also change the name of our tables since they are no longer
+ temporary or transitional tables.
+ </para>
+ <para>
+ Select the <literal>tempmembers</literal> table in the
+ <guilabel>Schemata</guilabel> pane and open the table editor.
+ Rename the table <literal>tempmembers</literal>,
+ <literal>members</literal>, by changing the table name in the text
+ box in the top left of the table editor. Select the
+ <literal>unique_value</literal> field and press the
+ <keycap>Delete</keycap> key to remove it. Choose <literal>Apply
+ Changes</literal> to view a dialog box with the following content:
+ </para>
+
+<programlisting>
+ALTER TABLE `tempmembers`
+ RENAME TO `members`,
+ DROP COLUMN `unique_value`;
+</programlisting>
+
+ <para>
+ Making similar changes to the <literal>tempmemberaccreditations
+ </literal> table will result in the following DDL statement:
+ </para>
+
+<programlisting>
+ALTER TABLE `tempmemberaccreditations`
+ RENAME TO `memberaccreditations`
+ DROP COLUMN `unique_value`;
+</programlisting>
+
+ <para>
+ Adding indexes to tables is also easily accomplished using the
+ Query Browser. Since we expect searches on the
+ <literal>lastname</literal> and the <literal>city</literal> 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 <guilabel>Indices</guilabel> tab. Click the
+ <guimenu>+</guimenu> button on the bottom left and a new index
+ called <literal>new_index</literal> appears in the list of
+ indexes. Change the name to <literal>lastname_idx</literal> and
+ drag and drop the <literal>lastname</literal> column to the
+ <literal>Columns</literal> text area on the right.
+ </para>
+
+ <para>
+ Create an index on the city column in the same way. When you apply
+ your changes you should see something similar to the following:
+ </para>
+
+<programlisting>
+ALTER TABLE `members`
+ ADD KEY `lastname_idx` (`lastname`),
+ ADD KEY `city_idx` (`city`);
+</programlisting>
+
+ <para>
+ The <literal>memberaccreditations</literal> 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
+ <guibutton>+</guibutton> button on the lower left and ensure that
+ <literal>PRIMARY</literal> is selected in the
+ <guilabel>Kind</guilabel> drop-down list box. Create a primary key
+ composed of two columns by dragging the
+ <literal>memberid</literal> column and the
+ <literal>accreditationacronym</literal> column to the
+ <guilabel>Columns</guilabel> list. When applying your changes you
+ should see:
+ </para>
+
+<programlisting>
+ALTER TABLE `memberaccreditations`
+ ADD PRIMARY KEY (`accreditationacronym`, `memberid`);
+</programlisting>
+
+ <para>
+ After altering database objects, it's always an idea to refresh the
+ view in the <guilabel>Schemata</guilabel> pane. Do this by right
+ clicking the <literal>spreadsheet</literal> database and choosing
+ the <guimenu>Refresh Schemata</guimenu> option.
+ </para>
+
+ <para>
+ Looking at the data there is yet one more change we could apply.
+ The <literal>accreditationacronym</literal> field may indicate
+ another database entity. Let's create a table of accreditation
+ acronyms with their corresponding descriptions.
+ </para>
+
+ <para>
+ 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:
+ </para>
+
+<programlisting>
+CREATE TABLE tblaccreditations
+ SELECT DISTINCT accreditationacronym AS acronym,
+ '' AS description
+ FROM tempmemberaccreditations;
+</programlisting>
+
+ <para>
+ At this point we don't have the information necessary to add a
+ description so we we populate this field with an empty string.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
</section>
<section id ="confirming-data-integrity">
@@ -980,7 +1103,7 @@
</para>
<para>
- It mat seem counterintuitive, but such updates can be handled most
+ It may seem counterintuitive, but such updates can be handled most
easily by recreating the entire database again. If we script this
process then updates can be done in a matter of seconds. All we
need are a few modifications to the script file that we saved as
@@ -988,11 +1111,11 @@
</para>
<para>
- The only additions to this script are <literal>DROP TABLE
- </literal>statements — making it much easier to reuse the
- database already created. This script can be run from the command
- line as described in the previous section or you can open it
- within Query browser.
+ The only additions to this script are <literal>DROP
+ TABLE</literal> statements — making it much easier to reuse
+ the database that's already there. This script can be run from the
+ command line as described in the previous section or you can open
+ it within Query browser.
</para>
<para>
@@ -1018,4 +1141,14 @@
</section>
+ <section id="migration-script">
+
+ <title>The Migration Script</title>
+
+<programlisting>
+
+</programlisting>
+
+ </section>
+
</appendix>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r5534 - trunk/userguide | plavin | 23 Mar |
