Thanks for getting back.
At this point, to everyone except the development architect, we’re still gathering information prior to making a decision. My two biggest issues come down to 1) not making the database “stupid” and 2) not eliminating the ability to troubleshoot this once its in production. The dev guy wants to make the database structure match the object structure so that his mapping code is minimized. That’s just contrary to all my experience and most of the research I’ve done. We may have a fight there. The other concern is a bit more esoteric, but it’s been my experience that when there is a TSQL issue, I’m able to get a fix out to production a heck of a lot faster than the developers are able to rewrite & deploy their code.
I’ve come up with a list of concerns/issues/questions that we’ll be presenting when we start the final meetings prior to a decision. Have a look and please feel free to comment (formatting was a bit of a hash after the paste):
• Scalability Performance and Tuning
? I/O increase due to “chattier” applications
§ Most documentation indicates a lot more, smaller transactions, not to mention the possibility of frequent requests to verify structure (the app checking to see if the database has changed) prior to generating & running queries.
§ Mitigation is to ensure appropriate configuration & use of nHibernate. Monitoring can be done with Profiler
? I/O increase due to loading larger data sets more frequently
§ Ensure the use of “lazy” collections to reduce data moved
§ No other mitigation possible.
? General performance issues due to “generic” procedures using less efficient access methods
§ Generic data types used in queries can lead to indexes not being used
? Zero possibility to tune queries in a production setting
§ Any, all, changes require re-coding and re-deployment. There are no methods available for database only tuning except applying an index or forcing parameterization.
• Object-centric approach to building databases
§ This includes transactional level reporting as well as moving data between a model driven design and a more flexible design (normalized or star schema or whatever) that better supports reporting. Coding time reduced on the front end is, to some degree, tacked on to the back-end.
§ A data cleansing mechanism may be required.
§ Redundancy of data an authoritative sources for data may require some refinement.
? Data integrity
§ Generated structures are dependent on discovery to determine the proper constraints required on the data, or all constraints are assumed to be in the code.
§ Without integrity maintained with data the possibility of “dirty” data is increased (“USA”, “U.S.A.”,”US” all values entered through app).
? We will have to give over full read/write privileges at the table level to the application. I think, it’s possible, we’d have to give it ‘dbo’ in production. Based on a few statements in some of the research, it’s even possible we’d have to give it ‘sa’ (although that is completely unproven currently).
• Integration with other systems at the data level
? Depending on the application, this may not be required. But if it is required at any point, it will entail a larger than normal effort to convert the data to a more normalized structure.