1

I am developing an application with NH and Sql Server. I have decided to go with the method of creating the database structure myself then map with NH afterwards.

When creating a database structure I like to use foreign keys to maintain referential integrity. In the cases where there is a one to one table mapping which is nullable, I have in the past created a dummy zero ID row on the lookup table. The reason that I have done this is because I have seen that left join queries were not as performant as full joins.

i am keen to improve and interested to get some advice for whether i should pursue this again with my new database structure which would mean getting NH to ignore zero ID's globally upon selects somehow. i suspect this could be fraught with danger when using NH and perhaps i am better taking an alternative route with the database. any advice very appreciated thanks.

Slim
  • 101
  • 8
  • In my opinion, using NH should encourage you to develop your application from an OO point of view. I recommend to develop the OO model first and care about your classes (encapsulation etc.) and not too much about database details. If you need to process millions of records and your classes are just trivial, you can't get much advantage of NH. – Stefan Steinegger Aug 22 '11 at 06:45
  • You may be correct by this is a good learning process for me. – Slim Aug 25 '11 at 06:41

1 Answers1

1

The reason that I have done this is because I have seen that left join queries were not as performant as full joins.

I don't know enough about your application, performance requirements and the measurements that you did. But it might be worth not creating artificial 0 value in FK column just for the simplicity sake. The measurement that you did might no longer be relative or maybe this was not a bottleneck in the first place. Maybe there is a way to improve left join performance by putting index on FK column.

What you need to know when mapping one-to-one in NHibernate is that lazy loading for nullable associations is not supported. Look at this answer for options.

Community
  • 1
  • 1
Dmitry
  • 17,078
  • 2
  • 44
  • 70
  • Dmitry, thanks for taking the time to reply. When I looked over my database I realised that in fact there weren't that many cases where I needed a nullable foreign key. Your answer is enabling me to move forward with more confidence. I will repost if I encounter any issues during the NHibernate implementation. – Slim Aug 25 '11 at 06:43