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

I am just wondering if someone could tell me the best way to code this project I'm working on. I seek ur wisdom ;o)

I am creating a new site for a friend of mine, who owns a web hosting business. He has multiple groups, and each group has multiple hosting plans.

What would be the best way to make this dynamic? I am creating the system using a MySQL database. I created one table for groups. He can add, from the back end interface I made, new groups, which he can assign a group id(number), and a group name.

I created the hosting Plans table, which is ONLY for the plan NAME, and assigned group id(number).

This is where I am confused.

How can I get it where all the features would be automatically added to a comparison table, like this:
----------------------------------------------------------- FEATURES|plan1 name | plan2 name | plan3 name | plan4 name | ----------------------------------------------------------- feature1 name | Yes | No | Yes | yes| ----------------------------------------------------------- feature2 name | Yes | Yes | Yes | Yes | -----------------------------------------------------------
and so forth. Basically, just a regular table with the plan names at the top, and the feature names on the left side, with the details under that plan name, like yes, no or how many it might include, including the possibility of unlimited.

I have been racking my brain trying to find a simple, and managable way to do this.

I would really love it if someone had an idea that I could use.

Thank you very much!
Xav

Replies are listed 'Best First'.
Re: Creation Suggestion needed... please
by tkil (Monk) on Apr 26, 2004 at 07:12 UTC

    Just traverse your entire set of tuples, keeping track of all values of feature and/or plan. Then traverse each feature, and each plan, and output either the value you stored, or a useful default. (If you have a truly vast number of features and/or plans, it might be worthwhile to do two passes so you don't have to keep all of them in memory at once, but that seems unlikely...)

    my %features; my %plans; my %feat_plan_val; while ( my ( $feat_id, $feat_name, $plan_id, $plan_name, $val ) = $sth->fetchrow_array() ) { $features{$feat_id} = $feature_name; $plans{$plan_id} = $plan_name; $feat_plan_val{$feat_id}{$plan_id} = $val; } # later... my @plan_ids = sort keys %plans; print join( "\t", 'Features', @plans{@plan_ids} ), "\n"; foreach my $feat_id ( sort keys %features ) { my @vals = ( $features{$feat_id} ); foreach my $plan_id ( @plan_ids ) { my $val = $feat_plan_val{$feat_id}{$plan_id}; push @vals, ( defined $val ? $val : '-' ); } print join( "\t", @vals ), "\n"; }
Re: Creation Suggestion needed... please
by EdwardG (Vicar) on Apr 26, 2004 at 08:01 UTC

    It sounds like you've started coding without thinking about the design, and frankly, it sounds like you would benefit from more than just "an idea" now that you are stuck.

    But in the spirit of careful-what-you-wish-for, here's an idea:

    Add a column "plan_id" to your plan table, this will link to tables described below.

    Make a third table called "features", and include these columns

    • feature_id
    • feature_name

    Make a fourth table called "plan_features", and include these columns

    • plan_id
    • feature_id

    The plan_features table links the plans table to the features table in a many-to-many relationship.

    Make a fifth table called "feature_valid_values", and include these columns

    • feature_id
    • valid_value

    This table contains valid values for each feature, such as "yes", "no", "unlimited". You don't need this table if you don't care what values are used for each feature.

    Can you see how this hangs together? The idea is that each distinct set of information (groups, plans, features, feature values) has its own identity, and relates to other sets of information in a specific way.

Re: Creation Suggestion needed... please
by powerhouse (Friar) on Apr 26, 2004 at 07:26 UTC
    So, your using MySQL, but this is a Perl site...
    Are you using Perl with CGI.pm or without CGI.pm, are you using DBI, or what?

    What modules are you using?
    thx,
    Richard