Database – Does ORM promote database de-normalisation

databasedatabase-developmentorm

Doctrine and Propel both make use of single and concrete table inheritance to map object relationships. The former sees all possible fields in the class tree mapped to a single table – whereas the latter maps each class to a specific table, duplicating common fields in the inheritance hierarchy.

While this facilitates the ORM apparatus, it suggests bad database design to me. Are these bad design patterns to enforce on a database?

Best Answer

It's impossible to say whether a particular database design is bad without knowing what the application is doing, the shape of the data, the performance expectations and so on. While generally normalisation (to some degree) is seen as best practice, it's pretty common to denormalise areas of databases for performance reasons so good and bad are very much open for discussion without a lot more data than most people have when they start out.

Add in the many approaches that can be taken to object to relational mappings and things become even more complex as the "best" database structure will depend on the specific object model, the level of inheritance and so on.

By taking a one size fits all approach ORM persistence libraries will almost always produce a non-optimal database structure for any given situation and will use some things which can be seen as bad practice for that a given situation.

You certainly could write an ORM that normalised but you'd see fairly hefty performance implications as for every insert into a main table it needed to scan the various look up tables to see if values existed, if they did obtain their keys and if they didn't carry out the relevant inserts.

(When you do this by hand you can short cut some of this as you know you presented them with a drop down containing only valid value so you don't need to do these look ups, you can just use the key happy that it's going to be valid, the ORM couldn't make that assumption as it doesn't control the UI.)

But what you have to remember is that they aren't aiming to optimise for database performance or data integrity, they're optimising for development speed. If the specific structure of your data is important to you then you either need to code your object / RDBMS mappings by hand, or at the very least do a detailed assessment of all the libraries available and select the one which most closely meets your needs (if one exists).

Essentially it comes down to requirements and the trade off between well structured data, database performance and development speed. As with many trade-offs you don't get to pick all three.

Related Solutions

Database – NHibernate and complex domain model against a legacy database

Unfortunately I don't know much about AutoMapper but I can offer a solution to your second question. Fluent NHibernate is a tool to aide with your NHibernate mappings, allowing you to configure them in a programmatic way rather using than XML. One of the methods you can call when setting up your mappings is DiscriminateSubClassesOnColumn() which allows you to specify the database column that will be used to create your sub-classes. Then in your sub-classes you specify the DiscriminatorValue which can be an enum.

Here is what I imagine your mappings to look like (untested code):

public class ProgramMap : ClassMap<Program>
{
    public ProgramMap()
    {
        //map your fields as normal, eg: Map(p => p.Id), Map(p => p.Name)
        DiscriminateSubClassesOnColumn<int>("Episode");
    }
}

public class MovieeMap : SubclassMap<Movie>
{
    public MovieMap()
    {
        DiscriminatorValue(0);
    }
}

The problem may actually be specifying the DiscriminatorValue for your Movies but hopefully this gives you something to go on. For reference I've used the following links to put this answer together so they may be worth a look in case I've missed something out.

Note that if you don't specify the DiscriminatorValue, Fluent NHibernate assumes that the discriminator column contains your class name (which would be a terrible database design!).

Audit Trails and Entity Relationships in ORM

Never never never have only one table for all auditing. This becomes a hot spot in the database and is a guarnatee of poor performance. You can do something simliar to that but do it for each audited table (We have script that will create new audit tables in our structure as we need them. )

I prefer to have the relasionship make the details subordinate to one thing at defines the actual audited information which is the auditlogid and date time. That way it is easier to relate all records together for one action (say you had a bad delete that took out 25000 records instead of 10, it is much easier to get them back from the audit tables if they are all related together. Make sure you think about how to get data out of the audit table to fix a mistake and write the script to do that.

Never perform auditing at any level except through triggers on the database. You want to catch all changes not just those made by one application. Any unauthorized changes made through a stealth method are even more important to capture even if you think the application is the only way to change the data. And I have never seen any database of any size that didn't have the need to run run ad hoc data changes outside the application.

Best Answer

Related Solutions

Database – NHibernate and complex domain model against a legacy database

Audit Trails and Entity Relationships in ORM

Related Topic