Most distinguished Monks,
A recent node
) 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:
my %parameters = (
DSN => "DBI:mysql:music;host=localhost;"
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);
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:
Normalizer.pm (highlighted syntax - 128 KB)
Normalizer.pm (plain script - 38 KB)
Normalizer.pod (documentation - 30 KB)
Normalizer-0.05.tgz (complete package - 33 KB)
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.
_ _ _ _
(_|| | |(_|><
Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
Read Where should I post X? if you're not absolutely sure you're posting in the right place.
Please read these before you post! —
Posts may use any of the Perl Monks Approved HTML tags:
You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
- a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
Link using PerlMonks shortcuts! What shortcuts can I use for linking?
See Writeup Formatting Tips and other pages linked from there for more info.
| & || & |
| < || < |
| > || > |
| [ || [ |
| ] || ] ||