Writing Nearly Codeless Apps: Part 3

  • David Ziffer

    SSCommitted

    Points: 1513

    Comments posted to this topic are about the item Writing Nearly Codeless Apps: Part 3

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Hi David,

    Thanks for the article (and the series).

    I have one (minor) issue with this part. The audit table uses the primary key of the current-row table, plus a datatime column. The precision of datetime is 3/1000 of a second. This means that, on an extremely busy system, you run into the risk of getting duplicate rows errors when adding to the audit table.

    On SQL Server 2008 and later, you could use DATETIME2(7), to get a precision of 100ns. That would reduce the chance of collisions, but not completely remove it.

    I hardly ever recommend using IDENTITY, but this audit table would be a situation where I make an exception. Using IDENTITY to generate primary keys for this table is the only way to make 100% sure that there will not be any collisions.

    Best, Hugo


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Cade Roux

    Ten Centuries

    Points: 1326

    I have successfully used http://autoaudit.codeplex.com/ for triggers and I've been happy. The particular system I used it in wasn't what I would call a high volume OLTP environment, since the tables we managed were control tables for a dimensional data warehouse - so the majority of the big data was managed with a different paradigm. However, I certainly would recommend it and expect to use it in future products and am even looking for similar system for a more-OLTP-based Oracle project I'm working on now.

  • David Ziffer

    SSCommitted

    Points: 1513

    Hugo Kornelis (10/20/2010)


    On SQL Server 2008 and later, you could use DATETIME2(7), to get a precision of 100ns. That would reduce the chance of collisions, but not completely remove it.

    Thanks for this post Hugo. I will look into this and almost certainly make this change. RAP was originally developed under SQL Server 2005, which did not have DATETIME2. By the way RAP detects (and resolves) such collisions.

    I hardly ever recommend using IDENTITY, but this audit table would be a situation where I make an exception. Using IDENTITY to generate primary keys for this table is the only way to make 100% sure that there will not be any collisions.

    Even more importantly, using IDENTITY keys is the only way to actually preserve the identity of a row through an audit, since it is the only field guaranteed to remain forever invariant. The audit trail of a row using a business key as a primary key would be lost at every point where someone changes the business key.

    But there are even larger benefits to using IDENTITY keys consistently. For example, RAP has a built-in ORM that manages all database/data-layer interactions. The complexity of this ORM is fantastically reduced because all keys in the system (including foreign keys) are the same. This is where the Henry Ford analogy starts kicking in. The benefits of consistency are rarely apparent at the component level, but startlingly clear at the system level.

  • jyates

    SSC Veteran

    Points: 213

    Something else that we often forget is that, in order to get consistently good results, people using the system must be trained. As you stated, the Romans did this well.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    David Ziffer (10/20/2010)


    Hugo Kornelis (10/20/2010)


    On SQL Server 2008 and later, you could use DATETIME2(7), to get a precision of 100ns. That would reduce the chance of collisions, but not completely remove it.

    Thanks for this post Hugo. I will look into this and almost certainly make this change. RAP was originally developed under SQL Server 2005, which did not have DATETIME2. By the way RAP detects (and resolves) such collisions.

    Don't forget that all datetime2 does is reduce the probability of collisions. It does not completely prevent them.

    With respect to the IDENTITY column, I just want to stress that my comment was specific about the audit tables. I am not a big fan of IDENTITY in the tables that hold the actual data. Not because they are bad per say, but because they are almost always used in a bad way. I estimate that well over 90% of the people using IDENTITY columns make at least one, and usually two of the following two mistakes:

    1) Using the IDENTITY column as the only key in the table, instead of also including and enforcing the actual "business" key.

    2) Exposing the IDENTITY values to the end user, instead of keeping them internal to the application and using the actual "busness" key for data entry and reporting.

    When accompanied by a UNIQUE constraint on the actual business key and not exposed to the end users, there is nothing wrong with using an IDENTIY column as a surrogate key.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • David Ziffer

    SSCommitted

    Points: 1513

    Hugo Kornelis (10/20/2010)


    When accompanied by a UNIQUE constraint on the actual business key and not exposed to the end users, there is nothing wrong with using an IDENTIY column as a surrogate key.

    Hugo: I wish I could always be working with people with your level of understanding.

  • GoofyGuy

    SSCertifiable

    Points: 6029

    David Ziffer quoted an article which stated:

    The Romans created an army where systems, training, and supervision combined to create a world-conquering force ...

    Systems fail when they cannot quickly adapt to changing circumstances. Remember Arminius.

  • David Ziffer

    SSCommitted

    Points: 1513

    Craig-315134 (10/20/2010)


    David Ziffer quoted an article which stated:

    The Romans created an army where systems, training, and supervision combined to create a world-conquering force ...

    Systems fail when they cannot quickly adapt to changing circumstances. Remember Arminius.

    Hmmmm ... I'm not sure what the implication is here, but I imagine it's that RAP would tie you down to a fixed structure that can't adapt. Like every other design it has its limitations, but actually by virtue of its extraordinary consistency a RAP-based design should be dramatically easier to modify than most hand-written apps. The consistency alone would lead the maintainer to be able to deduce the scope of the changes.

    In a typical design, for example, a schema change to one table would require you to alter the table, then alter all the code in the database associated with that table, then alter the data layer to account for the change, and then finally adapt the business rules and UI to take advantage of the change. With RAP, you alter the table. Then RAP regenerates the database code and the data layer code for you, and the code is flawless. Finally of course you then have to modify the business rules and UI (as before) to take advantage of the change. The RAP cycle takes a lot less time and the code-generated portion is error free.

    In my experience, which now spans over three decades, the apps that are the hardest to change are the hand-written ones with lots of inconsistencies. Once again I'll use the car analogy. Which is easier and cheaper to maintain and adapt .... a custom hand-built car, or one that came off an assembly line? I'll take the Honda Civic, thank you.

  • GoofyGuy

    SSCertifiable

    Points: 6029

    David Ziffer wrote:

    In my experience, which now spans over three decades, the apps that are the hardest to change are the hand-written ones with lots of inconsistencies.

    What a coincidence - I've been at this game well over three decades as well - but only a small part of it as a DBA, with a much longer stint in application development.

    You're right, it's better to use libraries and built-in functions whenever and wherever possible, which explains the success of everything from .Net to JQuery. However ...

    Once again I'll use the car analogy. Which is easier and cheaper to maintain and adapt .... a custom hand-built car, or one that came off an assembly line? I'll take the Honda Civic, thank you.

    Once again? I must have missed something ... I'd thought you started and ended with the Roman Legions. But regardless: in my own experience, a Honda won't get you very far after the paved road (which, incidentally, was part of the Romans' system of domination) ends and the morass begins. I'll take a Range Rover, thank you.

    Or, if you prefer a more recent historic analogy than the Roman Empire, look at the US/Viet-Nam war. The US certainly seemed far more systematic and technically adept than its foe - but it was scarcely as adaptable.

    Arminius and Ho Chi Minh were both very good at stopping empires in their tracks, through adaptivity ... although I'm not sure either was much good at SQL.

  • Julien.Chappel

    SSC Journeyman

    Points: 96

    I like the idea generally, but itshould have been pointed out that the system is not a limitation but a framework only. If the system fails then everything fails within it.

    I don'nt like the philosophy "put your trust in the system not in genius." This rubbish. Even the system is invented by geniuses, like Henry Ford (inventing the most hated system the production line). I would like to defet this philosophy with the Roman Army example. Geniuses: what would have been the Roman Army without Cornelius Scipio (reorganising the tactical deployment suit to the situation), Aemilius Paulus (Introducing new tactics to beat the famous macedon falanx at Pydna), Gaius Marius (reorganising the Roman Army so that after decades of disastorous defets it was capable to win battles again-Aquae Sixtee; Vercelle-, Julius Ceasar... and the list goes on of these ancient geniuses. Also the Roman Army is full of individual heroes.

    regards

    Julien

  • David Ziffer

    SSCommitted

    Points: 1513

    Julien.Chappel (10/20/2010)


    I don'nt like the philosophy "put your trust in the system not in genius." This rubbish.

    I agree that we always need geniuses, and I never meant to imply that we can dispense with them. The question is how we use them.

    To use the military analogy, you can either put your genius in a uniform and hope that he leads the troops to a victory mostly through fortunate circumstance (because there is a limit to the effect of a single genius on the battlefield no matter how good he is). Or you can instead use the genius to design a system that allows all ordinary foot soldiers to perform 100% better.

    I prefer the latter approach because it distributes the effect of the genius more effectively. Also you lose fewer geniuses that way.

  • Julien.Chappel

    SSC Journeyman

    Points: 96

    Hi David,

    I think we have a different point of view about geniuses. Let me quote you: "Or you can instead use the genius to design a system ."

    In my book if you can use a genius he/she is not a genius anymore. There are certain circumstances when the system can force a genius to back down temporally (Galileo Galilei) but certainly not use them. Galilei's last words on his deathbed were "Eppur si muove". It means And yet it moves. Implementation in its context: The Eart is moving whatever the Vatican says. Giordano Bruno rather died on the pire then retract his ideas.

    So don't try to use genius because you end up with mediocre sapling.

    Just anoter thought. Geniuses who design systems never become part of the system. It is only looks that way. They are controlling the system. Bill Gates for example. He can walk away from Microsoft any time, he still remains one of the most richest man in the world.

    Well we are far away from SQL Server and Writing Nearly Codeless Apps but I enjoyed the exchange of ideas.

    reagrds

    Julien

  • Julien.Chappel

    SSC Journeyman

    Points: 96

    Hi David,

    I think we have a different point of view about geniuses. Let me quote you: "Or you can instead use the genius to design a system ."

    In my book if you can use a genius he/she is not a genius anymore. There are certain circumstances when the system can force a genius to back down temporally (Galileo Galilei) but certainly not use them. Galilei's last words on his deathbed were "Eppur si muove". It means And yet it moves. Implementation in its context: The Eart is moving whatever the Vatican says. Giordano Bruno rather died on the pire then retract his ideas.

    So don't try to use genius because you end up with mediocre sapling.

    Just anoter thought. Geniuses who design systems never become part of the system. It is only looks that way. They are controlling the system. Bill Gates for example. He can walk away from Microsoft any time, he still remains one of the most richest man in the world.

    Well we are far away from SQL Server and Writing Nearly Codeless Apps but I enjoyed the exchange of ideas.

    reagrds

    Julien

  • Pantelis Magos

    Grasshopper

    Points: 15

    Where are the links for Parts 1 and 2?

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply