This is a programming/algorithm question: How would you store an unordered
list of unordered lists in an RDBMS?
+-----+----------------+
| pid | name |
+-----+----------------+
| 1 | Rei |
| 1 | Minako |
| 1 | Michiru |
| 2 | Usagi | -> (['Rei', 'Minako', 'Michiru'],
| 2 | Rei | ['Usagi', 'Rei'],
| 3 | Ami | ['Ami', 'Usagi'],
| 3 | Usagi | ['Makoto', 'Minako', 'Haruka'])
| 4 | Makoto |
| 4 | Minako |
| 4 | Haruka |
+-----+----------------+
The above is the database design that I'm currently using to
accomplish this. I find that the Perl code to parse it into a Perl
data structure is a pain, though:
my $sth = db::query("SELECT * FROM pairings WHERE fid = $fid ORDER BY pid");
while (my $pairing = $sth->fetchrow_hashref) {
if ($lastpid == $pairing->{pid}) {
$pairings[$#pairings] .= '/'.$pairing->{name};
} else {
push(@pairings, $pairing->{name});
$lastpid = $pairing->{pid};
}
}
Does anyone have suggestions on how I might store arrays in an RDBMS
in a cleaner fashion than I am doing now? (Perhaps my algorithm can be
improved? Maybe there's something simple I overlooked? Or maybe
there's a CPAN class that abstracts this sort of conversion for me
without being too inefficient?)
