David Ziffer (10/13/2010)

Hopefully this will only be used on small projects that do not have to be maintained and do not have to scale.

Having been on the receiving end of consultant\vendor created apps that are built using techniques very similar to this, it is definitely not an approach that should be followed for anything other than small, quick and dirty, one off apps that won't be around for very long. Trying to transfer the whole RAD\Agile philosophy into the database design and implementation world is at best just lazy, at worst a huge waste of time and money as the project ends up being reworked from the ground up. Maybe there will come a time where CPUs are so fast and memory so cheap that the extremely poor performance from databases designed using these techniques won't matter, but that time is not now. The performance and quality of any serious enterprise application depend on the design of the database and the abilities\experience of those who are writing the SQL to access the databases. Some may think that designing a database can be written into a few "rules" to follow and then code those rules into a generator, but that just reveals how little is actually known about the database design process. I am not going to rehash the issues and problems with using synthetic keys as these have been gone over time and time again. Just because they make it easy for a programmer does not make them right for a database design. The one thing programmers\developers refuse to think about is that the data is not part of the program, the data is part of the corporation and that data and hence the database design needs to last long after the program\application is obsolete. As such, the database design requirements should be analyzed and worked out independently of the application, not as part of coding the application.

With regards to your second remark about date fields and keeping all data in the "current" table, I would suggest that there is nothing at all wrong with that approach depending upon the business requirements. I, also, have worked in a place where all data was kept in the "current" table and no rows were updated, other than to expire or logically delete them. Keeping them in the "current" table was the only way to meet the business requirements that they be able to see the data as if they were "traveling in time", what the organization called "full point-in-time". To briefly explain it, they needed to be able to display, for legal reasons, exactly what a user would have seen at any point in the past. But the full concept is much more complex than that. Suffice it to say that four different dates are required for each row,(created, effective,expired ,logically deleted) and while the SQL was complex, it was by no means a performance disaster. Every query was significantly sub-second as the SLA between the application group and the business was that every screen change had to be sub-second. This is a very large scale government application with over 40,000 online users. I cannot say the same for the "generated" database designs and applications that we have receiving lately from vendors and contractors. They are the ones that have been performance nightmares and, because the developers don't go anywhere near the database but leave their "middle tier" framework to generate every thing for them, they haven't a slightest clue about how to tune the code or design. The "get the historical data out of the current table" response to poor performing queries is typical of quick and dirty solutions that end up becoming a nightmare down the road. We have a couple of legacy systems where someone decided that was the way to go. Now, every single query has to run against both the current and historical databases because the business needs to access data that was not considered "current" by the developers. The problem, as I see it these days, is that people are so intent on getting something done quickly, they look for and use quick solutions to build things out their realm of expertise. There is a reason why people specialize in certain areas and that is because the complexity of the field requires the expertise of a specialist for anything beyond a very simple application that may have only a couple of users.

No one has ever been able to convince me that any of these code-generating\database-generating solutions is solid enough for serious use. They might be good enough to generate a prototype\mockup, but I would never stake my career or reputation on the use of such a product for a production quality application\database. As such, I will always recommend against them when management comes asking for input from the senior IT staff. I understand that those working contracts want to get the code\apps done quickly and get their pay check, but those of us who work for the company want to receive products that perform, are maintainable, and have vendors that are able to support performance\scalability issues when they arise. When we receive an app that is obviously generated and not thoughtfully coded, that vendor\contractor is noted and will not receive my recommendations in the future.

Ian Dundas

Senior IT Analyst - Database

Manitoba Public Insurance Corporation