I've a smallish design issue that I hope to seek you advice on. I've a couple of small mysql tables that store ID to name pairs. For instance,
# Table name: levels
level_id level_name
1 Secondary One
2 Secondary Two
3 Secondary Three
4 Secndary Four
The bulk of the member's profile is stored in 'profiles':
# Table name: profiles
member_id
level_id
dob
.
.
.
joined
So to retrieve the level name information, I would do a query as follows:
my $sql = qq{ SELECT * FROM profiles, levels
WHERE member_id=23
AND profiles.level_id=levels.level_id
};
my $sth = $dbh->prepare($sql);
$sth->execute();
my $hashref = $sth->fetchrow_hashref();
# So level name is stored in $hashref->{level_name}
The levels information stored in 'levels' will stay pretty much static - no add, update or delete is likely to take place. Its inclusion in the database is merely to tie a particular level ID to a level name - as a result of normalisation. Thus, I'm wondering if it's better to just store that sort of information in a hash as follows:
# In a separate file from the main script
our %levels = (
1 => Secondary One,
2 => Secondary Two,
3 => Secondary Three,
4 => Secndary Four
);
In which case, the sql query will be simplified to (without AND):
my $sql = qq{ SELECT * FROM profiles, levels WHERE member_id=23 };
my $sth = $dbh->prepare($sql);
$sth->execute();
my $hashref = $sth->fetchrow_hashref();
So level name can be retrived via $levels{$hashref->{level_id}}
May seem pretty obvious but I would like to find out before I commit to a particular design: Where should I store that sort of information? In mysql tables or in hashes?