BEGIN {

  my $dbh = DBI->connect(...) or die DBI->errstr;

  my $sth = $dbh->prepare(
    qq(
      SELECT
        price_type_id,
        price_type_label
      FROM
        price_type
    )
  ) or die $dbh->errstr;

  $sth->execute or die $sth->errstr;

  my $data = $sth->fetchall_arrayref or die $sth->errstr;

  for my $row (@$data) {
    my ($id, $label) = @{$row};
    my $name = lc($label);
    $name =~ s/\s/_/;
    eval qq(
      sub $name {
        return shift->get_price($id);
      }
    );
  }

}
[download]

-><- -><- -><- -><- -><-
All things are Perfect
    To every last Flaw
    And bound in accord
         With Eris's Law
 - HBT; The Book of Advice, 1:7

I'd use a different object model.

With the system you've described you've tightly coupled products with changes to the price type. Instead, I'd have a subclass of PriceType for each different type. These would be auto-generated from the price types table.

That way you can isolate the price type dependant information into the PriceType subclasses.

Pseudo-code:

package PriceType;
use base qw(Class::Singleton);

sub id { croak "this should return the price_type_id" };

my $dbh = DBI->connect(...);
my $price_type = $dbh->prepare('select price_type_id, price_type_label
+ from price_type');
$price_type->execute;
$price_type->bind_columns(my \($id, $label) );
while (my $type = $price_type->fetch) {
    $label =~ = s/[^a-z]//gs;
    my $method = join('::', __PACKAGE__, $label, "id");
    my $id = $id;
    { no strict 'refs'; *{$method} = sub { $id } };
};
[download]

and get_price in the product class becomes:

sub get_price {
   my ($self, $price_type) = @_;
   croak "need PriceType" unless UNIVERSAL::isa($price_type, 'PriceTyp
+e');
   my $sth = $self->sql_find_price;
   $sth->execute($self->id, $price_type->id);
   my ($price) = $sth->fetchrow_array;
   return $price;
}
[download]

So rather than:

my $cost = $product->cost;
my $list = $product->list_price;
my $sale = $product->sale_price;
[download]

you would have

my $cost = $product->price(PriceType::Cost->instance);
my $list = $product->price(PriceType::ListPrice->instance);
my $sale = $product->price(PriceType::SalePrice->instance);
[download]

A tad more verbose, but you can now change price types to your hearts content without touching your product class.

As an alternative, you could argue that the price is more intrinsic to the PriceType than the Product, so you might want to shift get_price into PriceType giving you:

my $cost = PriceType::Cost->instance->get_price($product);
my $list = PriceType::ListPrice->instance->get_price($product);
my $sale = PriceType::SalePrice->instance->get_price($product);
[download]

Hope these vague ramblings make some sort of sense :-)

adrianh, your idea is very insightful and makes perfect sense from a pure Object Design view.

But I am thinking it would also make sense simply to keep the classes matching the tables, and view those classes as a middle layer between your perl code and the database tables.

The most straight forward view to look at this is, to take each table as an class, and the table's columns as properties of the class representing it. This is the basic route to move from traditional RD (relational database) towards modern OD (object database).

Assume the database design is alright, then there would be no need for a subclass of the price type class. If you need a subclass for price type class, so you can store extra info of a particular price type, then where does those extra info being stored in the database? There must be a need for extra columns and different table(s) (which disagrees with the assumption that the database is perfect.)

So if price type is a table to hold all price types, generally speaking, it indicates that there is no need for subclasses of the price type class.

Of course, those are just "generally speaking"... but would be usually alright.

Your thoughts?

The most straight forward view to look at this is, to take each table as an class, and the table's columns as properties of the class representing it. This is the basic route to move from traditional RD (relational database) towards modern OD (object database).

The most straightforward view is not necessarily the correct one ;-)

The one class per table assumption when implementing a relational model in an object oriented world can be an inaccurate oversimplification.

In most non-trivial database schema you will have tables representing relationships that are better implemented in an object oriented environment by inheritance, object composition, etc. - rather than by separate classes.

You will also find single tables representing multiple classes. For example, a single table representing users with different roles (admin, normal, publisher, etc.) might be best implemented in an object oriented environment by multiple subclasses of a user class - since the methods applicable to an "admin" user would be different from those applicable to a "normal" user.

Assume the database design is alright, then there would be no need for a subclass of the price type class. If you need a subclass for price type class, so you can store extra info of a particular price type, then where does those extra info being stored in the database? There must be a need for extra columns and different table(s) (which disagrees with the assumption that the database is perfect.)

I think the database design is just fine. We have products. We have prices of three different types (cost, list & sale). There are many different ways you could represent this in a relation database. Let's consider three:

1. We could have a single product table, and three separate price tables cost_price, sale_price and list_price.
2. We could have a single product table and a single price table, with the different price types indicated by an enumerated type column.
3. We could have a single product table and a single price table, with the different price types indicated by a relationship with a price type table (i.e. what Ovid originally described).

The first is obviously lousy since it's not fully normalised.

The second is fine as far as it goes, but we know from Ovid's original question that it is likely that there are going to be more price types added.

We don't want to have to change our database schema every time marketing come up with a new pricing scheme. We also don't want to have to change our code every time there is a new pricing scheme - and it's hard to find out what an enumerated type column can represent in DBI.

We also know that we need to associate labels with each price type for presentational purposes - surely this would be better in the database than in the code.

Which, of course, leads us to the third option listed above. We have the different types represented explicitly as a separate table that we can query and alter without having to change the database schema or code. We can associate labels with each type easily so we don't have to recode our presentation layer every time we change our underlying price types.

There are, in turn, many possible OO implementations of the above schema. Each with their own advantages and disadvantages. Since we know that price types are likely to change it would seem best to ensure that these changes have as little impact as possible on the rest of the system. To me, isolating the functionality relating to prices and price types into a single PriceType class hierarchy seems a good way of doing this.

So if price type is a table to hold all price types, generally speaking, it indicates that there is no need for subclasses of the price type class.

No it doesn't. It may or may not be true - but you cannot argue the number of classes from the number of tables. Relation models and object models are just different.

Of course, those are just "generally speaking"... but would be usually alright.

As you can probably guess I would disagree with "usually" :-)

price_type : enum('Sale Price',List Price','Cost')
[download]

select amount from prices where id=$id and price_type ='$price_type';
[download]

Is there a specific reason you cannot do it that way? Are applications alrady using the get_price() call and expecting the product object to return the appropriate price?

enoch

without addressing the main issue (because i don't have any good ideas,) i will mention that when i see code like

sub SALE_PRICE_ID {1}
sub LIST_PRICE_ID {2}
sub COST_ID       {3}
[download]

i find people usually mean

sub SALE_PRICE_ID() {1}
sub LIST_PRICE_ID() {2}
sub COST_ID()       {3}
[download]

which will inline these subs. for a sub to be inlined into a constant, it must have a prototype of "()". you (and others) can find more on this in perlsub, under the "Constant Functions" heading.

~Particle *accelerates*

First, these are method calls and prototypes on method calls are ignored. I realize this is a special case that allows for constant folding -- I don't want to use prototypes for the only and only case where they actually work with methods :) I might encourage others to do Bad Things.

Second, the discussion of void prototype issues and the resultant "mysterious" behavior is enough for me to avoid using them unless I really, really want them. Also, subclassing them doesn't always work if they've been optimized away. Later versions of Perl (IIRC >= 5.8?) will optimize away the subroutine itself, thus ensuring that calls to SUPER from a subclass will have disappointing results.

Cheers,
Ovid

New address of my CGI Course.
Silence is Evil (feel free to copy and distribute widely - note copyright text)

Is there a reason you didn't use the "constant" pragma? Personally I find it annoying and use package variables instead, but I believe it does exactly what you just did.

1. As a data integration reference, we can use it to check and make sure all price-id’s in all other tables are valid, i.e. there is row in this table for that price-id.
2. Store the visual presentation for each price-id, for example, if you want to print some report, instead of showing the less meaningful price-id, you may want to show the price type.

I am Ovid's coworker and this part of the database was designed by me.

The product_prices table does store the price_id and uses (product_id, price_id) as the primary key. It wouldn't be normalized otherwise. The name in price_types is only used for display purposes.

Hey, slacker! Get back to work! Sheesh. How are we going to get anything done if you're playing on Perlmonks all day? ;)

Cheers,
Ovid

New address of my CGI Course.
Silence is Evil (feel free to copy and distribute widely - note copyright text)

Okay, then I don’t have a single problem with the database design. (Scary, the guy did the database design is also here, good I didn’t say any thing too strong ;-).

To be serious, I don’t have problem with your database design, I said those all because of Ovid’s typos …Well, he is a good guy made couple small typos ;-)

Good to talk to you. Perl rules.

MJD says you can't just make shit up and expect the computer to know what you mean, retardo! I run a Win32 PPM repository for perl 5.6x+5.8x. I take requests. ** The Third rule of perl club is a statement of fact: pod is sexy.

To ensure that tomorrow's pricing scheme fits into the schema you design today, you might need to limit the creativity of your marketing department. This is a 'bad idea', because you 'need to eat'.

Maybe what you need is a nice way to keep your code maintainable in the face of potentially wildly different requirements. Perhaps you should write new modules for the different pricing schemes, using inheritance or calling common subroutines where it makes sense.

As my friend Steve says, "Make different things different and what is the same the same." If your job is to take other people's money, the answer should be 'yes' whether the implied coding requirements violate design goals or not.

Some of the research on this topic refers to the buzz-phrase "business rules". I think the idea of encapsulating business rules into a fixed relational schema and associated algorithms is known to be problematic.

It should work perfectly the first time! - toma