Pattern / methodology for representing large database records as objects

Question

A bit of background information: We have an old database application written in Access that lets users monitor their workload, and the code is... 'procedural' might be too kind. The vast majority of code is hard-wired to form events, there's a lot of duplication, and little to no abstraction. If it needs to fetch data from the database, it'll just do it right there and then, even if that means roughly the same bit of code repeated 6 times for 6 similar buttons (and 6 practically identical database requests). It's not pretty.

I want to rebuild the application in VB.NET, but I'm hitting a bit of a design snag when it comes to writing classes to represent database records as objects - I don't have a huge amount of experience in OO programming (written a few small apps, read lots of books and material, regular SE lurker), so this is my first OO database application.

The problem is that a given 'job' for a user has a lot of fields on the database. About 30 or so - ID, Title, Requestor, Request Date, Target Date, Priority, Type, Approval... and so on. First pass: Whack everything into a dirty great Job class:

Public Class Job
    Private mID As String
    Private mTitle As String
    Private mRequestorName As String
    Private mRequestorEmail As String
    ...

    Public Property ID() As String
        Get
            Return mID
        End Get
        Set(value As String)
            Me.mID = value
        End Set
    End Property
    ...
End Class

Yikes. I'm sure you can see my hesitance at having such an enormous class sitting front-and-center of the application. All those properties seem to be violating encapsulation too - the Private members might as well be Public, but conversely I need to be able to alter fields and later write back to the database.

Second pass: There's a few things in here that could be extracted and situated elsewhere: for example, all those Requestor___ members could sit in a Requestor class

Public Class Job
    Private mID As String
    Private mTitle As String
    Private mRequestor As Requestor
    ...
End Class

Public Class Requestor
    Private mName As String
    Private mEmail As String
    ...
End Class

So now I have half a dozen of these. Smaller classes? Feels nice. But it feels like all I've really achieved is essentially database normalisation, which doesn't feel like it should live in the application. And the only real change to the structure is that it takes more steps to get to data items.

And I still have all those properties (which are forcing me to use hungarian notation on my private members, which I'm not a fan of, but VB.NET isn't case-sensitive so I can't use the same names), which still aren't sitting right with me.

Is there a standard pattern or methodology for moving large data items to and from a database? My current best idea is the above with commit() and read() methods in the Job class - alterations are made to the objects and then later commited to the database.

Answer 1

The answer to your question is that you probably, most of the time, don't need to.

Actually, you're building up your model in such a way that it trivially maps the existing, relational database design. This is a bad approach per se, moreover if that relational design is flawed and/or denormalized. Persistence is support, not kernel.

Instead, focus on concrete problems your application is supposed to solve, and build a model explicitly designed to fulfill those requirements . This model will reflect your application's conceptual domain, much more than it does your data model.

My experience with large database records is that, most of the time, only a subset of columns is required to perform certain operations.

Answer 2

If you're taking the time to re-write the code in another language, take the time to clean up the database. While your normalization steps may seem like they haven't accomplished much, I can assure you that steps like this will pay enormous dividends. If you have a poorly designed database schema, you will have to support that poor design in your source code.

If you can't re-design the database, then it's probably still worth designing the persistence layer correctly in your source code and then have a light-weight layer in your code that can interact with the poorly-designed database.

Answer 3

The reference to the object-relational impedance mismatch is an interesting read. However when we look at it more carefully, it is basically complaining why a square peg is not fitting a round hole.

That is what a really good ORM product resolves. Unfortunately having been part of two separate ORM's design and development, I can say that many ORM's bring their own challenges.

Even though the question is a year old, the principles mentioned here are learnt over the years and they still apply and may be helpful to someone.

1. Use the DRY principal
2. Always fetch the full record (or only the key fields, of the index you're using to search). There can be exceptions in case of reporting, but for general OLTP applications, do this as a principle because you will change logic often and shouldn't have to go back and change your base record objects (as suggested in the next two principles). The speed penalty rarely applies and coding benefit is much higher.
3. Accept the fact that a database is meant to give you sets of rows (tuples), and use them as they are instead of fighting the structure.
4. Make sure the very first layer of objects that deal with the database is "record aware" (i.e. previous principle). This means objects each handle one type of record only, usually from one table, or one view.
5. You can optionally add a layer of objects that are multi-table aware, i.e. aware of the relationships between tables. This makes things easier. But then you HAVE to use that new layer only, and not go direct to the lower layer for consistency, otherwise you'll have a lot of Code Smell http://blog.codinghorror.com/code-smells/
6. Now write your business logic, in separate objects from the ones that deal with the database. Except put all the validation type constraint checking in the first layer. This means keep the data validation separated from business rules or business functionality.
7. Never "not implement" validation at the database itself, in other words, never skimp/skip on validation but put as much as the database engine will allow. This means from as basic as implementing simple validation like "CHECK constraints" to complex cross table DML triggers. In other words, never let junk into the database (I see a ton of systems by well meaning developers with this flaw). Hmm.. Did I say it loud enough.. I guess not... so... what I'm SAYING :-) is "Don't rely on your UI objects for data integrity that can be done by the database engine". And if you HAVE to allow unvalidated data somewhere in your business process because the "operator/user" may not have it at first use, e.g. a patient is not conscious to give you all the information in an ER scenario, then make sure some process is in place that regularly requires someone to go and back-fill missing data.
8. Records with BLOB fields or other large fields should be handled by the main object meant for the record, but only fetch the blob by key reference as needed. This implementation should be hidden and other objects using the "record" object shouldn't have to care when and how the BLOB is fetched or saved to the database.

This list is deceptively small but will make life easier for the developer and for the DBA who has to live with the design, in any project where Object to relational mapping is needed.