in reply to Database design and Class::DBI

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 :-)

Replies are listed 'Best First'.
Re: Re: Database design and Class::DBI
by pg (Canon) on Mar 06, 2003 at 05:35 UTC
    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?
[reply]
      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" :-)

[reply]

In Section Seekers of Perl Wisdom