view release on metacpan or  search on metacpan

docs/Normalizer.html  view on Meta::CPAN

  my $norm = DBSchema::Normalizer->new ( 
        { 
            DSN           => $DSN,
            username      => $username,
            password      => $password,
            src_table     => $sourcetable,
            index_field   => $indexfield,
            lookup_fields => $lookupfields, # comma separated list
            lookup_table  => $lookuptable,
            dest_table    => $dest_table,
            copy_indexes  => "yes", 
        });
  $norm-&gt;do();  # Just Do It!</PRE>
<PRE>
  # Alternatively, you can have some more control, by
  # creating the lookup table and normalized table separately,
  # especially useful if one of them is an intermediate step.
  #
  use DBSchema::Normalizer qw(create_lookup_table create_normalized_table);
  my $norm = DBSchema::Normalizer-&gt;new( 
        { 
            DSN      =&gt; $DSN,
            username =&gt; $username,
            password =&gt; $password
        });
  $norm-&gt;create_lookup_table (
          {
             src_table     =&gt; $tablename,
             index_field   =&gt; $indexfield,
             lookup_fields =&gt; $lookupfields,
             lookup_table  =&gt; $lookuptable
      });
  $norm-&gt;create_normalized_table (
          {
             src_table     =&gt; $tablename,
             index_field   =&gt; $indexfield,
             lookup_fields =&gt; $lookupfields,
             lookup_table  =&gt; $lookuptable,
             dest_table    =&gt; $dest_table,
             copy_indexes  =&gt; &quot;yes&quot;,
      });</PRE>
<P>
<HR>
<H1><A NAME="description">DESCRIPTION</A></H1>
<P><STRONG>DBSchema::Normalizer</STRONG> is a module to help transforming MySQL database tables from 1st to 2nd normal form.
Simply put, it will create a lookup table out of a set of repeating fields from a source table, and replace such fields by a foreign key that points to the corresponding fields in the newly created table.
All information is taken from the database itself. There is no need to specify existing details. 
The module is capable of re-creating existing indexes, and should deal with complex cases where the replaced fields are part of a primary key.</P>
<P>
<H2><A NAME="algorithm">Algorithm</A></H2>
<P>The concept behind <STRONG>DBSchema::Normalizer</STRONG> is based upon some DBMS properties. To replace repeating fields with a foreign key pointing to a lookup table, you must be sure that for each distinct set of values you have a distinct forei...
<PRE>
         I. Read all records into memory
        II. for each record, identify the unique value for the fields to be
            moved into a lookup table and store it in a hash
        II. again, for each record, find the corresponding value in the 
            previously saved hash and save the non-lookup fields plus the 
                unique key into a new table
        IV. for each key in the hash, write the values to a lookup table</PRE>
<P>I can find four objections against such attempt:</P>
<P>1. Memory problems. The source table can be very large (and some of the table I had to normalize were indeed huge. This kind of solution would have crashed any program trying to load them into memory.) Instead of reading the source table into memo...
<P>2. Data specific solution. To implement this algorithm, we need to include details specific to our particular records in our code. It is not a good first step toward re-utilization.</P>
<P>3. Data conversion. We need to fetch data from the database, eventually transform it into suitable formats for our calculation and then send it back, re-packed in database format. Not always an issue, but think about the handling of floating point...
<P>4. Finally, I would say that this kind of task is not your job. Nor is Perl's. It belongs in the database engine, which can easily, within its boundaries, identify unique values and make a lookup table out of them. And it can also easily make a jo...
<P>That said, the solution springs to mind. Let the database engine do its job, and have Perl drive it towards the solution we need to achieve.  The algorithm is based upon the fact that a table created from a SELECT DISTINCT statement is guaranteed ...
<P>The algorithm takes four steps:</P>
<P>I. create the lookup table</P>
<PRE>
        CREATE TABLE lookup ({lookupID} INT NOT NULL auto_increment 
                  primary key, {LOOKUP FIELDS});</PRE>
<PRE>
        #(and add a key for each {LOOKUP FIELDS})</PRE>
<P>II. fill in the lookup table
</P>
<PRE>

        INSERT INTO lookup 
        SELECT DISTINCT NULL {LOOKUP FIELDS} FROM source_table;
        #(the {lookupID} is automatically created, being auto_increment)</PRE>
<P>III. create the normalized table</P>
<PRE>
        CREATE TABLE norm_table ({source_table FIELDS} - 
                 {LOOKUP FIELDS} + {lookupID})</PRE>
<P>IV. fill in the normalized table</P>
<PRE>
        INSERT INTO normalized table 
        SELECT {source_table FIELDS} - {LOOKUP FIELDS} + {lookupID}
        FROM source_table 
        INNER JOIN lookup 
            on (source_table.{LOOKUP FIELDS}= lookup.{LOOKUP FIELDS})</PRE>
<P>As you can see, the entire operation is run in the server workspace, thus avoiding problems of (a) fetching records (less network traffic), (b) handling data conversion, (c) memory occupation and (d) efficiency.</P>
<P>Let's see an example.</P>
<P>Having a table MP3 with these fields</P>
<PRE>
 mysql&gt; describe MP3;
 +----------+-------------+------+-----+----------+----------------+
 | Field    | Type        | Null | Key | Default  | Extra          |
 +----------+-------------+------+-----+----------+----------------+
 | ID       | int(11)     |      | PRI | NULL     | auto_increment |
 | title    | varchar(40) |      | MUL |          |                |
 | artist   | varchar(20) |      | MUL |          |                |
 | album    | varchar(30) |      | MUL |          |                |
 | duration | time        |      |     | 00:00:00 |                |
 | size     | int(11)     |      |     | 0        |                |
 | genre    | varchar(10) |      | MUL |          |                |
 +----------+-------------+------+-----+----------+----------------+
 7 rows in set (0.00 sec)</PRE>
<P>We want to produce two tables, the first one having only [ID, title, duration, size], while the second one should get [artist, album, genre]. (The second one will also needed to be further split into [artist] and [album, genre] but we can deal wit...
<P>Here are the instructions to normalize this table:</P>
<PRE>
        DROP TABLE IF EXISTS tmp_albums;
        CREATE TABLE tmp_albums (album_id INT NOT NULL AUTO_INCREMENT 
               PRIMARY KEY, 
        artist varchar(20) not null,
        album varchar(30) not null,
        genre varchar(10) not null, 
        KEY artist (artist), KEY album (album), KEY genre (genre));</PRE>
<PRE>
        INSERT INTO tmp_albums 
        SELECT DISTINCT NULL, artist,album,genre FROM MP3;</PRE>
<PRE>