Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??
Most distinguished Monks,
A recent node (thanks, jeffa) offered a very interesting theoretical (and practical) view into database normalization.
While I couldn't agree more on the benefits of normalization, I was wondering if there was a different way of tackling the problem from the practical side.
Of course, normalization is never going to be "easy." But it could be easier than building a specialized solution for each table.
Due to previous experience with these tasks (which I used to solve in C - don't shoot!) I felt that a more general solution is possible, and fiddling around with Perl I came up with a module that can reduce our normalization efforts to something like the following:

#!/usr/bin/perl -w use strict; use Normalizer; my %parameters = ( DSN => "DBI:mysql:music;host=localhost;" . "mysql_read_default_file=$ENV{HOME}/.my.cnf", src_table => "MP3", index_field => "album_id", lookup_fields => "artist,album,genre", lookup_table => "tmp_albums", dest_table => "songs", copy_indexes => 1 ); my $norm = Normalizer->new (\%parameters); $norm->do();
The more adventurous could also try a one-liner (Normalization Golf?):
perl -e 'use Normalizer; Normalizer->snew(qw(localhost music \ MP3 album_id album,artist,genre tmp_albums songs 1 0 0))->do()'
What happens with this script?
Having this initial data (database: music, table: 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 | | | +----------+-------------+------+-----+----------+----------------+
Here are the instructions produced by the above lines of perl:
DROP TABLE IF EXISTS tmp_albums; # create the lookup table 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)); # populate the lookup table INSERT INTO tmp_albums SELECT DISTINCT NULL, artist,album,genre FROM MP3; DROP TABLE IF EXISTS songs; # create the destination table CREATE TABLE songs (ID int(11) not null auto_increment, title varchar(40) not null, duration time not null default '00:00:00', size int(11) not null, album_id INT(11) NOT NULL, PRIMARY KEY (ID), KEY title (title), KEY album_id (album_id)); # Here is the trick! Using the lookup fields # as foreign keys, we populate the destination # table from source_table JOINed to lookup_table INSERT INTO songs SELECT src.ID, src.title, src.duration, src.size, album_id FROM MP3 src INNER JOIN tmp_albums lkp ON (src.artist =lkp.artist and src.album =lkp.album and src.genre =lkp.genre);
Don't rush to the CPAN ;-). The Normalizer module is not there (yet) also because I don't know if I should ask for a standalone namespace or under DBIx:: (any piece of advice here will be more than welcome). It is not a short piece of code either. It is 968 lines (65% of which are documentation) and I didn't feel like posting all of it in this node.
But you are welcome to have a look at it from the following addresses: (highlighted syntax - 128 KB) (plain script - 38 KB)
Normalizer.pod (documentation - 30 KB)
Normalizer-0.05.tgz (complete package - 33 KB)

update 2-Feb-2002
Now in the CPAN as DBSchema-Normalizer

The black magic behind this code is more SQL than Perl. However, Perl makes it easier to collect the necessary pieces of information from the database engine and create the SQL statements. More important, Perl makes a generalized solution feasible.
A complete explanation of the algorithm is in the module documentation. The basic concept is to let the database engine work the heavy load, while Perl is directing the operations without wasting any valuable resources.
This module deals only with MySQL databases, but the principle should be valid for any RDBMS. If you want to try it risk-free, it is possible to run the script in "simulation mode," producing the SQL without executing it.
I hope this is going to be helpful, and I will be glad to receive your comments.

 _  _ _  _  
(_|| | |(_|><

In reply to Database normalization the easier way by gmax

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or or How to display code and escape characters are good places to start.
Log In?

What's my password?
Create A New User
Domain Nodelet?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (3)
As of 2022-09-28 19:38 GMT
Find Nodes?
    Voting Booth?
    I prefer my indexes to start at:

    Results (124 votes). Check out past polls.