# Diagram 1
+-----+-----------------+--------+-----------+------+
| id | title | artist | album | year |
+-----+-----------------+--------+-----------+------+
| 494 | Isobel | Bjork | Telegram | 1996 |
| 495 | Cover Me | Bjork | Telegram | 1996 |
| 498 | Crying | Bjork | Debut | 1993 |
| 499 | One Day | Bjork | Debut | 1993 |
+-----+-----------------+--------+-----------+------+
####
# Diagram 2
+-----+-----------------+--------+-----------+------+
| id | titles | artist | album | year |
+-----+-----------------+--------+-----------+------+
| 494 | Isobel,Cover Me | Bjork | Telegram | 1996 |
| 495 | Crying,One Day | Bjork | Debut | 1993 |
+-----+-----------------+--------+-----------+------+
##
##
CREATE TABLE songs (
id int(11) NOT NULL auto_increment,
title varchar(65) default NULL,
artist varchar(65) default NULL,
album varchar(65) default NULL,
year varchar(4) default NULL,
PRIMARY KEY (id)
);
##
##
CREATE TABLE song (
id int(11) NOT NULL auto_increment,
title varchar(65) default NULL,
PRIMARY KEY (id)
);
##
##
CREATE TABLE artist (
id int(11) NOT NULL auto_increment,
name varchar(65) default NULL,
PRIMARY KEY (id)
);
##
##
CREATE TABLE album (
id int(11) NOT NULL auto_increment,
title varchar(65) default NULL,
year varchar(4) default NULL,
artist_id int(11) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY index_artist_id(artist_id)
REFERENCES artist(id)
);
##
##
CREATE TABLE song (
id int(11) NOT NULL auto_increment,
title varchar(65) default NULL,
album_id int(11) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY index_album_id(album_id)
REFERENCES album(id)
);
##
##
mysql: select * from mp3.song limit 3;
+----+--------------------------------+----------+
| id | title | album_id |
+----+--------------------------------+----------+
| 1 | Until the End of the World | 1 |
| 2 | So Cruel | 1 |
| 3 | Tryin to Throw Your Arms Aroun | 1 |
+----+--------------------------------+----------+
mysql: select * from mp3.album limit 3;
+----+--------------+------+-----------+
| id | title | year | artist_id |
+----+--------------+------+-----------+
| 1 | Achtung Baby | 1991 | 1 |
| 2 | War | 1983 | 1 |
| 3 | October | 1981 | 1 |
+----+--------------+------+-----------+
mysql: select * from mp3.artist limit 3;
+----+-------------+
| id | name |
+----+-------------+
| 1 | U2 |
| 2 | Bjork |
| 3 | Pink Floyd |
+----+-------------+
##
##
mysql: select artist.name from artist
inner join album on artist.id=album.artist_id
inner join song on album.id=song.album_id
where song.title='So Cruel';
+------+
| name |
+------+
| U2 |
+------+
##
##
$VAR1 = {
'Bjork' => {
'Debut' => {
'list' => [
'Crying',
'One Day',
'Come To Me',
'Violently Happy'
],
'year' => '1993'
},
'Telegram' => {
'list' => [
'Isobel',
'Cover Me',
'Army Of Me',
'Possibly Maybe'
],
'year' => '1996'
}
}
'U2' => {
#etc...
}
};
##
##
use strict;
use DBI;
# connect
my $dbh = DBI->connect(
qw(DBI:mysql:mp3:host user pass),
{ RaiseError => 1 }
);
# get existing data as one big ole 2-d array
my $songs = $dbh->selectall_arrayref("
select artist,album,title,year
from songs
");
# munge data into new data structure
my %hash;
foreach my $row (@$songs) {
my ($artist,$album,$title,$year) = @$row;
# auto-vivification is what makes this all so cool
push @{$hash{$artist}->{$album}->{'list'}},$title;
# getting the year is tricky - since i know they
# are all the same - i can safely or-cache it ;)
$hash{$artist}->{$album}->{'year'} ||= $year;
}
foreach my $artist (keys %hash) {
# insert this artist - get unique id for albums
$dbh->prepare("
insert into artist(name)
values(?)
")->execute($artist);
my $artist_id = last_id();
foreach my $album (keys %{$hash{$artist}}) {
my $year = $hash{$artist}->{$album}->{'year'};
# insert this album - get unique id for songs
$dbh->prepare("
insert into album(title,year,artist_id)
values(?,?,?)
")->execute($album,$year,$artist_id);
my $album_id = last_id();
foreach my $song (@{$hash{$artist}->{$album}->{'list'}}) {
# insert this song
$dbh->prepare("
insert into song(title,album_id)
values(?,?)
")->execute($song,$album_id);
}
}
}
$dbh->disconnect;
# mysql specific - other vendor's milleage will vary
sub last_id {
$dbh->selectall_arrayref("
select LAST_INSERT_ID()
")->[0]->[0]
}
##
##
# orginal First Normal Form table
mysql: select artist,album,title,year
from songs
order by artist,year,album,title
limit 2;
+----------------+-------------+-----------------+------+
| artist | album | title | year |
+----------------+-------------+-----------------+------+
| 10,000 Maniacs | In My Tribe | A Campfire Song | 1987 |
| 10,000 Maniacs | In My Tribe | Cherry Tree | 1987 |
+----------------+-------------+-----------------+------+
2 rows in set (0.02 sec)
# new Second Normal Form tables
mysql: select artist.name, album.title as album,
song.title, album.year
from artist
inner join album on artist.id=album.artist_id
inner join song on album.id=song.album_id
order by artist.name,album.year,album.title,song.title
limit 2;
+----------------+-------------+-----------------+------+
| name | album | title | year |
+----------------+-------------+-----------------+------+
| 10,000 Maniacs | In My Tribe | A Campfire Song | 1987 |
| 10,000 Maniacs | In My Tribe | Cherry Tree | 1987 |
+----------------+-------------+-----------------+------+
2 rows in set (0.04 sec)
##
##
# 1st form
mysql: update songs set name="Bjork"
where name="Bork";
Rows matched: 14 Changed: 14
# 2nd form
mysql: update artist set name="Bjork"
where name="Bork";
Rows matched: 1 Changed: 1