It has been our busiest month yet, as evidenced by the lack of blog posts. We've been preparing the code drop for our Developer Release, which involves tying together all the different modules we've been working on. While the rest of the team toils, I'm going to talk about one of the bigger issues we've been dealing with over the past few months--data persistence, which is developer-speak for "saving information in the database".
Designing object-oriented software yields almost total control over modeling business concepts. Traditional database-driven design may have a Transaction table that holds all transactions, each with identical fields. An object-oriented approach allows the creation of separate objects for each type of transaction, endowing each transaction with the properties and functionality that make it unique. Why have a Share amount in a Dividend? Dividends don't actually have shares, after all. With an object-oriented approach, Dividends not only remain share-free, but also add other unique fields, like PaidDate and ExDate, and specific functionality to utilize them.
Sounds great, right? It is. Much of our next-gen functionality is dependent on FinFolio using a modern object framework. Time-sensitive ownership, n-level account/portfolio trees, the extensible plug-in framework, alerts, and even on-the-fly cost allocation is all dependent on a pervasive object model.
The problem comes in making these complex and nuanced objects persist to a database. Databases aren't designed to hold complex and nuanced objects. Databases work best when they have generic tables, like Account and Transaction. If we were to map a complicated object model directly to database tables, a simple SQL statement like this:
SELECT Code, Date, Shares, Dollars, Price, Amount FROM Trans
would become something like this:
SELECT 'Buy' AS Code, TradeDate AS Date, Shares, Dollars, Price FROM Buy
UNION
SELECT 'Sell' AS Code, TradeDate AS Date, Shares, Dollars, Price FROM Sell
UNION
SELECT 'Div' AS Code, DateRcvd AS Date, NULL as Shares, Amount, NULL AS Price
FROM Dividend
UNION
SELECT 'DivRnv' AS Code, DateRcvd AS Date, Shares, Dollars, NULL AS Price
FROM DividendReinvested
UNION
SELECT 'BegBal' AS Code, Date, Shares, StBal AS Dollars, StBal/Shares AS Price
FROM BeginningBalance
UNION
SELECT 'MgmtFee' AS Code, Date, NULL AS Shares, Fee AS Dollars, NULL AS Price
FROM ManagementFee
...
The solution to this headache is something called an Object Relational Mapping (ORM) layer, which takes a delicate object model and maps it intelligently into a database, allowing SQL-like object queries to be written without worrying about the actual SQL code that runs against the database.
The problem is that none of the ORM products on the market today work well for all aspects of object design. Most of them have limitations that tie them to a particular style of object-oriented development. Unfortunately, FinFolio is wide-reaching in its use of technology; we use many-many relationships, deep object-linking, disconnected objects, and a limited-functionality front-end environment (Silverlight). Most ORM frameworks have frustrating limitations that prohibit one or more of our requirements.
We started off with an ORM called nHibernate which is renowned for its flexibility and enterprise-class capabilities. We quickly grew frustrated with the limitations we encountered. We switched to Microsoft's new Entity Framework beta, tentatively liked what we saw, but ran into much bigger limitations. Our main issue was an overall speed degradation that forced us to cast our net wider.
Our months-long journey took us through several ORM frameworks, each with their own set of fatal limitations, and eventually we decided to take a second look at nHibernate. This time we took a different direction with our object structure that didn't rely as heavily on abstraction, allowing us to map the objects more straightforwardly to data tables. The new direction turned out to be a wild goose chase, as we had an n-squared problem in our number of linking tables, due to the numerous relationships between our different entity objects. Rather than compromise the objects, which would result in reduced functionality in the software, we came full circle and ended up back at the original ORM model we had in our first attempt with nHibernate. In fact, we're so close that my original post on the issue is still valid, albeit premature.
Obviously, we should have accepted the minor limitations nHibernate placed on us months ago. But until we tried the other ORMs, there wasn't any way to know that our first solution was our best solution. The only lingering concern about our final implementation is a minor quibble about how nHibernate structures the database to accommodate FinFolio's highly recursive and polymorphic object model. But we can layer some simple read-write SQL views atop the data tables to make it easy to write SQL queries.
