Lieven Cardoen

A big part in the role of the data source layer is talking to a database, most of the times a relational database. I have done some reading about how to implement this data source layer because it can become pretty messy in big projects. It’s also hard to refactor once a choice of pattern is made so it’s important to make the right choice from the beginning. For my current project I’ll look into some patterns, ideas, new technologies and write down a resume here.

Architectural patterns

They define how the domain logic talks to the database. In my experience there should be data access layer (.NET, Java, …) developers and database developers. Most of the times a programmer also does the database side as a result of which they don’t become database experts. When you have data access layer developers and database developers, you will want to seperate SQL from the code, so that a database developer can have a look at the SQL. In this way, a Database administrator can understand how best to tune it and how to arrange indexes.

We had a performance problem with lazy loading a tree. It went so slow that usability was down to nothing. The trees are saved as left right trees in the database (preorder tree traversal algorithm). Normally the advantage of these trees are that loading the whole tree goes very fast. However, for the lazy loading, we only need the children of the node we are loading, and this took a very long time. After searching a bit on indexes in SQL, I managed to improve the performance to a level that usability is really very good now. Lazy loading the trees is now a pleasure.

A good way to do this is to have classes based on the tables (one class for each table). These classes form a gateway to the table. Developers who specialize in the database have a clear place to go.

1. Row Data Gateway: An instance for each row that’s returned by a query.
2. Table Data Gateway: Single object for each table in the database. This provides methods to query the database that return a Record Set.

This will only work for applications where the domain model is allmost equal to the database structure, having one class for each table.

A Table Data Gateway can also be used to organise Stored Procedures. You could treat the Stored Procedures as Tables and have a Table Data Gateway to wrap the calls to the stored procedures.

Active Record is having a Row Data Gateway and then adding domain logic to the class. Again, if your Domain Model corresponds closely to the database structure, this option is usefull. However, if your Domain Model becomes more and more complicated, the Active Record approach will break down sooner or later. As you start to implement inheritance, OO patterns, stategies, … in your Domain Model, things will get out of control. Another thing is that O/R mapping tools generate classes that you will need to extend to add Domain Logic. Eventually sending these objects to Flex will get messy as you will have to exclude certain properties that do not need to be send. A solution is to isolate your Domain Model from the database by making an indirection layer responsible for the mapping between domain objects and db tables. This is called a Data Mapper and it completly isolates the two layers.

There are tools that create these patterns(O/R mapping tools). I have used MyGeneration together with Entity Spaces and SubSonic (both Table & Row Data Gateways I think). Both were very helpfull but not ideal. They are third party tools and when you change the database you need to recreate all the classes. The biggest problem is that if the database changes, compiling the project will be no problem. At runtime however problems will start and errors will follow. All the projects that I’ve done are RIA using Flex and Remote Calls to the Server. This means having DTO’s. The classes created by Entity Spaces and SubSonic are too large and complicated too send to Flex. So I had to create Mappers from Entity Spaces/SubSonic to the DTO’s, which made it complicated. I’m kind of hoping that LINQ solves this. In the current project I’m working on the complexity is even bigger. I’m using Stored Procedures to retrieve data and SubSonic to save data and sometimes retrieve data. This means I have to convert DataSets and SubSonic Objects to DTO’s before sending them to Flex. Now we are also porting the model from Flex to .NET, which means that converters also need to be able to convert datasets and subsonic objects to the Model. It gets kind of complicated and I’m searching for a good solution here.

Another thing are OO databases. I haven’t experimented with them yet, but the whole problem of mapping the database to the model would disappear with a OO database (I think). The whole mapping thing is only relevant because of the fundamental difference between objects and relations. Objects hold a reference to another object while tables hold a primary id to another record. I guess not many projects use OO databases, maybe because relational databases have been around for a long time and are proven technology.