hash or tables...

kiat has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

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
[download]

The bulk of the member's profile is stored in 'profiles':

# Table name: profiles
member_id
level_id
dob
.
.
.
joined
[download]

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}
[download]

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
);
[download]

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}}
[download]

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?

Merry Christmas and thanks for reading.

Comment on hash or tables... Select or Download Code

Replies are listed 'Best First'.
Re: hash or tables... by castaway (Parson) on Dec 26, 2003 at 08:47 UTC
I'm gonna go for 'in the database'. Putting it in the script just seems 'wrong'. It's better to have all that sort of data in one place, from an overall point of view. Supposing you come back to the code a year later, or someone else attempts to understand it, and starts out by just looking at the database, you'll have a level_id in there that makes no sense. After all, if its never going to change, you could just put the level name in the profiles table and have done with it. (Yes, thats a repition of data, but not much..) So, if its even 'slightly' likely its going to take place, its much better to have it in the database anyway, since in theory you can add to that without changing code. Even if this is just a little script written for your use only, try to think a little more globally. Someday someone might ask for a script that does something like this, and you'll give it to them without thinking about it, so make it as easy to comprehend as possible, including for yourself several years later. C.	[reply]
Re: hash or tables... by CountZero (Bishop) on Dec 26, 2003 at 09:41 UTC
Definitely in the database: that's were data are supposed to go. But you do not need to make a separate table for it (as castaway already suggested). Still if ever you want to change the level descriptions, you will need to adjust the descriptions everywhere in the database, UNLESS you code these descriptions as an ENUM data field which will allow you up to 255 different members in the ENUM in one byte or 65535 different members in two bytes. Changing the level descriptions is then as simple as changing the definition of the ENUM field. CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply]
Re: hash or tables... by Aristotle (Chancellor) on Dec 26, 2003 at 12:06 UTC
Let's leave out all the specifics and put your question into more abstract terms: "Should I hardcode app data into the app logic in order to simplify the logic?" Rephrased in this form, I think you can answer your question yourself. Makeshifts last the longest.	[reply]
Re: hash or tables... by djantzen (Priest) on Dec 26, 2003 at 10:33 UTC
Databases are there to be hit, so hit it. You're talking about an optimization with meager benefits and it seems without any justification save for a few charactors in your SQL statement. Your join is the correct way to do it. "The dead do not recognize context" -- Kai, Lexx	[reply]
Re: hash or tables... by Art_XIV (Hermit) on Dec 26, 2003 at 14:53 UTC
I definitely would NOT hardcode the levels into your code. If you are going to use the %levels in multiple places in you code then you might want to experiment with SELECT'ing the levels into %level and using that as a cache. Keeping them cached would probably be worth it from a performance standpoint if doing so will actually let you make fewer calls to the database, but probably not worth it if it just means make making the same number (albiet shorter) calls to the db. Don't automatically take the caching route even if the performance gains are tremendous, though. Maintainability is just as important or even more important than performance. You don't want to have to do kludges or make hard-to-change code in the name of performance. In the sample code that you provided, most of your overhead is going to come from creating db connections and cursors. The overhead from the join is going to be miniscule compared to that. Hanlon's Razor - "Never attribute to malice that which can be adequately explained by stupidity"	[reply]
Re: hash or tables... by jZed (Prior) on Dec 27, 2003 at 06:44 UTC
SELECT FROM profiles, levels WHERE member_id=23 AND profiles.level_id=levels.level_id* As others have said, do it in the database, but you don't need the AND. Use this: SELECT * FROM profiles NATURAL JOIN levels WHERE member_id=23 A NATURAL join will automatically join on level_id since it has the same name in both tables, no need for the AND clause.	[reply]