I am currently involved in a project aiming to re-write an existing Intranet frontend for a FTSE250 insurance firm in the UK.
The existing Intranet was built with .Net 1.1 by ex VB6 developers with a poorly structured code and no sign of a layered architecture with SQL code littered throughout the application and no classes to model the domain.
If that wasn’t bad enough, the database this system is providing a frontend for has clearly not been built following the principles of OLTP databases. More on this later.
The reason why this is an issue is that we’ve all used Object Relational Mapping (ORM) tools in the past and it’s been decided to use NHibernate to provide the concrete implementation of our Data Access Layer which is exposed using the Repository pattern.
Things that are making it difficult for us application developers with a fairly good experience with NHibernate:
Data structure
And ORM requires a good database structure, with a fair amount of normalization. The thing that hit me several times is that a lot of developers are ready to lose some of the basic OO principles when designing a database at the same time as creating their domain model. NHibernate does support interesting concepts such as Many-to-Any relationships that can enable polymorphic associations between entities (class Foo : IFoo {IBar Bar {get;set;}}).
With poor data structure we may end up with unwanted data duplication and sometimes with a substantial gap between how the data is structured and the understanding someone would have just by looking at the frontend. For instance Many-to-Many “business” relationships done in only 2 tables..
Auditing
Version Auditing done in the same table as the table that is audited, instead of using another table which is better in my opinion, we end up with an extra Version column that has to in effect be part of the composite key now.
I am not sure what’s the best solution but I would favor a FooAudit table containing the exact same columns from Foo.
Multi-tenancy with Composite Keys
Multi-tenancy with Composite Keys. Having read several times the advice not to use Composite Keys with NHibernate and to instead use Surrogate Keys we’ve had a really hard time mapping the database to an object model, one of our team members even had to patch NHibernate so that we can support a fairly simple Man- to-One relationship between tables containing shared elements of their Composite Keys such as:
Table1 has a Composite Key formed of keys Table1KeyA, Table1KeyB and Table1KeyC
Table2 has a Composite Key formed of keys Table2KeyA and Table1KeyB
NHibernate doesn’t support this model natively because it expects to only hold distinct set of columns for foreign keys from its primary key columns (in Table1 so that it holds a reference to Table2).As we haven’t got experience with Multi-Tenanting we’ve probably done some early mistakes that may cost us dear in the future, for instance it may be best not to use the keys involved in the Multi-Tenanting part of the Primary Keys and use some interceptors to set those up when making queries or by using filters.
We’re obviously not the only ones having to try to map a Domain Model on top of a Legacy Database with an ORM. Like so many before us, we had to create Database Views to create associations between tables or even hacks directly from within entities themselves. All this make it very difficult to reach Persistence Ignorance and this is a lesson learned that there is a fundamental difference between a Database built by DBAs and Application Developers who will favor Surrogate Keys.