use Perl

• Database Abstractions (Score:2)

  by Ovid (2709)

  The benefits of database abstractions such as Class::DBI are obvious, but the downsides of these technologies can hamper scalability. On one hand, with the CDBI approach, we have a clear mapping of classes to tables, but the databases for which these have been designed are relational when classes tend to be heirarchical. This not only forces developers to accept awkward design constraints (subclassing a table can be an act of supreme masochism), but with the wrong design, it can also limit scalability.
  • Re:Database Abstractions (Score:2)

    by Ovid (2709)

    Dang. I seemed a bit crabby in that, didn't I? I think I made the situation sound worse than it really is.
    • Re:Database Abstractions (Score:2)

      by ziggy (25) on 2004.08.06 11:22 (#33144) Journal

      I don't think that was overly crabby. It just highlights that there's a solution to this problem that is simple, obvious and wrong.

      I haven't used Class::DBI in very many projects, but it does make the process of writing an abstraction for a simple database downright painless. I've also written a couple of thin database frameworks, so I've got an idea of what the problem domain is about.

      The situation you described feels like a heavyweight OODB mapping. I don't think that's an accurate description of Class::DBI or the new wave of abstractions that are coming out. Sure, an abstraction that requires multiple queries to do the region/store/salesperson/customer walk though the database is a bad idea. Using raw SQL, this would be a single query with joins. Conceptually, it is a database method that returns customers with a single free variable -- region.

      Perhaps it's difficult to write an abstraction that can take a database model and generate that method automagically. Perhaps it's difficult and inefficient to write code that walks the tree from regions down to customers. I agree that's the wrong way to do things. The right way (IMNSHO), is to add a method on the customer model that invokes that single SQL query with a single parameter -- region. Sure, you're introducing SQL code into a (Perl) program, but that's not such a bad thing. The point isn't to eliminate raw SQL statements, but to abstract them away from higher level constructs, and allow them to vary as the database model varies, without unnecessary impact to the rest of the system.

      Sure, you lose the ability to treat the database as a transparent persistant object store, but I'm starting to believe that was always a bad idea. Which might be why there's no one good solution to this problem.

      Reply to This

      Parent

      • Re:Database Abstractions (Score:2)

        by Ovid (2709)

        Well, one thing that might help is to implement a "one table" interface. Imagine that your boss wants a report of the average sales per salesperson. If everything was in one big table, you could write:

        SELECT   salesman_name, avg(revenue)
        FROM     the_table
        GROUP BY salesman_name
        ORDER BY salesman_name

        Of course, such a database would be impossible to manage, but if you treat it like that, you can drop the FROM clause:

        SELECT   salesman_name, avg(revenue)
        GROUP BY salesma